pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDrawbacks of create index where is not null ?
6+ messages / 4 participants
[nested] [flat]
* Drawbacks of create index where is not null ?
@ 2012-10-10 17:06 Franck Routier <franck.routier@axege.com>
2012-10-11 05:26 ` Re: Drawbacks of create index where is not null ? Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 6+ messages in thread
From: Franck Routier @ 2012-10-10 17:06 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have pretty large tables, with columns that might never receive any
data, or always receive data, based on the customer needs.
The index on these columns are really big, even if the column is never
used, so I tend to add a "where col is not null" clause on those indexes.
What are the drawbacks of defining my index with a "where col is not
null" clause ?
Franck
Attachments:
smime.p7s (application/pkcs7-signature, 4.4K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Drawbacks of create index where is not null ?
2012-10-10 17:06 Drawbacks of create index where is not null ? Franck Routier <franck.routier@axege.com>
@ 2012-10-11 05:26 ` Craig Ringer <ringerc@ringerc.id.au>
2012-10-11 05:42 ` Re: Drawbacks of create index where is not null ? Scott Marlowe <scott.marlowe@gmail.com>
2012-10-11 08:22 ` Re: Drawbacks of create index where is not null ? Franck Routier <franck.routier@axege.com>
0 siblings, 2 replies; 6+ messages in thread
From: Craig Ringer @ 2012-10-11 05:26 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
On 10/11/2012 01:06 AM, Franck Routier wrote:
> Hi,
>
> I have pretty large tables, with columns that might never receive any
> data, or always receive data, based on the customer needs.
> The index on these columns are really big, even if the column is never
> used, so I tend to add a "where col is not null" clause on those indexes.
>
> What are the drawbacks of defining my index with a "where col is not
> null" clause ?
* You can't CLUSTER on a partial index; and
* The partial index will only be used for queries that use the condition
"WHERE col IS NOT NULL" themselves. The planner isn't super-smart about
how it matches index WHERE conditions to query WHERE conditions, so
you'll want to use exactly the same condition text where possible.
--
Craig Ringer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Drawbacks of create index where is not null ?
2012-10-10 17:06 Drawbacks of create index where is not null ? Franck Routier <franck.routier@axege.com>
2012-10-11 05:26 ` Re: Drawbacks of create index where is not null ? Craig Ringer <ringerc@ringerc.id.au>
@ 2012-10-11 05:42 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-10-11 05:44 ` Re: Drawbacks of create index where is not null ? Scott Marlowe <scott.marlowe@gmail.com>
2012-10-11 22:31 ` Re: Drawbacks of create index where is not null ? Sergey Konoplev <gray.ru@gmail.com>
1 sibling, 2 replies; 6+ messages in thread
From: Scott Marlowe @ 2012-10-11 05:42 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: Franck Routier <franck.routier@axege.com>; pgsql-performance
On Wed, Oct 10, 2012 at 11:26 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 10/11/2012 01:06 AM, Franck Routier wrote:
>>
>> Hi,
>>
>> I have pretty large tables, with columns that might never receive any
>> data, or always receive data, based on the customer needs.
>> The index on these columns are really big, even if the column is never
>> used, so I tend to add a "where col is not null" clause on those indexes.
>>
>> What are the drawbacks of defining my index with a "where col is not null"
>> clause ?
>
>
> * You can't CLUSTER on a partial index; and
>
> * The partial index will only be used for queries that use the condition
> "WHERE col IS NOT NULL" themselves. The planner isn't super-smart about how
> it matches index WHERE conditions to query WHERE conditions, so you'll want
> to use exactly the same condition text where possible.
I think the query planner has gotten a little smarter of late:
smarlowe=# select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.6 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
(1 row)
smarlowe=# drop table a;
DROP TABLE
smarlowe=# create table a (i int);
CREATE TABLE
smarlowe=# insert into a select null from generate_series(1,10000);
INSERT 0 10000
smarlowe=# insert into a values (10);
INSERT 0 1
smarlowe=# insert into a select null from generate_series(1,10000);
INSERT 0 10000
smarlowe=# create index on a (i) where i is not null;
CREATE INDEX
smarlowe=# explain select * from a where i =10;
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on a (cost=4.28..78.00 rows=100 width=4)
Recheck Cond: (i = 10)
-> Bitmap Index Scan on a_i_idx (cost=0.00..4.26 rows=100 width=0)
Index Cond: (i = 10)
(4 rows)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Drawbacks of create index where is not null ?
2012-10-10 17:06 Drawbacks of create index where is not null ? Franck Routier <franck.routier@axege.com>
2012-10-11 05:26 ` Re: Drawbacks of create index where is not null ? Craig Ringer <ringerc@ringerc.id.au>
2012-10-11 05:42 ` Re: Drawbacks of create index where is not null ? Scott Marlowe <scott.marlowe@gmail.com>
@ 2012-10-11 05:44 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2012-10-11 05:44 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: Franck Routier <franck.routier@axege.com>; pgsql-performance
On Wed, Oct 10, 2012 at 11:42 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Oct 10, 2012 at 11:26 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> On 10/11/2012 01:06 AM, Franck Routier wrote:
>>>
>>> Hi,
>>>
>>> I have pretty large tables, with columns that might never receive any
>>> data, or always receive data, based on the customer needs.
>>> The index on these columns are really big, even if the column is never
>>> used, so I tend to add a "where col is not null" clause on those indexes.
>>>
>>> What are the drawbacks of defining my index with a "where col is not null"
>>> clause ?
>>
>>
>> * You can't CLUSTER on a partial index; and
>>
>> * The partial index will only be used for queries that use the condition
>> "WHERE col IS NOT NULL" themselves. The planner isn't super-smart about how
>> it matches index WHERE conditions to query WHERE conditions, so you'll want
>> to use exactly the same condition text where possible.
>
> I think the query planner has gotten a little smarter of late:
>
> smarlowe=# select version();
> version
> ----------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.1.6 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
> (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
> (1 row)
>
> smarlowe=# drop table a;
> DROP TABLE
> smarlowe=# create table a (i int);
> CREATE TABLE
> smarlowe=# insert into a select null from generate_series(1,10000);
> INSERT 0 10000
> smarlowe=# insert into a values (10);
> INSERT 0 1
> smarlowe=# insert into a select null from generate_series(1,10000);
> INSERT 0 10000
> smarlowe=# create index on a (i) where i is not null;
> CREATE INDEX
> smarlowe=# explain select * from a where i =10;
> QUERY PLAN
> ------------------------------------------------------------------------
> Bitmap Heap Scan on a (cost=4.28..78.00 rows=100 width=4)
> Recheck Cond: (i = 10)
> -> Bitmap Index Scan on a_i_idx (cost=0.00..4.26 rows=100 width=0)
> Index Cond: (i = 10)
> (4 rows)
Actually after an analyze it just uses the plain index no bitmap scan.
So I get the same explain output with or without the "and i is not
null" clause added in.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Drawbacks of create index where is not null ?
2012-10-10 17:06 Drawbacks of create index where is not null ? Franck Routier <franck.routier@axege.com>
2012-10-11 05:26 ` Re: Drawbacks of create index where is not null ? Craig Ringer <ringerc@ringerc.id.au>
2012-10-11 05:42 ` Re: Drawbacks of create index where is not null ? Scott Marlowe <scott.marlowe@gmail.com>
@ 2012-10-11 22:31 ` Sergey Konoplev <gray.ru@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Sergey Konoplev @ 2012-10-11 22:31 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Craig Ringer <ringerc@ringerc.id.au>; Franck Routier <franck.routier@axege.com>; pgsql-performance
On Wed, Oct 10, 2012 at 10:42 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> I think the query planner has gotten a little smarter of late:
>
> smarlowe=# create index on a (i) where i is not null;
> CREATE INDEX
> smarlowe=# explain select * from a where i =10;
> QUERY PLAN
> ------------------------------------------------------------------------
> Bitmap Heap Scan on a (cost=4.28..78.00 rows=100 width=4)
> Recheck Cond: (i = 10)
> -> Bitmap Index Scan on a_i_idx (cost=0.00..4.26 rows=100 width=0)
> Index Cond: (i = 10)
> (4 rows)
It is even smarter a little bit more:
[local]:5432 grayhemp@grayhemp=# create index h_idx1 on h (n) where v
is not null;
CREATE INDEX
[local]:5432 grayhemp@grayhemp=# explain analyze select * from h where
v = '0.5';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on h (cost=1616.10..8494.68 rows=1 width=30)
(actual time=111.735..111.735 rows=0 loops=1)
Recheck Cond: (v IS NOT NULL)
Filter: (v = '0.5'::text)
-> Bitmap Index Scan on h_idx1 (cost=0.00..1616.10 rows=102367
width=0) (actual time=19.027..19.027 rows=100271 loops=1)
(5 rows)
--
Sergey Konoplev
a database and software architect
http://www.linkedin.com/in/grayhemp
Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Drawbacks of create index where is not null ?
2012-10-10 17:06 Drawbacks of create index where is not null ? Franck Routier <franck.routier@axege.com>
2012-10-11 05:26 ` Re: Drawbacks of create index where is not null ? Craig Ringer <ringerc@ringerc.id.au>
@ 2012-10-11 08:22 ` Franck Routier <franck.routier@axege.com>
1 sibling, 0 replies; 6+ messages in thread
From: Franck Routier @ 2012-10-11 08:22 UTC (permalink / raw)
To: pgsql-performance
Le 11/10/2012 07:26, Craig Ringer a écrit :
> * The partial index will only be used for queries that use the
> condition "WHERE col IS NOT NULL" themselves. The planner isn't
> super-smart about how it matches index WHERE conditions to query WHERE
> conditions, so you'll want to use exactly the same condition text
> where possible.
>
From my experiments, the planner seems to be smart enougth to tell that
"where col = 'myvalue' " will match with partial index "where col is not
null".
So it will use the index and not do a full tablescan. (this is on 8.4).
This is also what Scott says in his reply.
I'm not thinking of using more complex where predicat for my indexes,
just "is not null". So I think I should not be hit by this...
Thanks,
Franck
Attachments:
smime.p7s (application/pkcs7-signature, 4.4K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-10-11 22:31 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-10 17:06 Drawbacks of create index where is not null ? Franck Routier <franck.routier@axege.com>
2012-10-11 05:26 ` Craig Ringer <ringerc@ringerc.id.au>
2012-10-11 05:42 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-10-11 05:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-10-11 22:31 ` Sergey Konoplev <gray.ru@gmail.com>
2012-10-11 08:22 ` Franck Routier <franck.routier@axege.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