To: pgsql-performance@postgresql.org
From: Felix Scheicher <mandavi@web.de>
Subject: queries are fast after dump->restore but slow again after some days
	dispite vacuum
Date: Thu, 19 Jul 2012 11:33:28 +0000 (UTC)
Lines: 63
Message-ID: <loom.20120719T133136-384@post.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Loom/3.14 (http://gmane.org/)

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.