Re: 'Interesting' prepared statement slowdown on large table join

On Thu, May 12, 2011 at 8:53 AM, Prodan, Andrei
<Andrei(dot)Prodan(at)awinta(dot)com> 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