explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n2OZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.154 165.670 ↓ 51.0 51 1

Sort (cost=4,306.25..4,306.26 rows=1 width=714) (actual time=165.667..165.670 rows=51 loops=1)

  • Sort Key: t4.open DESC, t4.term_score DESC, t4.merchant_short_id
  • Sort Method: quicksort Memory: 69kB
2. 0.024 165.516 ↓ 51.0 51 1

Subquery Scan on t4 (cost=4,306.02..4,306.24 rows=1 width=714) (actual time=165.315..165.516 rows=51 loops=1)

  • Filter: (t4.catalog_items_rn <= 2)
  • Rows Removed by Filter: 222
3. 0.172 165.492 ↓ 273.0 273 1

WindowAgg (cost=4,306.02..4,306.14 rows=1 width=790) (actual time=165.313..165.492 rows=273 loops=1)

4. 0.308 165.320 ↓ 273.0 273 1

Sort (cost=4,306.02..4,306.03 rows=1 width=616) (actual time=165.308..165.320 rows=273 loops=1)

  • Sort Key: t2.merchant_id, (NULLIF((ts_rank_cd(catalog_item.fts, q.tsquery))::double precision, '0'::double precision))
  • Sort Method: quicksort Memory: 314kB
5. 2.963 165.012 ↓ 273.0 273 1

Nested Loop (cost=715.85..4,306.01 rows=1 width=616) (actual time=140.467..165.012 rows=273 loops=1)

  • Join Filter: (catalog_item.fts @@@ q.tsquery)
  • Rows Removed by Join Filter: 12288
6. 0.003 0.003 ↑ 1.0 1 1

Function Scan on q (cost=0.00..0.10 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)

7. 2.108 162.046 ↓ 1,570.1 12,561 1

Nested Loop (cost=715.85..4,305.08 rows=8 width=737) (actual time=140.113..162.046 rows=12,561 loops=1)

8. 0.032 140.490 ↓ 15.9 286 1

Subquery Scan on t2 (cost=715.42..728.24 rows=18 width=445) (actual time=140.087..140.490 rows=286 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 10
9. 0.349 140.458 ↓ 5.5 296 1

WindowAgg (cost=715.42..722.71 rows=54 width=534) (actual time=140.086..140.458 rows=296 loops=1)

10. 0.697 140.109 ↓ 5.5 296 1

Sort (cost=715.42..715.55 rows=54 width=429) (actual time=140.074..140.109 rows=296 loops=1)

  • Sort Key: merc.group_id, (COALESCE((true), false)) DESC, d.distance, t.delivery_fee, (((merc.evaluations #>> '{IFOOD,avg}'::text[]))::real) DESC, merc.merchant_short_id
  • Sort Method: quicksort Memory: 225kB
11. 0.478 139.412 ↓ 5.5 296 1

Nested Loop (cost=119.49..713.86 rows=54 width=429) (actual time=132.031..139.412 rows=296 loops=1)

12. 0.372 137.454 ↓ 5.5 296 1

Nested Loop Left Join (cost=119.23..702.40 rows=54 width=601) (actual time=131.783..137.454 rows=296 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 310
13. 0.424 134.658 ↓ 5.6 606 1

Nested Loop (cost=118.79..606.79 rows=108 width=600) (actual time=131.747..134.658 rows=606 loops=1)

14. 0.101 132.086 ↓ 6.6 716 1

Subquery Scan on t (cost=118.38..130.09 rows=108 width=30) (actual time=131.731..132.086 rows=716 loops=1)

  • Filter: (t.mode <> 'EXCLUSION'::delivery_mode)
  • Rows Removed by Filter: 19
15. 0.155 131.985 ↓ 6.7 735 1

Unique (cost=118.38..118.92 rows=109 width=46) (actual time=131.730..131.985 rows=735 loops=1)

16. 0.528 131.830 ↓ 6.8 745 1

Sort (cost=118.38..118.65 rows=109 width=46) (actual time=131.729..131.830 rows=745 loops=1)

  • Sort Key: dels.owner_id, ((dels.mode <> 'EXCLUSION'::delivery_mode)), dels.zipcode COLLATE "C", dels.priority
  • Sort Method: quicksort Memory: 83kB
17. 0.648 131.302 ↓ 6.8 745 1

Nested Loop (cost=0.98..114.69 rows=109 width=46) (actual time=2.990..131.302 rows=745 loops=1)

18. 127.364 127.364 ↓ 658.0 658 1

Index Scan using polygons_geom_idx on polygons pol (cost=0.55..4.90 rows=1 width=16) (actual time=2.978..127.364 rows=658 loops=1)

  • Index Cond: (geom && '0101000020E61000008EDF2582828647C06D945055FCDA36C0'::geometry)
  • Filter: _st_intersects(geom, '0101000020E61000008EDF2582828647C06D945055FCDA36C0'::geometry)
  • Rows Removed by Filter: 225
19. 3.290 3.290 ↑ 110.0 1 658

Index Scan using deliveries_pol_id_idx on deliveries dels (cost=0.43..98.51 rows=110 width=61) (actual time=0.005..0.005 rows=1 loops=658)

  • Index Cond: (pol_id = pol.pol_id)
20. 2.148 2.148 ↑ 1.0 1 716

Index Scan using merchants_pkey on merchants merc (cost=0.42..4.41 rows=1 width=586) (actual time=0.003..0.003 rows=1 loops=716)

  • Index Cond: (merchant_id = t.owner_id)
  • Filter: supports_delivery
  • Rows Removed by Filter: 0
21. 2.424 2.424 ↓ 0.0 0 606

Index Only Scan using merchant_shifts_pkey on merchant_shifts shift (cost=0.43..0.79 rows=1 width=31) (actual time=0.004..0.004 rows=0 loops=606)

  • 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))
  • Rows Removed by Filter: 0
  • Heap Fetches: 170
22. 1.480 1.480 ↑ 1.0 1 296

Function Scan on round d (cost=0.26..0.36 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=296)

23. 19.448 19.448 ↓ 44.0 44 286

Index Scan using catalog_items_merchant_short_id_idx on catalog_items catalog_item (cost=0.43..198.61 rows=1 width=726) (actual time=0.006..0.068 rows=44 loops=286)

  • Index Cond: (merchant_short_id = t2.merchant_short_id)
  • Filter: (((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 (range_of_times))) AND (date_part('dow'::text, now()) = ANY ((COALESCE(days, '{0,1,2,3,4,5,6}'::integer[]))::double precision[])))
  • Rows Removed by Filter: 3