Re: why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Miernik <public(at)public(dot)miernik(dot)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/