Re: 9.5alpha1: Partial index not used

On 2015-07-31 20:03:41 -0400, Tom Lane wrote:
> "Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> writes:
> > Consider this table:
> > ...
> > "concept_start_idx" btree (start) WHERE start IS NOT NULL
>
> > and this query:
>
> > select id as IdValue, canonicalname as Description, null as IsLeaf from public.concept where start;
>
> > Clearly this should be able to use the partial index (if start is true
> > it is also not null)
>
> As you surmise, there's no proof rule for that.
>
> > and since there are only 3 out of 3 million rows in result it would
> > also be beneficial (and PostgreSQL 9.1 did use the index).
>
> ... and there wasn't in 9.1 either. I get a seqscan from examples like
> this in every branch back to 8.3, which is as far back as I can test
> conveniently.

This is weird. I do remember that I tested various indexes until I found
one which was actually used on the development server (which was 9.1 at
the time and upgraded to 9.5 recently). However, on the test system
(still on 9.1) I can't get postgres to use the index either.

So I must assume that I'm either completely misremembering or that I
changed the index after that for some reason I don't remember.
Sorry, my bad.

> Only the last case produces use of the index. I agree that it'd be better
> if they all did, but I'm disinclined to consider it a bug fix unless you
> can show a specific case in which there's a performance regression from
> older releases.

"grossly incorrect plan choices are cause for a bug report" (from
http://www.postgresql.org/docs/9.5/static/indexes-partial.html) ;-)

Yeah, not a regression, and even though I consider that "grossly
incorrect", not high on my priority list (since the "workaround" is
arguably "more correct" in my case). So please consider it a feature
request instead of a bug report.

hp

--
_ | Peter J. Holzer | I want to forget all about both belts and
|_|_) | | suspenders; instead, I want to buy pants
| | | hjp(at)hjp(dot)at | that actually fit.
__/ | http://www.hjp.at/ | -- http://noncombatant.org/