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

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/

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance