explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zR1

Settings
# exclusive inclusive rows x rows loops node
1. 0.681 345.392 ↓ 209.0 209 1

Sort (cost=13,781.40..13,781.40 rows=1 width=715) (actual time=345.384..345.392 rows=209 loops=1)

  • Sort Key: t4.open DESC, t4.term_score DESC, t4.merchant_short_id
  • Sort Method: quicksort Memory: 250kB
2. 0.039 344.711 ↓ 209.0 209 1

Subquery Scan on t4 (cost=13,781.16..13,781.39 rows=1 width=715) (actual time=344.528..344.711 rows=209 loops=1)

  • Filter: (t4.catalog_items_rn <= 100)
3. 0.143 344.672 ↓ 209.0 209 1

WindowAgg (cost=13,781.16..13,781.28 rows=1 width=791) (actual time=344.526..344.672 rows=209 loops=1)

4. 0.303 344.529 ↓ 209.0 209 1

Sort (cost=13,781.16..13,781.17 rows=1 width=617) (actual time=344.520..344.529 rows=209 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: 245kB
5. 2.000 344.226 ↓ 209.0 209 1

Nested Loop (cost=1,258.98..13,781.15 rows=1 width=617) (actual time=120.419..344.226 rows=209 loops=1)

  • Join Filter: (catalog_item.fts @@@ q.tsquery)
  • Rows Removed by Join Filter: 6548
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. 1.659 342.223 ↓ 965.3 6,757 1

Nested Loop (cost=1,258.98..13,780.33 rows=7 width=738) (actual time=120.297..342.223 rows=6,757 loops=1)

8. 0.041 119.082 ↓ 8.1 146 1

Subquery Scan on t2 (cost=720.00..732.82 rows=18 width=446) (actual time=118.525..119.082 rows=146 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 5
9. 0.468 119.041 ↓ 2.8 151 1

WindowAgg (cost=720.00..727.29 rows=54 width=535) (actual time=118.524..119.041 rows=151 loops=1)

10. 0.357 118.573 ↓ 2.8 151 1

Sort (cost=720.00..720.13 rows=54 width=430) (actual time=118.517..118.573 rows=151 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: 126kB
11. 0.202 118.216 ↓ 2.8 151 1

Nested Loop (cost=119.49..718.44 rows=54 width=430) (actual time=111.968..118.216 rows=151 loops=1)

12. 0.220 117.108 ↓ 2.8 151 1

Nested Loop Left Join (cost=119.23..706.98 rows=54 width=604) (actual time=111.747..117.108 rows=151 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 456
13. 0.291 114.460 ↓ 5.6 607 1

Nested Loop (cost=118.79..606.79 rows=108 width=603) (actual time=111.686..114.460 rows=607 loops=1)

14. 0.100 112.021 ↓ 6.6 716 1

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

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

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

16. 0.392 111.774 ↓ 6.8 745 1

Sort (cost=118.38..118.65 rows=109 width=46) (actual time=111.673..111.774 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.215 111.382 ↓ 6.8 745 1

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

18. 108.535 108.535 ↓ 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.936..108.535 rows=658 loops=1)

  • Index Cond: (geom && '0101000020E61000008EDF2582828647C06D945055FCDA36C0'::geometry)
  • Filter: _st_intersects(geom, '0101000020E61000008EDF2582828647C06D945055FCDA36C0'::geometry)
  • Rows Removed by Filter: 225
19. 2.632 2.632 ↑ 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.004..0.004 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=589) (actual time=0.003..0.003 rows=1 loops=716)

  • Index Cond: (merchant_id = t.owner_id)
21. 2.428 2.428 ↓ 0.0 0 607

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

  • 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: 1
  • Heap Fetches: 287
22. 0.906 0.906 ↑ 1.0 1 151

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

23. 11.388 221.482 ↓ 46.0 46 146

Bitmap Heap Scan on catalog_items catalog_item (cost=538.98..724.76 rows=1 width=724) (actual time=1.447..1.517 rows=46 loops=146)

  • Recheck 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: 2
  • Heap Blocks: exact=4574
24. 210.094 210.094 ↑ 1.7 50 146

Bitmap Index Scan on catalog_items_merchant_short_id_fts_idx (cost=0.00..538.98 rows=86 width=0) (actual time=1.439..1.439 rows=50 loops=146)

  • Index Cond: (merchant_short_id = t2.merchant_short_id)