Skip site navigation (1) Skip section navigation (2)

Index ignored with "is not distinct from", 8.2 beta2

From: "JEAN-PIERRE PELLETIER" <pelletier_32(at)sympatico(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index ignored with "is not distinct from", 8.2 beta2
Date: 2006-10-27 02:19:20
Message-ID: BAY118-F101965BD0514B9E75583C195040@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-performance
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-performance by date

Next:From: Gavin HamillDate: 2006-10-27 09:20:25
Subject: Re: VACUUMs take twice as long across all nodes
Previous:From: Tom DarciDate: 2006-10-27 00:01:19
Subject: Re: query slows down drastically with increased number of

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group