Re: Different query plans for the same query

Hi Tom,

it would be really hard for us to change the underlying tables and the executed query. Is there any other way for us to avoid the really bad query (e.g. a hint for the planner)?

Regards,
Robert Hell

-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Gesendet: Freitag, 18. September 2009 17:43
An: Hell, Robert
Cc: tv(at)fuzzy(dot)cz; pgsql-performance(at)postgresql(dot)org
Betreff: Re: [PERFORM] Different query plans for the same query

"Hell, Robert" <Robert(dot)Hell(at)fabasoft(dot)com> writes:
> 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?

Garbage in, garbage out :-(. When you've got rowcount estimates that
are off by a couple orders of magnitude, it's unsurprising that you get
bad plan choices. In this case it appears that the "bad" and "good"
plans have just about the same estimated cost. I'm guessing that the
underlying statistics change a bit due to autovacuum activity, causing
the plan choice to flip unexpectedly.

The real fix would be to get the rowcount estimates more in line with
reality. I think the main problem is that in cases like

-> Index Scan using ind_atobjval on atobjval t6 (cost=0.00..12.04 rows=1 width=12) (actual time=0.032..0.953 rows=775 loops=1)
Index Cond: ((attrid = 285774255985991::bigint) AND (objval = 285774255985589::bigint))

the planner is supposing that the two conditions are independent when
they are not. Is there any way you can refactor the data representation
to remove the hidden redundancy?

regards, tom lane