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/
