On Tue, Jun 21, 2005 at 09:46:50 +1000, John Hansen <john@geeknet.com.au> wrote:
> 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.
I think the real problem is that check constraints on tables aren't
used by the optimizer. Given that, what you have below is expected.
There has been talk about that in the past, but I haven't heard anything
recently about someone considering implenting that.
For your problem consider not using a partial index. It isn't going to
save anything if it has a constraint matching that of the table.
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org