From: Andrus <kobruleht2@hot.ee>
To: Scott Carey <scott@richrelevance.com>
To: pgsql-performance@postgresql.org
To: PFC <lists@peufeu.com>
Subject: Re: analyzing intermediate query
Date: Tue, 2 Dec 2008 20:58:43 +0200
Message-ID: <772C0DC9C4A04AA4939EB50F2707A791@andrusnotebook> (raw)
In-Reply-To: <BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9EF@EXVMBX018-1.exch018.msoutlookonline.net>
References: <gh38cg$1544$1@news.hub.org>
<op.uljfbad8cigqcu@soyouz>
<B52A6C218D2145378337CFDC89699D99@andrusnotebook>
<op.uljghcfxcigqcu@soyouz>
<95776070DDC841978DF8335DCF59D6BB@andrusnotebook>
<op.uljja8egcigqcu@soyouz>
<72246E216337406EAD285F5A45533672@andrusnotebook>
<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9EF@EXVMBX018-1.exch018.msoutlookonline.net>
Scott,
>Often times, switching an inner subselect that requires a distinct to a
>group by on that column yields better results. In this case, the IN should
>be equivalent, so it probably will not help. This would look like:
SELECT dok.*
FROM dok
JOIN (SELECT dokumnr FROM temptbl GROUP BY dokumnr ) x USING(dokumnr);
Thank you. This may be great idea.
I changed my query to use GROUP BY instead of DISTINCT
>Whether that hepls depends on how big dokumnr is and where the query
>bottleneck is.
I'm wondering how this can solve the issue when there is single or few
dokumnr columns.
Planner still thinks that temptbl projection contains 1000 rows and uses seq
scan instead of using bitmap index on dok table.
I tried
SELECT dok.*
FROM dok
JOIN (SELECT dokumnr FROM temptbl GROUP BY dokumnr ANALYZE ) x
USING(dokumnr);
but got error.
> Note there are subtle differences between DISTINCT and GROUP BY with
> respect to nulls.
dokumnr is int type and is not null always.
Andrus.
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: kobruleht2@hot.ee, scott@richrelevance.com, lists@peufeu.com
Subject: Re: analyzing intermediate query
In-Reply-To: <772C0DC9C4A04AA4939EB50F2707A791@andrusnotebook>
* 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