explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Fqf

Settings
# exclusive inclusive rows x rows loops node
1. 0.525 32.954 ↑ 4.5 253 1

Sort (cost=23,018.41..23,021.25 rows=1,136 width=551) (actual time=32.847..32.954 rows=253 loops=1)

  • Sort Key: t2.distance, t2.score DESC, t2.merchant_short_id
  • Sort Method: quicksort Memory: 142kB
2. 0.294 32.429 ↑ 4.5 253 1

Subquery Scan on t2 (cost=22,125.80..22,960.76 rows=1,136 width=551) (actual time=31.398..32.429 rows=253 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 77
3. 0.595 32.135 ↑ 10.3 330 1

WindowAgg (cost=22,125.80..22,611.44 rows=3,408 width=500) (actual time=31.396..32.135 rows=330 loops=1)

4. 1.181 31.540 ↑ 10.3 330 1

Sort (cost=22,125.80..22,134.32 rows=3,408 width=616) (actual time=31.384..31.540 rows=330 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: 161kB
5. 0.697 30.359 ↑ 10.3 330 1

Nested Loop Left Join (cost=0.97..21,925.84 rows=3,408 width=616) (actual time=1.561..30.359 rows=330 loops=1)

6. 1.676 28.342 ↑ 10.3 330 1

Nested Loop (cost=0.69..20,389.19 rows=3,408 width=538) (actual time=1.549..28.342 rows=330 loops=1)

7. 3.968 22.261 ↑ 3.9 881 1

Nested Loop Left Join (cost=0.43..19,698.81 rows=3,408 width=506) (actual time=0.038..22.261 rows=881 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 619
8. 10.793 10.793 ↑ 4.5 1,500 1

Seq Scan on merchants merc (cost=0.00..4,990.54 rows=6,817 width=519) (actual time=0.011..10.793 rows=1,500 loops=1)

  • Filter: (available AND (COALESCE(connected, false) OR always_online) AND (channels && '{IFOOD}'::text[]))
  • Rows Removed by Filter: 17237
9. 7.500 7.500 ↑ 1.0 1 1,500

Index Only Scan using merchant_shifts_pkey on merchant_shifts shift (cost=0.43..2.06 rows=1 width=31) (actual time=0.004..0.005 rows=1 loops=1,500)

  • 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
  • Rows Removed by Filter: 1
  • Heap Fetches: 67
10. 4.405 4.405 ↓ 0.0 0 881

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

  • Filter: (distance <= 15.0)
  • Rows Removed by Filter: 1
11. 1.320 1.320 ↑ 1.0 1 330

Index Scan using chain_external_id_key on merchant_chain chain (cost=0.28..0.44 rows=1 width=82) (actual time=0.004..0.004 rows=1 loops=330)

  • Index Cond: ((merc.group_id)::text = (external_id)::text)
Planning time : 0.985 ms
Execution time : 33.295 ms