From: Tom Lane <tgl@sss.pgh.pa.us>
To: scott.marlowe <scott.marlowe@ihs.com>
Cc: Andreas Kostyrka <andreas@mtg.co.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index not used, performance problem
Date: Mon, 31 Mar 2003 13:53:54 -0500
Message-ID: <24341.1049136834@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.33.0303311118440.12130-100000@css120.ihs.com>
References: <Pine.LNX.4.33.0303311118440.12130-100000@css120.ihs.com>
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> So, create your index this way to make it smaller and faster:
> create index dxname on sometable (bool_field) where bool_field IS TRUE;
Also note that the index itself could be on some other column; for
example if you do
create index fooi on foo (intcol) where boolcol;
then a query like
select ... from foo where intcol >= 42 and boolcol;
could use the index to exploit both WHERE conditions.
> You have to, however, access it the same way. the proper
> way to reference a bool field is with IS [NOT] {TRUE|FALSE}
This strikes me as pedantry. "WHERE bool" (resp. "WHERE NOT bool") has
the same semantics and is easier to read, at least to me. (Of course,
if you think differently, then by all means write the form that seems
clearest to you.)
But yeah, the condition appearing in the actual queries had best match
what's used in the partial-index CREATE command exactly. The planner is
not real smart about deducing "this implies that".
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, scott.marlowe@ihs.com, andreas@mtg.co.at
Subject: Re: Index not used, performance problem
In-Reply-To: <24341.1049136834@sss.pgh.pa.us>
References: <Pine.LNX.4.33.0303311118440.12130-100000@css120.ihs.com> <24341.1049136834@sss.pgh.pa.us>
* 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