Skip site navigation (1) Skip section navigation (2)

Re: Different query plans for the same query

From: "Hell, Robert" <Robert(dot)Hell(at)fabasoft(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <tv(at)fuzzy(dot)cz>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Different query plans for the same query
Date: 2009-09-24 06:41:22
Message-ID: B710F3299F04664DB6B37C258FDEEB94038ACB45@FABAMAIL.fabagl.fabasoft.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: jesperDate: 2009-09-24 08:27:46
Subject: Speed while runnning large transactions.
Previous:From: Tom LaneDate: 2009-09-24 02:53:08
Subject: Re: Use of sequence rather than index scan for one text column on one instance of a database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group