Slow performance after restoring a dump

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 
Hi, I was wondering if someone could help us work out why this query is so slow.

We've just dumped a database (Postgresql 9.1) and restored it to a new instance (AWS RDS 9.6) (via pg_dump, restored to psql)

We immediately see that the following fairly straightforward query is now extremely slow with a huge number of shared buffers hit.
On the new instance it takes 25 seconds. On the original system, 0.05 seconds.

The database schemas are both the same (with the same columns indexed) so I'm guessing it must be a configuration issue to make the planner go down a different route.
I have run an analyse on the whole database since restoring.

Also, if on the new instance, I disable indexscans, the query will take 0.047 seconds.

Can someone point us in the right direction on what's going on here?


Query:

explain (buffers,analyse)  select
            trans_date
            from stock_trans s
            join account_trans a using(account_trans_id)
            where product_id=100
            and credit_stock_account_id=3
            order by trans_date desc
            limit 1;


Bad Performance on New Instance:

https://explain.depesz.com/s/0HXq


                                                                                    QUERY PLAN          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.87..9086.72 rows=1 width=4) (actual time=25829.287..25829.287 rows=0 loops=1)
   Buffers: shared hit=43944066
   ->  Nested Loop  (cost=0.87..6196547.28 rows=682 width=4) (actual time=25829.286..25829.286 rows=0 loops=1)
         Buffers: shared hit=43944066
         ->  Index Scan Backward using account_trans_date_idx on account_trans a  (cost=0.43..392996.60 rows=11455133 width=8) (actual time=0.007..3401.027 rows=11455133 loops=1)
               Buffers: shared hit=251082
         ->  Index Scan using stock_trans_account_trans_idx on stock_trans s  (cost=0.43..0.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=11455133)
               Index Cond: (account_trans_id = a.account_trans_id)
               Filter: ((product_id = 2466420) AND (credit_stock_account_id = 3))
               Rows Removed by Filter: 1
               Buffers: shared hit=43692984
 Planning time: 0.271 ms
 Execution time: 25829.316 ms
(13 rows)


Disabled indexscan:

=> set enable_indexscan=off;

https://explain.depesz.com/s/zTVn

                                                                      QUERY PLAN                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=11049.80..11049.81 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=1)
   Buffers: shared hit=3
   ->  Sort  (cost=11049.80..11051.51 rows=682 width=4) (actual time=0.017..0.017 rows=0 loops=1)
         Sort Key: a.trans_date DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3
         ->  Nested Loop  (cost=35.99..11046.39 rows=682 width=4) (actual time=0.011..0.011 rows=0 loops=1)
               Buffers: shared hit=3
               ->  Bitmap Heap Scan on stock_trans s  (cost=31.59..5301.09 rows=682 width=4) (actual time=0.011..0.011 rows=0 loops=1)
                     Recheck Cond: (product_id = 2466420)
                     Filter: (credit_stock_account_id = 3)
                     Buffers: shared hit=3
                     ->  Bitmap Index Scan on stock_trans_product_idx  (cost=0.00..31.42 rows=1465 width=0) (actual time=0.009..0.009 rows=0 loops=1)
                           Index Cond: (product_id = 2466420)
                           Buffers: shared hit=3
               ->  Bitmap Heap Scan on account_trans a  (cost=4.40..8.41 rows=1 width=8) (never executed)
                     Recheck Cond: (account_trans_id = s.account_trans_id)
                     ->  Bitmap Index Scan on account_trans_pkey  (cost=0.00..4.40 rows=1 width=0) (never executed)
                           Index Cond: (account_trans_id = s.account_trans_id)
 Planning time: 0.272 ms
 Execution time: 0.047 ms
(21 rows)


Explain from the same query on the original database:

https://explain.depesz.com/s/WHKJ

                                                                          QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9811.51..9811.52 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=1)
   Buffers: shared hit=3
   ->  Sort  (cost=9811.51..9813.23 rows=685 width=4) (actual time=0.019..0.019 rows=0 loops=1)
         Sort Key: a.trans_date
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3
         ->  Nested Loop  (cost=0.00..9808.09 rows=685 width=4) (actual time=0.014..0.014 rows=0 loops=1)
               Buffers: shared hit=3
               ->  Index Scan using stock_trans_product_idx on stock_trans s  (cost=0.00..3300.01 rows=685 width=4) (actual time=0.014..0.014 rows=0 loops=1)
                     Index Cond: (product_id = 2466420)
                     Filter: (credit_stock_account_id = 3)
                     Buffers: shared hit=3
               ->  Index Scan using account_trans_pkey on account_trans a  (cost=0.00..9.49 rows=1 width=8) (never executed)
                     Index Cond: (account_trans_id = s.account_trans_id)
 Total runtime: 0.050 ms
(15 rows)


Regards,
--
David 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux