Re: Same database, different query plans

I think I can answer this cause I recently had a similar problem. There is a voodoo setting in postgresql called "analyze target". It controls how much statistic information is kept per table. This information affects the query planner. If it makes a bad guess based on insufficient statistics data, it will absolutely kill performance (BTW, the documentation never makes it explicit). Increase default_analyze_target (I think that's what it's called, look up the docs) at least tenfold, restart postgresql, and run analyze again.

BTW, the default postgresql settings are WAY too conservative. I am now looking into tuning and there are a lot of things that need to be turned up.

hope this helps,

Eugene

>
> From: Michael Fuhr <mike(at)fuhr(dot)org>
> Date: 2005/08/18 Thu AM 10:05:14 EST
> To: WireSpot <wirespot(at)gmail(dot)com>
> CC: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Same database, different query plans
>
> On Thu, Aug 18, 2005 at 12:03:59PM +0300, WireSpot wrote:
> > The actual SELECT results (ie. non EXPLAIN) are identical in both
> > cases. The indexes and so on are identical. I've done a reindexing and
> > vacuuming on both of them just to be sure.
> >
> > As you can see, there's quite a bit of a difference between 0.3 ms and
> > 398 ms, and it shows. I haven't touched the query planning options.
> > Why the different planning and what can I do to fix the misguided one?
>
> Have you run ANALYZE or VACUUM ANALYZE in both databases to update
> the planner's statistics? If you have and get the same results,
> then it might be interesting to see the output of the following on
> both systems:
>
> SET enable_mergejoin TO off;
> SET enable_nestloop TO on;
> EXPLAIN ANALYZE SELECT ...
>
> SET enable_mergejoin TO on;
> SET enable_nestloop TO off;
> EXPLAIN ANALYZE SELECT ...
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>