From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joshua Shanks <jjshanks@gmail.com>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: query planner not using the correct index
Date: Thu, 07 Aug 2008 19:48:11 -0400
Message-ID: <29507.1218152891@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jjshanks@gmail.com, craig@postnewspapers.com.au
Subject: Re: query planner not using the correct index
In-Reply-To: <29507.1218152891@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox