Обсуждение: Index ignored with "is not distinct from", 8.2 beta2
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
One issue is that I'm not sure think you've got your sugar quite right. Have you tested with: (col IS NOT NULL AND 123 IS NOT NULL AND col = 123) OR (col IS NULL and 123 IS NULL) ? It's possible that the planner doesn't know about using an index for DISTINCT; or it might just want an index that's defined WHERE col IS NOT NULL. On Wed, Nov 01, 2006 at 03:29:33PM -0500, JEAN-PIERRE PELLETIER wrote: > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
This shows all three forms to be equivalent. SELECT exp1, exp2, exp1 IS NOT DISTINCT FROM exp2 AS isnotdistinct, exp1 is not null and exp2 is not null and exp1 =exp2 or exp1 is null and exp2 is null AS JP, (exp1 is not null and exp2 is not null and exp1 = exp2) or (exp1 is null and exp2 is null) AS Jim FROM (SELECT 1 AS exp1, 1 AS exp2 UNION ALL SELECT 1, 2 UNION ALL SELECT 1,NULL UNION ALL SELECT NULL,1 UNION ALL SELECTNULL,NULL) Q; I understand that the planner doesn't use indexes for IS NOT DISTINCT FROM, but it would be good because "is not distinct from" is very useful when you have nulls but don't want to use three value logic. null = null => true null = not null => false I don't think it is that uncommon and even some SQL constructs such as "select distinct" or "group by" compare null that way. I'll wait before using IS NOT DISTINCT FROM and stick with the equivalent longer forms which use indexes. 8.2 is better than 8.1 which was not picking up indexes even with the longer forms. Jean-Pierre Pelletier >From: "Jim C. Nasby" <jim@nasby.net> >To: JEAN-PIERRE PELLETIER <pelletier_32@sympatico.ca> >CC: pgsql-hackers@postgresql.org >Subject: Re: [HACKERS] Index ignored with "is not distinct from", 8.2 beta2 >Date: Mon, 6 Nov 2006 16:02:40 -0600 > >One issue is that I'm not sure think you've got your sugar quite right. >Have you tested with: > >(col IS NOT NULL AND 123 IS NOT NULL AND col = 123) OR > (col IS NULL and 123 IS NULL) ? > >It's possible that the planner doesn't know about using an index for >DISTINCT; or it might just want an index that's defined WHERE col IS NOT >NULL. > >On Wed, Nov 01, 2006 at 03:29:33PM -0500, JEAN-PIERRE PELLETIER wrote: > > 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 > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > >-- >Jim Nasby jim@nasby.net >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Mon, Nov 06, 2006 at 09:10:40PM -0500, JEAN-PIERRE PELLETIER wrote: > I understand that the planner doesn't use indexes for IS NOT DISTINCT FROM, > but it would > be good because "is not distinct from" is very useful when you have nulls > but don't want to use three value logic. The main issue is that currently indexes cannot be used to find NULLs in a table. Patches have been created that cover most index types, but it's not part of the main distribution. Partial indexes can be a solution to the "x IS NULL" clauses. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Tue, Nov 07, 2006 at 11:03:42AM +0100, Martijn van Oosterhout wrote: > On Mon, Nov 06, 2006 at 09:10:40PM -0500, JEAN-PIERRE PELLETIER wrote: > > I understand that the planner doesn't use indexes for IS NOT DISTINCT FROM, > > but it would > > be good because "is not distinct from" is very useful when you have nulls > > but don't want to use three value logic. > > The main issue is that currently indexes cannot be used to find NULLs > in a table. Patches have been created that cover most index types, but > it's not part of the main distribution. I assume you're referring to http://archives.postgresql.org/pgsql-patches/2005-09/msg00083.php ? I'm curious as to the status of that patch... presumably it never made it into the queue, but I'm not sure why since you seemed to address Tom's concerns (other than not indexing IS NOT NULL, which I'm not sure is all that useful...) -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Tue, Nov 07, 2006 at 02:12:29PM -0600, Jim C. Nasby wrote: > I assume you're referring to > http://archives.postgresql.org/pgsql-patches/2005-09/msg00083.php ? > > I'm curious as to the status of that patch... presumably it never made > it into the queue, but I'm not sure why since you seemed to address > Tom's concerns (other than not indexing IS NOT NULL, which I'm not sure > is all that useful...) There's been work on it. Theodor cleaned it up for HEAD and looked at adding GiST support. I beleive he's waiting for 8.2 to release. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
I can see that adding null to indexes would allow all cases of "is not distinct from" to use them. The lack of null in indexes would explain why a condition such as "col is not distinct from null" would not pick up an index. But my example was: "col is not distinct from 123" and the equivalent longer form has no problem picking up the index I could restate the problem as: Why can't the planner handle "col is not distinct from 123" as well as "col is not null and 123 is not null and col = 123 or col is null and 123 is null" Jean-Pierre Pelletier >From: Martijn van Oosterhout <kleptog@svana.org> >Reply-To: Martijn van Oosterhout <kleptog@svana.org> >To: JEAN-PIERRE PELLETIER <pelletier_32@sympatico.ca> >CC: jim@nasby.net, pgsql-hackers@postgresql.org >Subject: Re: [HACKERS] Index ignored with "is not distinct from", 8.2 beta2 >Date: Tue, 7 Nov 2006 11:03:42 +0100 > >On Mon, Nov 06, 2006 at 09:10:40PM -0500, JEAN-PIERRE PELLETIER wrote: > > I understand that the planner doesn't use indexes for IS NOT DISTINCT >FROM, > > but it would > > be good because "is not distinct from" is very useful when you have >nulls > > but don't want to use three value logic. > >The main issue is that currently indexes cannot be used to find NULLs >in a table. Patches have been created that cover most index types, but >it's not part of the main distribution. > >Partial indexes can be a solution to the "x IS NULL" clauses. > >Hope this helps, >-- >Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > From each according to his ability. To each according to his ability to >litigate. ><< signature.asc >>
> There's been work on it. Theodor cleaned it up for HEAD and looked at > adding GiST support. I beleive he's waiting for 8.2 to release. Yep, I have bundle of patches and I'm waiting for 8.2 branch split out of HEAD. -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/