Query Plan difference between 9.3 and 9.5 for the worse. Help !!!

Поиск
Список
Период
Сортировка
От Jorge Torralba
Тема Query Plan difference between 9.3 and 9.5 for the worse. Help !!!
Дата
Msg-id CACut7uQajVzTL9R-9=qs1B69+yiGJxUBs4B0=OHk1GOBWwwwwg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query Plan difference between 9.3 and 9.5 for the worse. Help !!!  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-admin
We have been struggling with a query on our system that under 9.3 was taking 15ms or so to run and after upgrading to 9.5 it is taking over 2500ms.

You can see the explains are very different but I cannot figure out why when using the same data on both system.

data from pg_stats on both systems is almost identical.

conf file on both systems are very close match.

An ideas what could be causing this behavior?

9.3 Explain


 Index Scan using target_account_type_idx on targets t  (cost=3.92..146295.18 rows=701 width=42)

   Index Cond: ((account_id = ANY ('removed for privacy'::bigint[])) AND (type = 'KeyTransaction'::text))

   Filter: (SubPlan 1)

   SubPlan 1

     ->  Limit  (cost=91.69..91.69 rows=1 width=334)

           ->  Sort  (cost=91.69..92.14 rows=183 width=334)

                 Sort Key: c.id

                 ->  Nested Loop  (cost=0.85..90.77 rows=183 width=334)

                       ->  Index Scan using condition_targets_target_id_idx on condition_targets ct  (cost=0.43..14.68 rows=9 width=56)

                             Index Cond: (target_id = t.id)

                       ->  Index Scan using index_conditions_family_id_archived_at on conditions c  (cost=0.42..8.45 rows=1 width=278)

                             Index Cond: (family_id = ct.condition_family_id)

                             Filter: ((id >= ct.created_condition_id) AND ((id < ct.removed_condition_id) OR (ct.removed_condition_id IS NULL)))

(13 rows)




9.5 Explain



 Hash Join  (cost=655687.56..674049.55 rows=28 width=42)

   Hash Cond: (t.id = ct.target_id)

   ->  Index Scan using target_account_type_idx on targets t  (cost=0.56..18356.43 rows=1556 width=50)

         Index Cond: ((account_id = ANY ('{removed for privacy }'::bigint[])) AND

 (type = 'KeyTransaction'::text))

   ->  Hash  (cost=652267.43..652267.43 rows=273566 width=8)

         ->  HashAggregate  (cost=649531.77..652267.43 rows=273566 width=8)

               Group Key: ct.target_id

               ->  Nested Loop  (cost=2545.42..518241.47 rows=52516119 width=8)

                     ->  Bitmap Heap Scan on conditions c  (cost=2544.99..100917.01 rows=144108 width=16)

                           Recheck Cond: (archived_at IS NULL)

                           ->  Bitmap Index Scan on index_conditions_family_id_archived_at  (cost=0.00..2508.96 rows=144108 width=0)

                     ->  Index Scan using condition_targets_idx on condition_targets ct  (cost=0.43..2.53 rows=37 width=32)

                           Index Cond: ((condition_family_id = c.family_id) AND (c.id >= created_condition_id))

                           Filter: ((c.id < removed_condition_id) OR (removed_condition_id IS NULL))

(14 rows)




--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

В списке pgsql-admin по дате отправления:

Предыдущее
От: Poul Kristensen
Дата:
Сообщение: Re: /var/run/postgresql/.s.PGSQL.5432 should be 5433
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Query Plan difference between 9.3 and 9.5 for the worse. Help !!!