explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jJ5H : PROD - idx "supports_takeout,supports_delivery,supports_dark_kitchen"

Settings
# exclusive inclusive rows x rows loops node
1. 14.545 386.647 ↑ 2.2 5,882 1

Sort (cost=339,712.35..339,744.28 rows=12,774 width=879) (actual time=386.380..386.647 rows=5,882 loops=1)

  • Sort Key: t2.distance, t2.score DESC, t2.merchant_short_id
  • Sort Method: quicksort Memory: 6026kB
2. 1.177 372.102 ↑ 2.2 5,882 1

Subquery Scan on t2 (cost=329,452.21..338,841.10 rows=12,774 width=879) (actual time=364.481..372.102 rows=5,882 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 997
3. 5.754 370.925 ↑ 5.6 6,879 1

WindowAgg (cost=329,452.21..334,913.10 rows=38,322 width=828) (actual time=364.479..370.925 rows=6,879 loops=1)

4. 20.648 365.171 ↑ 5.6 6,879 1

Sort (cost=329,452.21..329,548.02 rows=38,322 width=869) (actual time=364.468..365.171 rows=6,879 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: 8388kB
5. 11.712 344.523 ↑ 5.6 6,879 1

Nested Loop Left Join (cost=4,852.40..326,534.78 rows=38,322 width=869) (actual time=7.419..344.523 rows=6,879 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 1492
6. 0.000 290.956 ↑ 9.2 8,371 1

Gather (cost=4,851.96..231,282.83 rows=76,645 width=877) (actual time=7.313..290.956 rows=8,371 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1.220 309.649 ↑ 11.4 2,790 3

Hash Left Join (cost=3,851.96..222,618.33 rows=31,935 width=877) (actual time=8.905..309.649 rows=2,790 loops=3)

  • Hash Cond: ((merc.group_id)::text = (chain.external_id)::text)
8. 68.489 300.791 ↑ 11.4 2,790 3

Nested Loop (cost=0.26..218,682.79 rows=31,935 width=767) (actual time=1.131..300.791 rows=2,790 loops=3)

9. 232.299 232.299 ↑ 1.5 21,651 3

Parallel Seq Scan on merchants merc (cost=0.00..212,215.70 rows=31,935 width=735) (actual time=0.224..232.299 rows=21,651 loops=3)

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

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

  • Filter: (distance <= 15.0)
  • Rows Removed by Filter: 1
11. 3.679 7.638 ↓ 1.0 17,476 3

Hash (cost=2,068.10..2,068.10 rows=17,401 width=115) (actual time=7.638..7.638 rows=17,476 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1696kB
12. 3.959 3.959 ↓ 1.0 17,476 3

Seq Scan on merchant_chain chain (cost=0.00..2,068.10 rows=17,401 width=115) (actual time=0.012..3.959 rows=17,476 loops=3)

13. 41.855 41.855 ↑ 1.0 1 8,371

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

  • 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: 7199
Planning time : 0.788 ms
Execution time : 388.642 ms