To: pgsql-performance@postgresql.org
From: Miernik <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?
Date: Sat, 9 Aug 2008 23:19:32 +0200
Lines: 55
Message-ID: <20080809211932.7252.0.NOFFLE@turbacz.local>
References: <20080809203435.7227.0.NOFFLE@turbacz.local>
	<11224.1218315478@sss.pgh.pa.us>
Reply-To: Miernik <public@public.miernik.name>
User-Agent: tin/1.9.3-20080506 ("Dalintober") (UNIX) (Linux/2.6.25-2-686
	(i686))

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Miernik <public@public.miernik.name> writes:
>> miernik=> EXPLAIN UPDATE cnts SET p0 = FALSE WHERE uid IN (SELECT uid FROM alog WHERE pid = 3452654 AND o = 1);
>>                                           QUERY PLAN
>> -----------------------------------------------------------------------------------------------
>>  Nested Loop IN Join  (cost=0.00..3317.34 rows=1 width=44)
>>    ->  Seq Scan on cnts  (cost=0.00..36.00 rows=2000 width=44)
>>    ->  Index Scan using alog_uid_idx on alog  (cost=0.00..296.95 rows=1 width=4)
>>          Index Cond: ((alog.uid)::integer = (cnts.uid)::integer)
>>          Filter: ((alog.pid = 3452654::numeric) AND (alog.o = 1::numeric))
>> (5 rows)
> 
>> But if I give him only the inner part, it makes reasonable assumptions
>> and runs OK:
> 
> What's the results for
> 
> explain select * from cnts, alog where alog.uid = cnts.uid

miernik=> explain select * from cnts, alog where alog.uid = cnts.uid;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Hash Join  (cost=61.00..71810.41 rows=159220 width=76)
   Hash Cond: ((alog.uid)::integer = (cnts.uid)::integer)
   ->  Seq Scan on alog  (cost=0.00..54951.81 rows=3041081 width=37)
   ->  Hash  (cost=36.00..36.00 rows=2000 width=39)
         ->  Seq Scan on cnts  (cost=0.00..36.00 rows=2000 width=39)
(5 rows)

> 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...

How can I bring it back to working? Like un-run ANALYZE on that table or
something? All was running reasonably well before I changed from
autovacuum to running ANALYZE manually, and I thought I would improve
performance... ;(

-- 
Miernik
http://miernik.name/