From: Tom Lane <tgl@sss.pgh.pa.us>
To: Miernik <public@public.miernik.name>
Cc: pgsql-performance@postgresql.org
Subject: Re: why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?
Date: Sat, 09 Aug 2008 17:37:29 -0400
Message-ID: <11640.1218317849@sss.pgh.pa.us> (raw)
In-Reply-To: <20080809211932.7252.0.NOFFLE@turbacz.local>
References: <20080809203435.7227.0.NOFFLE@turbacz.local>
<11224.1218315478@sss.pgh.pa.us>
<20080809211932.7252.0.NOFFLE@turbacz.local>
Miernik <public@public.miernik.name> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If necessary, turn off enable_hashjoin and enable_mergejoin so we can
>> see a comparable plan.
> After doing that it thinks like this:
> miernik=> explain select * from cnts, alog where alog.uid = cnts.uid;
> QUERY PLAN
> -----------------------------------------------------------------------------------------
> Nested Loop (cost=4.95..573640.43 rows=159220 width=76)
> -> Seq Scan on cnts (cost=0.00..36.00 rows=2000 width=39)
> -> Bitmap Heap Scan on alog (cost=4.95..285.80 rows=80 width=37)
> Recheck Cond: ((alog.uid)::integer = (cnts.uid)::integer)
> -> Bitmap Index Scan on alog_uid_idx (cost=0.00..4.93 rows=80 width=0)
> Index Cond: ((alog.uid)::integer = (cnts.uid)::integer)
> (6 rows)
> Trying EXPLAIN ANALZYE now on this makes it run forever...
It couldn't run very long if those rowcounts were accurate. How many
rows in "cnts" really? How big is "alog", and how many of its rows join
to "cnts"?
While I'm looking at this, what's the real datatypes of the uid columns?
Those explicit coercions seem a bit fishy.
> How can I bring it back to working?
It's premature to ask for a solution when we don't understand the
problem.
regards, tom lane
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: tgl@sss.pgh.pa.us, public@public.miernik.name
Subject: Re: why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?
In-Reply-To: <11640.1218317849@sss.pgh.pa.us>
* 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