explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YPbx

Settings
# exclusive inclusive rows x rows loops node
1. 2.529 155.219 ↓ 821.0 821 1

Sort (cost=4,105.95..4,105.96 rows=1 width=714) (actual time=155.187..155.219 rows=821 loops=1)

  • Sort Key: t4.open DESC, t4.term_score DESC, t4.merchant_short_id
  • Sort Method: quicksort Memory: 834kB
2. 0.142 152.690 ↓ 821.0 821 1

Subquery Scan on t4 (cost=4,105.72..4,105.94 rows=1 width=714) (actual time=152.002..152.690 rows=821 loops=1)

  • Filter: (t4.catalog_items_rn <= 100)
  • Rows Removed by Filter: 36
3. 0.506 152.548 ↓ 857.0 857 1

WindowAgg (cost=4,105.72..4,105.84 rows=1 width=758) (actual time=152.001..152.548 rows=857 loops=1)

4. 0.763 152.042 ↓ 857.0 857 1

Sort (cost=4,105.72..4,105.73 rows=1 width=616) (actual time=151.996..152.042 rows=857 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: 851kB
5. 4.046 151.279 ↓ 857.0 857 1

Nested Loop (cost=720.59..4,105.71 rows=1 width=616) (actual time=124.354..151.279 rows=857 loops=1)

  • Join Filter: (catalog_item.fts @@@ q.tsquery)
  • Rows Removed by Join Filter: 14045
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.645 147.230 ↓ 2,128.9 14,902 1

Nested Loop (cost=720.59..4,104.89 rows=7 width=737) (actual time=123.614..147.230 rows=14,902 loops=1)

8. 0.031 123.999 ↓ 15.7 282 1

Subquery Scan on t2 (cost=720.16..732.98 rows=18 width=445) (actual time=123.596..123.999 rows=282 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 6
9. 0.340 123.968 ↓ 5.3 288 1

WindowAgg (cost=720.16..727.45 rows=54 width=502) (actual time=123.596..123.968 rows=288 loops=1)

10. 0.601 123.628 ↓ 5.3 288 1

Sort (cost=720.16..720.29 rows=54 width=429) (actual time=123.587..123.628 rows=288 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.288 123.027 ↓ 5.3 288 1

Nested Loop (cost=119.49..718.60 rows=54 width=429) (actual time=115.142..123.027 rows=288 loops=1)

12. 0.631 121.011 ↓ 5.3 288 1

Nested Loop Left Join (cost=119.23..707.14 rows=54 width=602) (actual time=114.907..121.011 rows=288 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 320
13. 0.579 117.948 ↓ 5.6 608 1

Nested Loop (cost=118.79..606.79 rows=108 width=601) (actual time=114.864..117.948 rows=608 loops=1)

14. 0.110 115.218 ↓ 6.6 717 1

Subquery Scan on t (cost=118.38..130.09 rows=108 width=30) (actual time=114.847..115.218 rows=717 loops=1)

  • Filter: (t.mode <> 'EXCLUSION'::delivery_mode)
  • Rows Removed by Filter: 19
15. 0.161 115.108 ↓ 6.8 736 1

Unique (cost=118.38..118.92 rows=109 width=46) (actual time=114.845..115.108 rows=736 loops=1)

16. 0.429 114.947 ↓ 6.8 746 1

Sort (cost=118.38..118.65 rows=109 width=46) (actual time=114.844..114.947 rows=746 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.416 114.518 ↓ 6.8 746 1

Nested Loop (cost=0.98..114.69 rows=109 width=46) (actual time=0.645..114.518 rows=746 loops=1)

18. 111.466 111.466 ↓ 659.0 659 1

Index Scan using polygons_geom_idx on polygons pol (cost=0.55..4.90 rows=1 width=16) (actual time=0.636..111.466 rows=659 loops=1)

  • Index Cond: (geom && '0101000020E61000008EDF2582828647C06D945055FCDA36C0'::geometry)
  • Filter: _st_intersects(geom, '0101000020E61000008EDF2582828647C06D945055FCDA36C0'::geometry)
  • Rows Removed by Filter: 226
19. 2.636 2.636 ↑ 110.0 1 659

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=659)

  • Index Cond: (pol_id = pol.pol_id)
20. 2.151 2.151 ↑ 1.0 1 717

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

  • Index Cond: (merchant_id = t.owner_id)
21. 2.432 2.432 ↓ 0.0 0 608

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=608)

  • 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: 330
22. 1.728 1.728 ↑ 1.0 1 288

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

23. 20.586 20.586 ↓ 53.0 53 282

Index Scan using catalog_items_merchant_short_id_idx on catalog_items catalog_item (cost=0.43..187.23 rows=1 width=724) (actual time=0.006..0.073 rows=53 loops=282)

  • 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: 2