Re: query plan ignoring check constraints

Someone Wrote:

> Should not check constraint act as the first filter? The index should
> ideally be scanned only when the check constraint is passed by the
search
> criteria but surprisingly it did not happen. The explain analyze
showed
> cost for index scans of subtables that cannot contain rows matching
the
> search criteria.

Obviously, indexes on columns with a check constraint, should be
qualified with the same check constraint.

test=# CREATE TABLE test (
foo text check(foo IN ('YES','NO'))
);
CREATE TABLE
test=# CREATE INDEX text_foo_idx ON test (foo) WHERE foo IN('YES','NO');
CREATE INDEX
test=# INSERT INTO test VALUES ('YES');
INSERT 280188 1
test=# INSERT INTO test VALUES ('NO');
INSERT 280189 1
test=# INSERT INTO test VALUES ('no');
ERROR: new row for relation "test" violates check constraint
"test_foo_check"
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'YES';
QUERY PLAN

------------------------------------------------------------------------
--------------------------------------------
Index Scan using text_foo_idx on test (cost=0.00..5.82 rows=7
width=32) (actual time=0.369..0.376 rows=1 loops=1)
Index Cond: (foo = 'YES'::text)
Total runtime: 0.490 ms
(3 rows)
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'no';
QUERY PLAN

------------------------------------------------------------------------
------------------------
Seq Scan on test (cost=0.00..25.38 rows=7 width=32) (actual
time=0.358..0.358 rows=0 loops=1)
Filter: (foo = 'no'::text)
Total runtime: 0.421 ms
(3 rows)
test=#

... John