explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F1hy

Settings
# exclusive inclusive rows x rows loops node
1. 13.719 432.439 ↑ 3.7 5,680 1

Sort (cost=414,555.63..414,608.62 rows=21,193 width=879) (actual time=432.171..432.439 rows=5,680 loops=1)

  • Sort Key: t2.distance, t2.score DESC, t2.merchant_short_id
  • Sort Method: quicksort Memory: 5827kB
2. 1.106 418.720 ↑ 3.7 5,680 1

Subquery Scan on t2 (cost=397,456.17..413,032.78 rows=21,193 width=879) (actual time=411.558..418.720 rows=5,680 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 977
3. 5.417 417.614 ↑ 9.6 6,657 1

WindowAgg (cost=397,456.17..406,516.03 rows=63,578 width=828) (actual time=411.556..417.614 rows=6,657 loops=1)

4. 23.782 412.197 ↑ 9.6 6,657 1

Sort (cost=397,456.17..397,615.11 rows=63,578 width=869) (actual time=411.540..412.197 rows=6,657 loops=1)

  • Sort Key: merc.group_id, ((merc.available AND (COALESCE(merc.connected, false) OR merc.always_online) AND COALESCE((true), false))) DESC, merc.chain_priority DESC, d.distance, (((merc
  • Sort Method: quicksort Memory: 8137kB
5. 12.900 388.415 ↑ 9.6 6,657 1

Nested Loop Left Join (cost=4,852.40..374,998.84 rows=63,578 width=869) (actual time=7.363..388.415 rows=6,657 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 1492
6. 0.000 326.621 ↑ 15.6 8,149 1

Gather (cost=4,851.96..245,854.04 rows=127,155 width=877) (actual time=7.330..326.621 rows=8,149 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1.303 353.240 ↑ 19.5 2,716 3

Hash Left Join (cost=3,851.96..232,138.54 rows=52,981 width=877) (actual time=9.028..353.240 rows=2,716 loops=3)

  • Hash Cond: ((merc.group_id)::text = (chain.external_id)::text)
8. 72.541 344.069 ↑ 19.5 2,716 3

Nested Loop (cost=0.26..228,147.77 rows=52,981 width=767) (actual time=1.027..344.069 rows=2,716 loops=3)

9. 271.525 271.525 ↑ 2.5 21,064 3

Parallel Seq Scan on merchants merc (cost=0.00..217,418.85 rows=52,981 width=735) (actual time=0.219..271.525 rows=21,064 loops=3)

  • Filter: (available AND (COALESCE(connected, false) OR always_online) AND (channels && '{IFOOD}'::text[]))
  • Rows Removed by Filter: 40950
10. 0.003 0.003 ↓ 0.0 0 63,193

Function Scan on round d (cost=0.26..0.36 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=63,193)

  • Filter: (distance <= 15.0)
  • Rows Removed by Filter: 1
11. 3.728 7.868 ↓ 1.0 17,470 3

Hash (cost=2,068.10..2,068.10 rows=17,401 width=115) (actual time=7.867..7.868 rows=17,470 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1695kB
12. 4.140 4.140 ↓ 1.0 17,470 3

Seq Scan on merchant_chain chain (cost=0.00..2,068.10 rows=17,401 width=115) (actual time=0.013..4.140 rows=17,470 loops=3)

13. 48.894 48.894 ↑ 1.0 1 8,149

Index Only Scan using merchant_shifts_pkey on merchant_shifts shift (cost=0.44..0.91 rows=1 width=31) (actual time=0.005..0.006 rows=1 loops=8,149)

  • 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, '[]':
  • Rows Removed by Filter: 0
  • Heap Fetches: 6253
Planning time : 0.771 ms
Execution time : 434.400 ms