[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Bad plan by Planner (Already resolved?)
From:       Robins Tharakan <robins.tharakan () comodo ! com>
Date:       2011-10-29 14:54:28
Message-ID: 4EAC10D4.1080300 () comodo ! com
[Download RAW message or body]


Thanks Tom!

Regret the delay in reply, but two of the three guesses were spot-on and 
resolved the doubt. 8.4.9 does take care of this case very well.

On 10/27/2011 01:27 AM, Tom Lane wrote:
> I suspect that you're just fooling yourself here, and the "optimized"
> query is no such thing.
:) I actually meant 'faster' query, but well...


> 1. The tables are horrendously bloated on the first database, so that
> many more pages have to be touched to get the same number of tuples.
> This would likely indicate an improper autovacuum configuration.
I believe you've nailed it pretty accurately. The tables are 
horrendously bloated and I may need to tune AutoVacuum to be much more 
aggressive than it is. I did see that HashAggregate makes only a minor 
difference, but what didn't strike is that the slowness could be bloat.


> 2. You failed to account for caching effects, ie the first example
> is being run "cold" and has to actually read everything from disk,
> whereas the second example has everything it needs already in RAM.
> In that case the speed differential is quite illusory.
On hindsight, this was a miss. Should have warmed the caches before 
posting. Re-running this query multiple times, brought out the result in 
~100ms.

> BTW, how come is it that "SELECT large_table_b.field_b FROM
> large_table_b WHERE field_a = 2673056" produces no duplicate field_b
> values?  Is that just luck?  Is there a unique constraint on the table
> that implies it will happen?
Its just luck. Sometimes the corresponding values genuinely don't exist 
in the other table, so that's ok.

--
Robins Tharakan


["smime.p7s" (application/pkcs7-signature)]

[prev in list] [next in list] [prev in thread] [next in thread]