explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1dwD

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=98.92..98.93 rows=1 width=8) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Limit (cost=98.83..98.90 rows=1 width=4) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Subquery Scan on tu4 (cost=98.83..98.90 rows=1 width=4) (actual rows= loops=)

  • Filter: ((tu4.catalog_items_rn <= 100) AND (tu4.catalog_items_campaign_count = 1))
4. 0.000 0.000 ↓ 0.0

WindowAgg (cost=98.83..98.87 rows=2 width=430) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=98.83..98.84 rows=2 width=44) (actual rows= loops=)

  • Sort Key: catalog_item.catalog_item_id
6. 0.000 0.000 ↓ 0.0

WindowAgg (cost=98.78..98.82 rows=2 width=44) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=98.78..98.79 rows=2 width=36) (actual rows= loops=)

  • Sort Key: t2.merchant_id
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=62.39..98.77 rows=2 width=36) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=62.32..98.57 rows=2 width=75) (actual rows= loops=)

  • Join Filter: (campaign.tag = ANY (catalog_item.tags))
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=62.32..76.90 rows=2 width=150) (actual rows= loops=)

  • Join Filter: ((t2.merchant_id = catalog_item.merchant_id) AND (t2.catalog_version = catalog_item.catalog_version) AND ((catalog_item.range_of_times IS NULL) OR ((timezone((t2.timezone)::text, to_timestamp((trunc((date_part('epoch'::text, now()) / '900'::double precision)) * '900'::double precision))))::time without time zone = ANY (catalog_item.range_of_times))))
11. 0.000 0.000 ↓ 0.0

Subquery Scan on t2 (cost=62.32..62.39 rows=1 width=30) (actual rows= loops=)

  • Filter: (t2.group_rn <= 1)
12. 0.000 0.000 ↓ 0.0

WindowAgg (cost=62.32..62.38 rows=1 width=251) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=62.32..62.33 rows=1 width=111) (actual rows= loops=)

  • Sort Key: merc.group_id, ((merc.available AND (COALESCE(merc.connected, false) OR merc.always_online) AND COALESCE((true), false) AND ((merc.radius_restriction IS NULL) OR ((d.distance)::double precision < merc.radius_restriction)))) DESC, merc.chain_priority DESC, d.distance, (CASE WHEN ((campaign_merc.value IS NOT NULL) AND (campaign_merc.code = 'FIXED_DELIVERY_FEE'::text)) THEN campaign_merc.value WHEN ((campaign_merc.value IS NOT NULL) AND (campaign_merc.code = 'DELIVERY_FEE_THRESHOLD'::text) AND (campaign_merc.value >= COALESCE(deliveries_range.normal_fee, t.delivery_fee))) THEN 0.0 WHEN ((campaign_merc.value IS NOT NULL) AND (campaign_merc.code <> ALL ('{FIXED_DELIVERY_FEE,DELIVERY_FEE_THRESHOLD}'::text[]))) THEN 0.0 ELSE COALESCE(deliveries_range.normal_fee, t.delivery_fee) END), (((merc.evaluations #>> '{IFOOD,avg}'::text[]))::real) DESC, merc.merchant_short_id
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=32.10..62.31 rows=1 width=111) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=31.94..54.08 rows=1 width=260) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=22.41..44.52 rows=1 width=251) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=22.40..44.50 rows=1 width=219) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=22.15..44.22 rows=1 width=243) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=16.60..27.88 rows=1 width=256) (actual rows= loops=)

  • Join Filter: (t.owner_id = merc.merchant_id)
20. 0.000 0.000 ↓ 0.0

Subquery Scan on t (cost=16.60..16.62 rows=1 width=36) (actual rows= loops=)

  • Filter: (t.mode <> 'EXCLUSION'::delivery_mode)
21. 0.000 0.000 ↓ 0.0

Unique (cost=16.60..16.61 rows=1 width=84) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=16.60..16.60 rows=1 width=84) (actual rows= loops=)

  • Sort Key: dels.owner_id, ((dels.mode <> 'EXCLUSION'::delivery_mode)), dels.zipcode COLLATE "C", dels.priority
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..16.59 rows=1 width=84) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Scan using polygons_geom_idx on polygons pol (cost=0.14..8.41 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (geom && '0101000020E61000000000001C738647C09920F1AAD3DA36C0'::geometry)
  • Filter: _st_intersects(geom, '0101000020E61000000000001C738647C09920F1AAD3DA36C0'::geometry)
25. 0.000 0.000 ↓ 0.0

Index Scan using deliveries_pol_id_idx on deliveries dels (cost=0.15..8.17 rows=1 width=67) (actual rows= loops=)

  • Index Cond: (pol_id = pol.pol_id)
26. 0.000 0.000 ↓ 0.0

Seq Scan on merchants merc (cost=0.00..11.25 rows=1 width=236) (actual rows= loops=)

  • Filter: (supports_delivery AND (channels && '{IFOOD}'::text[]))
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on campaign campaign_merc (cost=5.55..16.33 rows=1 width=82) (actual rows= loops=)

  • Recheck Cond: (tag = ANY (merc.tags))
  • Filter: (enabled AND (type = 'MERCHANT_TAG'::text) AND (now() >= start_date) AND (now() <= end_date))
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on campaign_pkey (cost=0.00..5.55 rows=10 width=0) (actual rows= loops=)

  • Index Cond: (tag = ANY (merc.tags))
29. 0.000 0.000 ↓ 0.0

Function Scan on round rd (cost=0.26..0.27 rows=1 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Function Scan on round d (cost=0.01..0.02 rows=1 width=32) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Limit (cost=9.53..9.53 rows=1 width=132) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=9.53..9.53 rows=1 width=132) (actual rows= loops=)

  • Sort Key: deliveries_range.distance
33. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on deliveries_range (cost=4.16..9.52 rows=1 width=132) (actual rows= loops=)

  • Recheck Cond: (merchant_id = merc.merchant_id)
  • Filter: ((delivered_by = t.delivered_by) AND (rd.raw_distance <= (distance)::double precision))
34. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on deliveries_range_merchant_id_distance_idx (cost=0.00..4.16 rows=2 width=0) (actual rows= loops=)

  • Index Cond: (merchant_id = merc.merchant_id)
35. 0.000 0.000 ↓ 0.0

Index Only Scan using merchant_shifts_merchant_short_id_dow_shift_range_idx on merchant_shifts shift (cost=0.16..8.20 rows=1 width=31) (actual rows= loops=)

  • Index Cond: ((merchant_short_id = merc.merchant_short_id) AND (dow = (date_part('dow'::text, timezone((merc.timezone)::text, now())))::integer))
  • Filter: (shift_range @> timerange((timezone((merc.timezone)::text, now()))::time without time zone, (timezone((merc.timezone)::text, now()))::time without time zone, '[]'::text))
36. 0.000 0.000 ↓ 0.0

Append (cost=0.00..14.41 rows=2 width=156) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on catalog_items catalog_item (cost=0.00..1.79 rows=1 width=185) (actual rows= loops=)

  • Filter: (date_part('dow'::text, now()) = ANY ((COALESCE(days, '{0,1,2,3,4,5,6}'::integer[]))::double precision[]))
38. 0.000 0.000 ↓ 0.0

Seq Scan on catalog_items_v2 catalog_item_1 (cost=0.00..12.62 rows=1 width=128) (actual rows= loops=)

  • Filter: (date_part('dow'::text, now()) = ANY ((COALESCE(days, '{0,1,2,3,4,5,6}'::integer[]))::double precision[]))
39. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..20.34 rows=28 width=32) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on campaign (cost=0.00..20.20 rows=28 width=32) (actual rows= loops=)

  • Filter: (enabled AND (now() >= start_date) AND (now() <= end_date))
41. 0.000 0.000 ↓ 0.0

Result (cost=0.07..0.08 rows=1 width=0) (actual rows= loops=)

  • One-Time Filter: (t2.supports_order_scheduling AND ((catalog_item.range_of_times IS NULL) OR (catalog_item.range_of_times @> '{12:00:00,12:15:00,12:30:00,12:45:00,13:00:00,13:15:00,13:30:00,13:45:00}'::time without time zone[])) AND (((timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) >= '2019-05-11 18:00:00-03'::timestamp with time zone) AND (timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) <= '2019-05-12 14:00:00-03'::timestamp with time zone)) OR ((timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) >= '2019-05-05 18:00:00-03'::timestamp with time zone) AND (timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) <= '2019-05-06 14:00:00-03'::timestamp with time zone)) OR ((timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) >= '2019-05-06 18:00:00-03'::timestamp with time zone) AND (timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) <= '2019-05-07 14:00:00-03'::timestamp with time zone)) OR ((timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) >= '2019-05-07 18:00:00-03'::timestamp with time zone) AND (timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) <= '2019-05-08 14:00:00-03'::timestamp with time zone)) OR ((timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) >= '2019-05-08 18:00:00-03'::timestamp with time zone) AND (timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) <= '2019-05-09 14:00:00-03'::timestamp with time zone)) OR ((timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) >= '2019-05-09 18:00:00-03'::timestamp with time zone) AND (timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) <= '2019-05-10 14:00:00-03'::timestamp with time zone)) OR ((timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) >= '2019-05-10 18:00:00-03'::timestamp with time zone) AND (timezone((t2.timezone)::text, '2019-05-05 22:00:00-03'::timestamp with time zone) <= '2019-05-11 14:00:00-03'::timestamp with time zone))))