Postgres NOT IN vs NOT EXISTS optimization - Mailing list pgsql-general
| From | Dirschel, Steve |
|---|---|
| Subject | Postgres NOT IN vs NOT EXISTS optimization |
| Date | |
| Msg-id | DM6PR03MB43322310B08F8F3D531315FCFAAA9@DM6PR03MB4332.namprd03.prod.outlook.com Whole thread Raw |
| Responses |
Re: Postgres NOT IN vs NOT EXISTS optimization
Re: Postgres NOT IN vs NOT EXISTS optimization |
| List | pgsql-general |
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
pgsql-general by date: