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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux