On Thu, Aug 7, 2008 at 4:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, you haven't told us how big any of these tables are, so it's
> hard to tell if the n_distinct value is wrong or not ... but in
> any case I don't think that the stats on attr1 have anything to do
> with your problem. The reason that the "fast" query is fast is that
> it benefits from the fact that there's only one bars row with
> bars_id = 12345. So the question is how many such rows does the
> planner now think there are (try "explain analyze select * from bars
> where bars_id = 12345"), and if it's badly wrong, then you need to be
> looking at the stats on bars.bars_id to find out why.
>
> regards, tom lane
>
foo is 400,000+ rows
bar is 300,000+ rows
I was just about to write back about this as with all my tinkering
today I figured that to be the root cause.
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
That plus the information information on
http://www.postgresql.org/docs/8.3/static/indexes-ordering.html make
it all make sense as to why the query planner is doing what it is
doing.
The only problem is we rarely if ever call the query with the where
clause containing those values. I did some testing and the planner
works awesome if we were to call those values but 99.9% of the time we
are calling other values.
It seems like the planner would want to get the result set from
bars.bars_id condition and if it is big using the index on the join to
avoid the separate sorting, but if it is small (0-5 rows which is our
normal case) use the primary key index to join and then just quickly
sort. Is there any reason the planner doesn't do this?
I found a way to run the query as a subselect which is fast for our
normal case but doesn't work for the edge cases so I might just have
to do count on the bars_id and then pick a query based on that.
