Re: Different query plans for the same query

> Hi all,
>
> on our PostgreSQL 8.3.1 (CentOS 5.3 64-bit) two different query plans
> for one of our (weird) queries are generated. One of the query plans
> seems to be good (and is used most of the time). The other one is bad -
> the query takes about 2 minutes and the database process, which is
> executing the query, is cpu bound during this time.
>
> After several tries I was able to reproduce the problem when executing
> the query with EXPLAIN ANALYZE. The bad query plan was generated only
> seconds after the good one was used when executing the query. What's the
> reasond for the different query plans? Statistics are up to date.
>
> ...

Hi,

please, when posting an explain plan, either save it into a file and
provide a URL (attachments are not allowed here), or use
explain.depesz.com or something like that. This wrapping makes the plan
unreadable so it's much more difficult to help you.

I've used the explain.depesz.com (this time):

- good plan: http://explain.depesz.com/s/HX
- bad plan: http://explain.depesz.com/s/gcr

It seems the whole problem is caused by the 'Index Scan using ind_atobjval
on atobjval t9' - in the first case it's executed only 775x, but in the
second case it's moved to the nested loop (one level deeper) and suddenly
it's executed 271250x. And that causes the huge increase in cost.

Why is this happening? I'm not sure, but I'm not quite sure the statistics
are up to data and precise enough - some of the steps state 'rows=1'
estimate, but 'rows=775' in the actual results.

Have you tried to increase target on the tables? That might provide more
accurate stats, thus better estimates.

regards
Tomas