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

Поиск
Список
Период
Сортировка
От Miernik
Тема why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?
Дата
Msg-id 20080809203435.7227.0.NOFFLE@turbacz.local
обсуждение исходный текст
Ответы 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@sss.pgh.pa.us>)
Список pgsql-performance
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/

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Scott Marlowe"
Дата:
Сообщение: Re: Distant mirroring
Следующее
От: Tom Lane
Дата:
Сообщение: Re: why query plan for the inner SELECT of WHERE x IN is wrong, but when run the inner query alone is OK?