Re: Different query plans for the same query

Hi,

sorry about that.

We use 100 as default_statistics_target for this database. The default should be 10 here - statistics are up to date I executed analyze manually this morning.

As mentioned before, the "bad plan" only happens once or twice a day - so the reproduction of that plan is very difficult.

I now played a little bit with statistics target for those three tables (alter table ...set statistics). It seems that there is a better query plan than the good one when using 10 as statistics target.

bad plan (sometimes with statistcs target 100, seconds after the good plan was chosen) - about 2 minutes: http://explain.depesz.com/s/gcr
good plan (most of the time with statistcs target 100) - about one second: http://explain.depesz.com/s/HX
very good plan (with statistics target 10) - about 15 ms: http://explain.depesz.com/s/qMc

What's the reason for that? I always thought increasing default statistics target should make statistics (and query plans) better.

Regards,
Robert

-----Ursprüngliche Nachricht-----
Von: tv(at)fuzzy(dot)cz [mailto:tv(at)fuzzy(dot)cz]
Gesendet: Freitag, 18. September 2009 11:20
An: Hell, Robert
Cc: pgsql-performance(at)postgresql(dot)org
Betreff: Re: [PERFORM] 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