Excessive rows/tuples seriously degrading query performance - Mailing list pgsql-performance

From Chadwick, Russell
Subject Excessive rows/tuples seriously degrading query performance
Date
Msg-id B37E267CB3D7C841A09F5078F117AFBC059409@q.idc-mcs.com
Whole thread Raw
Responses Re: Excessive rows/tuples seriously degrading query
List pgsql-performance
 
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)

pgsql-performance by date:

Previous
From: "Sean P. Thomas"
Date:
Subject: Optimizing FK & PK performance...
Next
From: "David Shadovitz"
Date:
Subject: Re: Measuring execution time for sql called from PL/pgSQL