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
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 |