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
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
You can try an index like:
create index
You can try an index like:
create index str_idx_url on str(url) where url = '';
I think that should be identical. ('' is the smallest string, no ?)
Thanks alot, it works now. But I still think the NOT NULL case would be
useful.
Best regards,
Mario Weilguni
Mario Weilguni [EMAIL PROTECTED] writes:
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.
It wants you to do this:
select id from str where url='foobar' and url is not null;
I know and you know
