pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feednot using partial index
3+ messages / 3 participants
[nested] [flat]
* not using partial index
@ 2011-04-20 08:46 Willy-Bas Loos <willybas@gmail.com>
2011-04-20 13:25 ` Re: not using partial index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Willy-Bas Loos @ 2011-04-20 08:46 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm using PostgreSQL 8.4 (and also 8.3).
A partial index like this:
CREATE INDEX table2_field1_idx
ON table2 (field1)
WHERE NOT field1 ISNULL;
Will not be used when select one record from 100K records:
explain select * from table2 where field1 = 256988
'Seq Scan on table2 (cost=0.00..1693.01 rows=1 width=4)'
' Filter: (field1 = 256988)'
But it WILL be used like this:
explain select * from table2 where field1 = 256988 and not field1 isnull
'Index Scan using table2_field1_idx on table2 (cost=0.00..8.28 rows=1
width=4)'
' Index Cond: (field1 = 256988)'
But, when i change the index from"NOT field1 ISNULL " to "field1 NOTNULL",
then the index WILL be used in both queries:
explain select * from table1 where field1 = 256988
'Index Scan using table1_field1_idx on table1 (cost=0.00..8.28 rows=1
width=4)'
' Index Cond: (field1 = 256988)'
'Index Scan using table1_field1_idx on table1 (cost=0.00..8.28 rows=1
width=4)'
' Index Cond: (field1 = 256988)'
' Filter: (NOT (field1 IS NULL))'
Any ideas why this might be?
Cheers,
WBL
Code below:
--drop table table1;
create table table1(field1 integer);
CREATE INDEX table1_field1_idx
ON table1 (field1)
WHERE field1 NOTNULL;
insert into table1 values(null);
insert into table1 select generate_series(1,100000);
vacuum analyze table1;
explain select * from table1 where field1 = 256988
explain select * from table1 where field1 = 256988 and not field1 isnull
--drop table table2;
create table table2(field1 integer);
CREATE INDEX table2_field1_idx
ON table2 (field1)
WHERE NOT field1 ISNULL;
insert into table2 values(null);
insert into table2 select generate_series(1,100000);
vacuum analyze table2;
explain select * from table2 where field1 = 256988
explain select * from table2 where field1 = 256988 and not field1 isnull
--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: not using partial index
2011-04-20 08:46 not using partial index Willy-Bas Loos <willybas@gmail.com>
@ 2011-04-20 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2011-04-20 13:25 UTC (permalink / raw)
To: Willy-Bas Loos <willybas@gmail.com>; +Cc: pgsql-performance
Willy-Bas Loos <willybas@gmail.com> writes:
> [ NOT field1 ISNULL is not seen as equivalent to field1 IS NOT NULL ]
> Any ideas why this might be?
The planner does not spend an infinite number of cycles on trying to
make different expressions look alike.
As it happens, 9.1 does know this equivalence, as a byproduct of
http://git.postgresql.org/gitweb?p=postgresql.git&a=commitdiff&h=220e45bf325b061b8dbd7451f87...
But I don't consider it a bug that older versions don't do it.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: not using partial index
@ 2011-04-23 15:48 Henry <henry.valdivia1@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Henry @ 2011-04-23 15:48 UTC (permalink / raw)
To: Willy-Bas Loos <willybas@gmail.com>; pgsql-performance
Willy-Bas Loos <willybas@gmail.com> wrote:
>Hi,
>
>I'm using PostgreSQL 8.4 (and also 8.3).
>
>A partial index like this:
>CREATE INDEX table2_field1_idx
> ON table2 (field1)
> WHERE NOT field1 ISNULL;
>
>Will not be used when select one record from 100K records:
>
>explain select * from table2 where field1 = 256988
>'Seq Scan on table2 (cost=0.00..1693.01 rows=1 width=4)'
>' Filter: (field1 = 256988)'
>
>But it WILL be used like this:
>
>explain select * from table2 where field1 = 256988 and not field1 isnull
>'Index Scan using table2_field1_idx on table2 (cost=0.00..8.28 rows=1
>width=4)'
>' Index Cond: (field1 = 256988)'
>
>
>But, when i change the index from"NOT field1 ISNULL " to "field1 NOTNULL",
>then the index WILL be used in both queries:
>
>explain select * from table1 where field1 = 256988
>'Index Scan using table1_field1_idx on table1 (cost=0.00..8.28 rows=1
>width=4)'
>' Index Cond: (field1 = 256988)'
>
>'Index Scan using table1_field1_idx on table1 (cost=0.00..8.28 rows=1
>width=4)'
>' Index Cond: (field1 = 256988)'
>' Filter: (NOT (field1 IS NULL))'
>
>
>Any ideas why this might be?
>
>
>Cheers,
>
>WBL
>
>Code below:
>
>--drop table table1;
>create table table1(field1 integer);
>CREATE INDEX table1_field1_idx
> ON table1 (field1)
> WHERE field1 NOTNULL;
>insert into table1 values(null);
>insert into table1 select generate_series(1,100000);
>
>vacuum analyze table1;
>
>explain select * from table1 where field1 = 256988
>explain select * from table1 where field1 = 256988 and not field1 isnull
>
>
>--drop table table2;
>create table table2(field1 integer);
>CREATE INDEX table2_field1_idx
> ON table2 (field1)
> WHERE NOT field1 ISNULL;
>insert into table2 values(null);
>insert into table2 select generate_series(1,100000);
>
>vacuum analyze table2;
>
>explain select * from table2 where field1 = 256988
>explain select * from table2 where field1 = 256988 and not field1 isnull
>
>
>--
>"Patriotism is the conviction that your country is superior to all others
>because you were born in it." -- George Bernard Shaw
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-04-23 15:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-20 08:46 not using partial index Willy-Bas Loos <willybas@gmail.com>
2011-04-20 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-23 15:48 Re: not using partial index Henry <henry.valdivia1@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox