To: "Joshua Shanks" <jjshanks@gmail.com>
cc: "Craig Ringer" <craig@postnewspapers.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: query planner not using the correct index 
In-reply-to: <84f0acdb0808071501n15b0182ga7eca7b1be37d705@mail.gmail.com> 
References: <84f0acdb0808061435q5ebb607cjc311f1eb4da841a@mail.gmail.com>
	<489A623A.4090400@postnewspapers.com.au>
	<84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com>
	<489A6AF5.50908@postnewspapers.com.au>
	<84f0acdb0808070811l43a3da9eq240d272f8ddd105c@mail.gmail.com>
	<27696.1218143560@sss.pgh.pa.us>
	<84f0acdb0808071501n15b0182ga7eca7b1be37d705@mail.gmail.com>
Comments: In-reply-to "Joshua Shanks" <jjshanks@gmail.com>
	message dated "Thu, 07 Aug 2008 17:01:53 -0500"
Date: Thu, 07 Aug 2008 19:48:11 -0400
Message-ID: <29507.1218152891@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Joshua Shanks" <jjshanks@gmail.com> writes:
> SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
> pg_stats WHERE tablename = 'bars' AND attname='bars_id';
>  null_frac | n_distinct |   most_common_vals   |     most_common_freqs
> -----------+------------+----------------------+---------------------------
>          0 |         14 | {145823,47063,24895} | {0.484667,0.257333,0.242}

> Those 3 values in reality and in the stats account for 98% of the
> rows. actual distinct values are around 350

So you need to increase the stats target for this column.  With those
numbers the planner is going to assume that any value that's not one
of the big three appears about (1 - (0.484667+0.257333+0.242)) / 11
of the time, or several hundred times in 300K rows.  If n_distinct were
up around 350 it would be estimating just a dozen or so occurrences,
which should push the join plan into the shape you want.  It's likely
that it won't bother to include any more entries in most_common_vals
no matter how much you raise the target; but a larger sample should
definitely give it a better clue about n_distinct.

			regards, tom lane