From: Robins Tharakan <robins.tharakan@comodo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Bad plan by Planner (Already resolved?)
Date: Sat, 29 Oct 2011 20:12:28 +0530
Message-ID: <4EAC10D4.1080300@comodo.com> (raw)
In-Reply-To: <9507.1319659042@sss.pgh.pa.us>
References: <4E9D2ECA0200002500042131@gw.wicourts.gov>
<4EA6952E.90209@comodo.com>
<9507.1319659042@sss.pgh.pa.us>
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
Attachments:
smime.p7s (application/pkcs7-signature, 6.4K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robins.tharakan@comodo.com
Subject: Re: Bad plan by Planner (Already resolved?)
In-Reply-To: <4EAC10D4.1080300@comodo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox