queries are fast after dump->restore but slow again after some days dispite vacuum

От: Felix Scheicher
Тема: queries are fast after dump->restore but slow again after some days dispite vacuum
Дата: ,
Msg-id: loom.20120719T133136-384@post.gmane.org
(см: обсуждение, исходный текст)
Ответы: Re: queries are fast after dump->restore but slow again after some days dispite vacuum  (Andrew Dunstan)
Список: pgsql-performance


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.


В списке pgsql-performance по дате сообщения:

От: mandavi
Дата:
Сообщение: Re: queries are fast after dump->restore but slow again after some days dispite vacuum
От: "mark"
Дата:
Сообщение: Deferred constraints performance impact ?