Skip site navigation (1) Skip section navigation (2)

Re: strange index behaviour with different statistics target

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: strange index behaviour with different statistics target
Date: 2009-01-13 23:23:08
Message-ID: Pine.LNX.4.64.0901131513480.5588@discord.home.frostconsultingllc.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, 13 Jan 2009, Tom Lane wrote:

> Jeff Frost <jeff(at)frostconsultingllc(dot)com> writes:
>> So, my question is, should changing the stats target on the shape column
>> affect the stats for the content_id and content_type columns?
>
> It would change the size of the sample for the table, which might
> improve the accuracy of the stats.  IIRC you'd still get the same number
> of histogram entries and most-common-values for the other columns, but
> they might be more accurate.

Why would they be more accurate?  Do they somehow correlate with the other 
column's histogram and most-common-values when the stats target is increased 
on that column?

The planner is choosing a plan I like for the query, I'm just trying to 
understand why it's doing that since the planner thinks the gist index is 
going to give it a single row (vs the 2827 rows it actually gets) and the fact 
that the cost didn't change for perusing the gist index.  I guess I was 
expecting the estimated rowcount and cost for perusing the gist index to go up 
and when it didn't I was pleasantly surprised to find I got a plan I wanted 
anyway.

>
>> Also, why does the index on content_id win out over the compound index
>> on (content_type, content_id)?
>
> It's deciding (apparently correctly, from the explain results) that the
> larger index isn't increasing the selectivity enough to be worth its
> extra search cost.  I suppose content_type = 'Story' isn't very
> selective in this table?

Ah!  You're right, especially with this content_id!

-- 
Jeff Frost, Owner 	<jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-01-13 23:40:21
Subject: Re: strange index behaviour with different statistics target
Previous:From: Tom LaneDate: 2009-01-13 23:06:01
Subject: Re: strange index behaviour with different statistics target

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group