From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
Cc: | Andreas Kostyrka <andreas(at)mtg(dot)co(dot)at>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Index not used, performance problem |
Date: | 2003-03-31 18:53:54 |
Message-ID: | 24341.1049136834@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Shankar K | 2003-03-31 20:55:44 | ext3 filesystem / linux 7.3 |
Previous Message | scott.marlowe | 2003-03-31 18:21:45 | Re: Index not used, performance problem |