Re: Join Filter vs. Index Cond (performance regression 9.1->9.2+/HEAD)

On Fri, May 29, 2015 at 6:45 PM, Andrew Gierth
<andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
> Obviously it makes little sense to use an (a,b,c) index to look up just
> (a,b) and then filter on c; the question is, what is the planner doing
> that leads it to get this so wrong? Finding a workaround for it was not
> easy, either - the only thing that I found that worked was replacing the
> t1 join with a lateral join with an OFFSET 0 clause to nobble the
> planner entirely.

I agree. That looks like a bug.

The fact that it chooses index-only scans is also a little strange,
considering that they seem to be entirely ineffective. The tables
have never been vacuumed, so presumably, the all-visible bits are all
0, and the planner knows it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company