From: tv@fuzzy.cz
To: sthomas@peak6.com
Cc: Adarsh Sharma <adarsh.sharma@orkash.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Re-Reason of Slowness of Query
Date: Wed, 23 Mar 2011 15:16:01 +0100
Message-ID: <557a8c61e87e5e54ae205daadd8f0ca4.squirrel@sq.gransy.com> (raw)
In-Reply-To: <4D89F6E5.7010803@peak6.com>
References: <4D89BABF.4080806@orkash.com>
<4D89F6E5.7010803@peak6.com>
> On 03/23/2011 04:17 AM, Adarsh Sharma wrote:
>
>> explain analyze select distinct(p.crawled_page_id) from page_content
>> p where NOT EXISTS (select 1 from clause2 c where c.source_id =
>> p.crawled_page_id);
>
> You know... I'm surprised nobody has mentioned this, but DISTINCT is
> very slow unless you have a fairly recent version of Postgres that
> replaces it with something faster. Try this:
Nobody mentioned that because the explain plan already uses hash aggregate
(instead of the old sort)
HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual
time=7047.259..7050.261 rows=72 loops=1)
which means this is at least 8.4. Plus the 'distinct' step uses less than
1% of total time, so even if you improve it the impact will be minimal.
regards
Tomas
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: tv@fuzzy.cz, sthomas@peak6.com, adarsh.sharma@orkash.com
Subject: Re: Re-Reason of Slowness of Query
In-Reply-To: <557a8c61e87e5e54ae205daadd8f0ca4.squirrel@sq.gransy.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