From: | Greg Sabino Mullane <greg(at)endpoint(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Planner chooses multi-column index in 9.2 when maybe it should not |
Date: | 2012-10-10 17:45:33 |
Message-ID: | 20121010174533.GM9910@tinybird.home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)endpoint(dot)com
End Point Corporation
PGP Key: 0x14964AC8
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2012-10-10 17:54:49 | Re: pg_upgrade not detecting version properly |
Previous Message | Tom Lane | 2012-10-10 17:31:29 | Re: Planner chooses multi-column index in 9.2 when maybe it should not |