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?
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>
Comments: In-reply-to Miernik <public@public.miernik.name>
	message dated "Sat, 09 Aug 2008 23:19:32 +0200"
Date: Sat, 09 Aug 2008 17:37:29 -0400
Message-ID: <11640.1218317849@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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