We are in the process of migrating from Oracle to Postgres and the following
query does much less work with Oracle vs Postgres.
explain (analyze, buffers)
select favoritegr0_.FAVORITE_GROUP_SID as favorite1_2_, favoritegr0_.CHANGED as
changed2_2_, favoritegr0_.TYPE_DISCRIMINATOR as type_dis3_2_,
favoritegr0_.GROUP_NAME as group_na4_2_, favoritegr0_.IS_DELETED as
is_delet5_2_, favoritegr0_.LAST_USED as last_use6_2_, favoritegr0_.POSITION as
position7_2_,
favoritegr0_.PRISM_GUID as prism_gu8_2_, favoritegr0_.PRODUCT_SID as
product_9_2_,
favoritegr0_.PRODUCT_VIEW as product10_2_, favoritegr0_.USAGE_TYPE as
usage_t11_2_, favoritegr0_.ROW_VERSION as row_ver12_2_
from cf0.FAVORITE_GROUP favoritegr0_
where 'FORMS.WESTLAW' = favoritegr0_.PRODUCT_SID
and favoritegr0_.PRODUCT_VIEW in ('DefaultProductView')
and (favoritegr0_.FAVORITE_GROUP_SID not in
(select favoriteen1_.FAVORITE_GROUP_SID
from cf0.FAVORITE_GROUP_MEMBER favoriteen1_
cross join cf0.CATEGORY_PAGE categorypa2_
where
favoriteen1_.CATEGORY_PAGE_SID=categorypa2_.CATEGORY_PAGE_SID
and categorypa2_.UNIQUE_NAME='Florida'
and categorypa2_.IS_DELETED=0
and favoriteen1_.IS_DELETED=0))
and favoritegr0_.IS_DELETED=0
and (favoritegr0_.USAGE_TYPE=0 or favoritegr0_.USAGE_TYPE is null)
and favoritegr0_.PRISM_GUID='ia74483420000012ca23eacf87bb0ed56'
order by favoritegr0_.POSITION desc;
Here is the plan in Postgres. It did 1426 shared block hits. If you look at
this plan it is not pushing filtering into the NOT IN subquery- it is fully
resolving that part of the query driving off where UNIQUE_NAME = 'Florida'.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=5198.22..5198.22 rows=1 width=144) (actual time=6.559..6.560 rows=1
loops=1)
Sort Key: favoritegr0_."position" DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1426
-> Index Scan using favorite_group_idx01 on favorite_group favoritegr0_
(cost=5190.18..5198.21 rows=1 width=144) (actual time=6.514..6.515 rows=1
loops=1)
Index Cond: (((prism_guid)::text =
'ia74483420000012ca23eacf87bb0ed56'::text) AND (is_deleted = 0))
Filter: ((NOT (hashed SubPlan 1)) AND ((usage_type = 0) OR (usage_type
IS NULL)) AND ('FORMS.WESTLAW'::text = (product_sid)::text) AND
((product_view)::text = 'DefaultProductView'::text))
Buffers: shared hit=1423
SubPlan 1
-> Nested Loop (cost=0.70..5189.90 rows=1 width=33) (actual
time=6.459..6.459 rows=0 loops=1)
Buffers: shared hit=1417
-> Index Scan using category_page_idx04 on category_page
categorypa2_ (cost=0.42..5131.71 rows=7 width=33) (actual time=0.035..6.138
rows=92 loops=1)
Index Cond: ((unique_name)::text = 'Florida'::text)
Filter: (is_deleted = 0)
Buffers: shared hit=1233
-> Index Scan using favorite_group_member_idx03 on
favorite_group_member favoriteen1_ (cost=0.28..8.30 rows=1 width=66) (actual
time=0.003..0.003 rows=0 loops=92)
Index Cond: ((category_page_sid)::text =
(categorypa2_.category_page_sid)::text)
Filter: (is_deleted = 0)
Buffers: shared hit=184
Planning Time: 1.624 ms
Execution Time: 6.697 ms
If I compare that to the plan Oracle uses it pushes the
favoritegr0_.FAVORITE_GROUP_SID predicate into the NOT IN. I'm able to get a
similar plan with Postgres if I change the NOT IN to a NOT EXISTS:
explain (analyze, buffers)
select favoritegr0_.FAVORITE_GROUP_SID as favorite1_2_, favoritegr0_.CHANGED as
changed2_2_, favoritegr0_.TYPE_DISCRIMINATOR as type_dis3_2_,
favoritegr0_.GROUP_NAME as group_na4_2_, favoritegr0_.IS_DELETED as
is_delet5_2_, favoritegr0_.LAST_USED as last_use6_2_, favoritegr0_.POSITION as
position7_2_,
favoritegr0_.PRISM_GUID as prism_gu8_2_, favoritegr0_.PRODUCT_SID as
product_9_2_,
favoritegr0_.PRODUCT_VIEW as product10_2_, favoritegr0_.USAGE_TYPE as
usage_t11_2_, favoritegr0_.ROW_VERSION as row_ver12_2_
from cf0.FAVORITE_GROUP favoritegr0_
where 'FORMS.WESTLAW' = favoritegr0_.PRODUCT_SID
and favoritegr0_.PRODUCT_VIEW in ('DefaultProductView')
and not exists (
select 'x'
from cf0.FAVORITE_GROUP_MEMBER favoriteen1_
cross join cf0.CATEGORY_PAGE categorypa2_
where favoriteen1_.CATEGORY_PAGE_SID=categorypa2_.CATEGORY_PAGE_SID
and categorypa2_.UNIQUE_NAME='Florida'
and categorypa2_.IS_DELETED=0
and favoriteen1_.IS_DELETED=0
and favoritegr0_.FAVORITE_GROUP_SID =
favoriteen1_.FAVORITE_GROUP_SID)
and favoritegr0_.IS_DELETED=0
and (favoritegr0_.USAGE_TYPE=0 or favoritegr0_.USAGE_TYPE is null)
and favoritegr0_.PRISM_GUID='ia74483420000012ca23eacf87bb0ed56'
order by favoritegr0_.POSITION desc;
Here you can see the query did 5 shared block hits- much better than the plan
above. It's pushing the predicate into the NOT EXISTS with a Nested Loop Anti
Join.
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=121.50..121.51 rows=1 width=144) (actual time=0.027..0.028 rows=1
loops=1)
Sort Key: favoritegr0_."position" DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=5
-> Nested Loop Anti Join (cost=5.11..121.49 rows=1 width=144) (actual
time=0.021..0.022 rows=1 loops=1)
Buffers: shared hit=5
-> Index Scan using favorite_group_idx01 on favorite_group
favoritegr0_ (cost=0.28..8.30 rows=1 width=144) (actual time=0.012..0.012
rows=1 loops=1)
Index Cond: (((prism_guid)::text =
'ia74483420000012ca23eacf87bb0ed56'::text) AND (is_deleted = 0))
Filter: (((usage_type = 0) OR (usage_type IS NULL)) AND
('FORMS.WESTLAW'::text = (product_sid)::text) AND ((product_view)::text =
'DefaultProductView'::text))
Buffers: shared hit=3
-> Nested Loop (cost=4.83..113.18 rows=1 width=33) (actual
time=0.008..0.009 rows=0 loops=1)
Buffers: shared hit=2
-> Bitmap Heap Scan on favorite_group_member favoriteen1_
(cost=4.41..56.40 rows=17 width=66) (actual time=0.007..0.008 rows=0 loops=1)
Recheck Cond: ((favoritegr0_.favorite_group_sid)::text =
(favorite_group_sid)::text)
Filter: (is_deleted = 0)
Buffers: shared hit=2
-> Bitmap Index Scan on favorite_group_member_idx02
(cost=0.00..4.41 rows=17 width=0) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((favorite_group_sid)::text =
(favoritegr0_.favorite_group_sid)::text)
Buffers: shared hit=2
-> Index Scan using category_page_pkey on category_page
categorypa2_ (cost=0.42..3.30 rows=1 width=33) (never executed)
Index Cond: ((category_page_sid)::text =
(favoriteen1_.category_page_sid)::text)
Filter: (((unique_name)::text = 'Florida'::text) AND
(is_deleted = 0))
Planning Time: 0.554 ms
Execution Time: 0.071 ms
Is Postgres able to drive the query the same way with the NOT IN as the NOT
EXISTS is doing or is that only available if the query has a NOT EXISTS? I
don't see an option to push predicate or something like that using
pg_hint_plan. I'm not sure if there are any optimizer settings that may tell
Postgres to treat the NOT IN like a NOT EXISTS when optimizing this type of
query.
Thanks in advance
Steve
This e-mail is for the sole use of the intended recipient and contains
information that may be privileged and/or confidential. If you are not an
intended recipient, please notify the sender by return e-mail and delete this
e-mail and any attachments. Certain required legal entity disclosures can be
accessed on our website:
https://www.thomsonreuters.com/en/resources/disclosures.html
