Re: How to interpret this explain analyse?

Richard Huxton <dev(at)archonet(dot)com> writes:
> Joost Kraaijeveld wrote:
>> 2. If not, what can I do about it?

> The key thing to look for here is the number of rows. If PG expects say
> 100 rows but there are instead 10,000 then it may choose the wrong plan.
> In this case the estimate is 1,100,836 and the actual is 1,104,380 -
> very close.

On the surface this looks like a reasonable plan choice. If you like
you can try the other two basic types of join plan by turning off
enable_hashjoin, which will likely drive the planner to use a merge
join, and then also turn off enable_mergejoin to get a nested loop
(or if it thinks nested loop is second best, turn off enable_nestloop
to see the behavior with a merge join).

What's important in comparing different plan alternatives is the ratios
of estimated costs to actual elapsed times. If the planner is doing its
job well, those ratios should be similar across all the alternatives
(which implies of course that the cheapest-estimate plan is also the
cheapest in reality). If not, it may be appropriate to fool with the
planner's cost estimate parameters to try to line up estimates and
reality a bit better.

See
http://www.postgresql.org/docs/8.0/static/performance-tips.html
for more detail.

regards, tom lane