On 10/18/14, 8:58 AM, Bruce Momjian wrote:
On Fri, Oct 17, 2014 at 11:03:04PM -0400, Tom Lane wrote:
Bruce Momjian <br...@momjian.us> writes:
On Fri, Oct 17, 2014 at 06:15:37PM -0400, Tom Lane wrote:
Those stats were perfectly valid: what the planner is looking for is
accurate minimum and maximum values for the index's leading column, and
that's what it got. You're correct that a narrower index could have given
the same results with a smaller disk footprint, but the planner got the
results it needed from the index you provided for it to work with.
Uh, why is the optimizer looking at the index on a,b,c and not just the
stats on column a, for example? I am missing something here.
Because it needs up-to-date min/max values in order to avoid being
seriously misled about selectivities of values near the endpoints.
See commit 40608e7f949fb7e4025c0ddd5be01939adc79eec.
Oh, I had forgotten we did that. It is confusing that there is no way
via EXPLAIN to see the access, making the method of consulting pg_stat_*
and using EXPLAIN unreliable. Should we document this somewhere?
I think we should. The common (mis)conception is that pg_stats shows
*user-driven* access, not access because of stuff the system is doing.
This is actually a huge problem for anyone who's trying to figure out how
useful indexes are; they see usage and thing they have queries that are using
the index when in reality they don't.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
