On Thu, May 12, 2011 at 8:53 AM, Prodan, Andrei <Andrei.Prodan@xxxxxxxxxx> wrote: > > @Jeff: thank you for the clear plan interpretation - but I'm afraid I > don't really understand the second bit: > 1) I provided the GOOD plan, so we already know what postgres thinks, > right? (Later edit: guess not. Doesn't work) > 2) There's no full table scan in any of the plans - it scans indices, > the problem seems to be that it scans them in the wrong order because it > thinks there are very few WHERE matches in big_table - which is > incorrect, as for that particular pair there is a huge amount of rows. Hi Andrei, "Explain analyze" only gives you the cost/rows for the plan components it actually executed, it doesn't give you costs for alternative rejected plans. Since the GOOD PLAN doesn't include the index scan in question, it doesn't give the estimated or actual rows for that scan under the stats/conditions that provoke the GOOD PLAN to be adopted. So to get that information, you have to design an experimental prepared query that will get executed using that particular scan, that way it will report the results I wanted to see. My concern is that the experimental query I proposed you use might instead decide to use a full table scan rather than the desired index scan. Although come to think of it, I think the same code will be used to arrive at the predicted number of rows regardless of whether it does a FTS or the desired index scan. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
