Hi, I am running a 9.1 server at Ubuntu. When I upgraded to the current version
I did a pg_dump followed by pg_restore and found that the db was much faster.
But slowed down again after two days. I did the dump-restore again and could now
compare the two (actually identical) databases. This is a query of the old one
directly after a VACUUM ANALYSE:
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------------------
WindowAgg (cost=2231.56..2232.17 rows=22 width=59) (actual
time=16748.382..16749.203 rows=340 loops=1)
-> Sort (cost=2231.56..2231.62 rows=22 width=59) (actual
time=16748.360..16748.575 rows=340 loops=1)
Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN
(e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 60kB
-> Nested Loop (cost=0.00..2231.07 rows=22 width=59) (actual
time=0.125..16747.395 rows=340 loops=1)
-> Index Scan using authorid1 on book_author ba
(cost=0.00..73.94 rows=20 width=8) (actual time=0.034..11.453 rows=99 loops=1)
Index Cond: (authorid = 543)
-> Index Scan using foreign_key_bookid on editions e
(cost=0.00..107.76 rows=8 width=51) (actual time=90.741..169.031 rows=3
loops=99)
Index Cond: (bookid = ba.bookid)
Filter: mainname
Total runtime: 16752.146 ms
(11 Zeilen)
And here after dump-restore:
QUERY PLAN
--------------------------------------------------------------------------------
---------------------------------------------------------------------
WindowAgg (cost=2325.78..2326.41 rows=23 width=58) (actual time=18.583..19.387
rows=340 loops=1)
-> Sort (cost=2325.78..2325.84 rows=23 width=58) (actual
time=18.562..18.823 rows=340 loops=1)
Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN
(e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
Sort Method: quicksort Memory: 60kB
-> Nested Loop (cost=0.00..2325.26 rows=23 width=58) (actual
time=0.385..18.060 rows=340 loops=1)
-> Index Scan using authorid1 on book_author ba
(cost=0.00..73.29 rows=20 width=8) (actual time=0.045..0.541 rows=99 loops=1)
Index Cond: (authorid = 543)
-> Index Scan using foreign_key_bookid on editions e
(cost=0.00..112.49 rows=9 width=50) (actual time=0.056..0.168 rows=3 loops=99)
Index Cond: (bookid = ba.bookid)
Filter: mainname
Total runtime: 19.787 ms
(11 Zeilen)
server settings:
shared_buffers = 680MB
work_mem = 10MB
maintenance_work_mem = 64MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
effective_cache_size = 1500MB
No matter how much I vacuum or analyse the slow db, I don't get it faster.
I also checked for dead tuples - there are none.
