Re: not null partial index?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: not null partial index?
Date: 2004-10-31 06:38:25
Message-ID: 25028.1099204705@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> Sorry for the confusing and foolish question. However still I wonder
> why my expression(functional) index does not work.

You could likely have gotten it to match to a query like
SELECT ... WHERE (bid is not null) = true;
which would have the proper form of "(indexed value) = constant".

Whether the planner would have picked an indexscan for that without
coercion is another issue. IIRC 7.4 does not keep statistics for
functional indexes and so it is unlikely to get the rowcount estimates
right for a query expressed this way. (Looking back at your example,
you don't seem to have run an ANALYZE anyway :-()

A partial index is likely to work better for this problem on both
counts: you can write just "WHERE bid is not null", and the normal
stats will (I think) be able to estimate that well.

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2004-10-31 06:45:20 Re: Charset/collate support and function parameters
Previous Message Dennis Bjorklund 2004-10-31 06:24:38 Re: Charset/collate support and function parameters