Re: Planner chooses multi-column index in 9.2 when maybe it should not

On Wed, Oct 10, 2012 at 01:31:29PM -0400, Tom Lane wrote:
> The above doesn't seem like a regression to me.  You told it not to use
> a seqscan, and it didn't.  (The reason it now considers the index is
> that an index-only scan is possible; before 9.2 there was no reason to
> consider an indexscan at all given this query, so you got the seqscan
> despite the attempted disable.)

Ah...index-only scans. Now it makes sense.

...
> It's not obvious that this is a worse plan than a seqscan --- the
> index-only scans will only have to read the index not the heap, at least
> if the heap is all-visible.  If it's coming out slower, then that's a
> question of whether the cost estimates match reality.  I'd wonder how
> many heap fetches occur anyway, and also whether you've tweaked the
> planner cost parameters.

We've lowered random_page_cost, but raising it back to the default does
not help.

> You should be able to force it back to the seqscan based plan by turning
> off enable_indexscan or enable_indexonlyscan.  It would be useful to
> see EXPLAIN ANALYZE (not just EXPLAIN) results for both this plan and
> the seqscan plan in 9.2.

Thanks, I will play around with both a better test case and getting some
explain analyzes (they were taking too long to run; thought I should get
the email out first in case it was something obvious).


--
Greg Sabino Mullane greg@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8