I noticed that the planner is unable to select an index scan when a partial index is available, the partial index is based on a "NOT NULL" condition.
Example: start with no index: mydb=# EXPLAIN ANALYZE select id from str where url='foobar'; NOTICE: QUERY PLAN: Seq Scan on str (cost=0.00..88.91 rows=1 width=4) (actual time=5.93..5.93 rows=0 loops=1) Total runtime: 6.01 msec EXPLAIN mydb=# create index str_idx_url on str(url) where url is not null; CREATE mydb=# analyze str; ANALYZE mydb=# EXPLAIN ANALYZE select id from str where url='foobar'; NOTICE: QUERY PLAN: Seq Scan on str (cost=0.00..91.05 rows=3 width=4) (actual time=6.24..6.24 rows=0 loops=1) Total runtime: 6.30 msec EXPLAIN mydb=# drop index str_idx_url; DROP mydb=# create index str_idx_url on str(url); CREATE mydb=# analyze str; ANALYZE mydb=# EXPLAIN ANALYZE select id from str where url='foobar'; NOTICE: QUERY PLAN: Index Scan using str_idx_url on str (cost=0.00..2.56 rows=1 width=4) (actual time=0.53..0.53 rows=0 loops=1) Total runtime: 0.60 msec EXPLAIN It's no big deal in my application, speed is more than fast enough, I just noticed it. The documentation says: "However, keep in mind that the predicate must match the conditions used in the queries that are supposed to benefit from the index. To be precise, a partial index can be used in a query only if the system can recognize that the query's WHERE condition mathematically implies the index's predicate. PostgreSQL does not have a sophisticated theorem prover that can recognize mathematically equivalent predicates that are written in different forms. (Not only is such a general theorem prover extremely difficult to create, it would probably be too slow to be of any real use.) The system can recognize simple inequality implications, for example "x < 1" implies "x < 2"; otherwise the predicate condition must exactly match the query's WHERE condition or the index will not be recognized to be usable. " Normally a "IS NOT NULL"/"IS NULL" should be easy to recognise, since NULL is very special. This would allow much smaller indices in some applications, for example I've a case with a table with 200000 rows where 4 values (of type text) are not null. The index size would be much smaller without all those NULL values. Best regards, Mario Weilguni ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]