Hello everyone.
Can anyone explain why this table which has never had more than a couple rows in it shows > 500k in the query planner even after running vacuum full. Its terribly slow to return 2 rows of data. The 2 rows in it are being updated a lot but I couldn't find any explanation for this behavior. Anything I could try besides droping db and recreating?
Thanks - Russ
toolshed=# explain analyze select * from stock_log_positions ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on stock_log_positions (cost=0.00..10907.77 rows=613577 width=22) (actual time=701.39..701.41 rows=2 loops=1)
Total runtime: 701.54 msec
(2 rows)
toolshed=# vacuum full analyze verbose stock_log_positions;
INFO: --Relation public.stock_log_positions--
INFO: Pages 4773: Changed 1, reaped 767, Empty 0, New 0; Tup 613737: Vac 57620, Keep/VTL 613735/613713, UnUsed 20652, MinLen 52, MaxLen 52; Re-using: Free/Avail. Space 4322596/4322596; EndEmpty/Avail. Pages 0/4773.
CPU 9.11s/13.68u sec elapsed 22.94 sec.
INFO: Index idx_stock_log_positions_when_log_filename: Pages 9465; Tuples 613737: Deleted 57620.
CPU 1.55s/1.27u sec elapsed 6.69 sec.
INFO: Rel stock_log_positions: Pages: 4773 --> 4620; Tuple(s) moved: 59022.
CPU 1.00s/4.45u sec elapsed 8.83 sec.
INFO: Index idx_stock_log_positions_when_log_filename: Pages 9778; Tuples 613737: Deleted 2897.
CPU 1.32s/0.44u sec elapsed 6.23 sec.
INFO: Analyzing public.stock_log_positions
VACUUM
toolshed=# explain analyze select * from stock_log_positions ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on stock_log_positions (cost=0.00..10757.37 rows=613737 width=22) (actual time=789.21..789.24 rows=2 loops=1)
Total runtime: 789.40 msec
(2 rows)
toolshed=# select * from stock_log_positions ;
when_log | filename | position
------------+--------------+----------
2003-12-11 | ActiveTrader | 0
2003-12-11 | Headlines | 0
(2 rows)