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

Re: not using partial index

From: Henry <henry(dot)valdivia1(at)gmail(dot)com>
To: Willy-Bas Loos <willybas(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: not using partial index
Date: 2011-04-23 15:48:35
Message-ID: a5c651ureodvf2p1vmvwdsox.1303573715550@email.android.com (view raw or flat)
Thread:
Lists: pgsql-performance

Willy-Bas Loos <willybas(at)gmail(dot)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

pgsql-performance by date

Next:From: Robert HaasDate: 2011-04-23 16:10:15
Subject: Re: How to configure a read-only database server?
Previous:From: Greg SmithDate: 2011-04-23 02:48:04
Subject: Re: OT (slightly) testing for data loss on an SSD drive due to power failure

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