To: pgsql-performance@postgresql.org
From: Miernik <public@public.miernik.name>
Subject: 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 22:34:35 +0200
Lines: 53
Message-ID: <20080809203435.7227.0.NOFFLE@turbacz.local>
Reply-To: Miernik <public@public.miernik.name>
User-Agent: tin/1.9.3-20080506 ("Dalintober") (UNIX) (Linux/2.6.25-2-686
	(i686))

Something goes wrong that this query plan thinks there is only gonna be
1 row from (SELECT uid FROM alog ... ) so chooses such query plan, and
thus it runs forever (at least so long that I didn't bother to wait,
like 10 minutes):


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:


miernik=> EXPLAIN SELECT uid FROM alog WHERE pid = 3452654 AND o = 1;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Bitmap Heap Scan on alog  (cost=100.21..9559.64 rows=3457 width=4)
   Recheck Cond: ((pid = 3452654::numeric) AND (o = 1::numeric))
   ->  Bitmap Index Scan on alog_pid_o_idx  (cost=0.00..99.35 rows=3457 width=0)
         Index Cond: ((pid = 3452654::numeric) AND (o = 1::numeric))
(4 rows)


Can't show you EXPLAIN ANALYZE for the first one, as it also runds
forver. For the second one, its consistent with the EXPLAIN.


Before it was running OK, but I recently disabled autovacuum and now run
VACUUM manually serveal times a day, and run ANALYZE manually on alog
and cnts tables before runnign the above. How may I fix this to work?

shared_buffers = 5MB
work_mem = 1MB
Machine is a 48 MB RAM Xen.

But not the disabling autovacuum broke it, but running ANALYZE manually
on the tables broke it, and I don't know why, I thougt ANALYZE would
improve the guesses?

-- 
Miernik
http://miernik.name/