From: Franck Routier <franck.routier@axege.com>
To: pgsql-performance@postgresql.org
Subject: Re: Drawbacks of create index where is not null ?
Date: Thu, 11 Oct 2012 10:22:53 +0200
Message-ID: <507681DD.2000906@axege.com> (raw)
In-Reply-To: <5076586B.1000306@ringerc.id.au>
References: <5075AB0F.5030005@axege.com>
<5076586B.1000306@ringerc.id.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: franck.routier@axege.com
Subject: Re: Drawbacks of create index where is not null ?
In-Reply-To: <507681DD.2000906@axege.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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