Index ignored with "is not distinct from", 8.2 beta2
От | JEAN-PIERRE PELLETIER |
---|---|
Тема | Index ignored with "is not distinct from", 8.2 beta2 |
Дата | |
Msg-id | BAY118-F20EA79FC1464E3166D213B95F80@phx.gbl обсуждение исходный текст |
Ответы |
Re: Index ignored with "is not distinct from", 8.2 beta2
|
Список | pgsql-hackers |
I've reposted this from pgsql-performance where I got no response. ========================================== Hi, I wanted to use "exp1 is not distinct from exp2" which I tough was syntaxic sugar for exp1 is not null and exp2 is not null and exp1 = exp2 or exp1 is null and exp2 is null but my index is ignored with "is not distinct from". Is this the expected behavior ? create temporary table t as select * from generate_series(1,1000000) t(col); create unique index i on t(col); analyze t; -- These queries don't use the index select count(*) from t where col is not distinct from 123; select count(*) from t where not col is distinct from 123; -- This query use the index select count(*) from t where col is not null and 123 is not null and col = 123 or col is null and 123 is null; explain analyze select count(*) from t where col is not distinct from 123; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Aggregate (cost=19154.79..19154.80 rows=1 width=0) (actual time=228.200..228.202 rows=1 loops=1) -> Seq Scan on t (cost=0.00..17904.90 rows=499956 width=0) (actual time=0.042..228.133 rows=1 loops=1) Filter: (NOT (col IS DISTINCT FROM 123)) Total runtime: 228.290 ms (4 rows) Time: 219.000 ms explain analyze select count(*) from t where not col is distinct from 123; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Aggregate (cost=19154.79..19154.80 rows=1 width=0) (actual time=235.950..235.952 rows=1 loops=1) -> Seq Scan on t (cost=0.00..17904.90 rows=499956 width=0) (actual time=0.040..235.909 rows=1 loops=1) Filter: (NOT (col IS DISTINCT FROM 123)) Total runtime: 236.065 ms (4 rows) Time: 250.000 ms explain analyze select count(*) from t where col is not null and 123 is not null and col = 123 or col is null and 123 is null; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Aggregate (cost=8.13..8.14 rows=1 width=0) (actual time=0.267..0.268 rows=1 loops=1) -> Index Scan using i on t (cost=0.00..8.13 rows=1 width=0) (actual time=0.237..0.241 rows=1 loops=1) Index Cond: (col = 123) Total runtime: 0.366 ms (4 rows) Time: 0.000 ms I am on Windows XP Service pack 2 with PostgreSQL 8.2 beta2 Thanks, Jean-Pierre Pelletier e-djuster
В списке pgsql-hackers по дате отправления: