Re: 9.5alpha1: Partial index not used

On 2015-07-31 20:03:41 -0400, Tom Lane wrote:
> "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> > Consider this table:
> > ...
> >     "concept_start_idx" btree (start) WHERE start IS NOT NULL
>=20
> > and this query:
>=20
> > select id as IdValue, canonicalname as Description, null as IsLeaf from=
 public.concept where start;
>=20
> > Clearly this should be able to use the partial index (if start is true
> > it is also not null)
>=20
> As you surmise, there's no proof rule for that.
>=20
> > 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).
>=20
> ... 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.=20

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

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