pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Plan difference between 9.3 and 9.5 for the worse. Help !!!
2+ messages / 2 participants
[nested] [flat]
* Query Plan difference between 9.3 and 9.5 for the worse. Help !!!
@ 2016-09-28 17:53 Jorge Torralba <jorge.torralba@gmail.com>
2016-09-28 22:28 ` Re: Query Plan difference between 9.3 and 9.5 for the worse. Help !!! Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Jorge Torralba @ 2016-09-28 17:53 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Query Plan difference between 9.3 and 9.5 for the worse. Help !!!
2016-09-28 17:53 Query Plan difference between 9.3 and 9.5 for the worse. Help !!! Jorge Torralba <jorge.torralba@gmail.com>
@ 2016-09-28 22:28 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2016-09-28 22:28 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: pgsql-admin
Jorge Torralba <jorge.torralba@gmail.com> writes:
> 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.
Hard to comment on this when you haven't shown us the query nor the
table schemas. But I'm going to guess based on the lack of a Limit
node in the second plan that your query involves WHERE EXISTS(SELECT
... LIMIT 1). The LIMIT 1 would have defeated optimization till recently,
but now the planner knows that it's a no-op in this context and throws it
away, and then is able to convert the sub-select to a semijoin. Usually
that results in a better plan; there's far from enough info here to
guess why it's worse for you.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-09-28 22:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-09-28 17:53 Query Plan difference between 9.3 and 9.5 for the worse. Help !!! Jorge Torralba <jorge.torralba@gmail.com>
2016-09-28 22:28 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox