explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iH74 : PROD -> idx e idx parcial

Settings
# exclusive inclusive rows x rows loops node
1. 14.407 365.762 ↑ 2.2 5,871 1

Sort (cost=288,899.01..288,930.93 rows=12,769 width=879) (actual time=365.490..365.762 rows=5,871 loops=1)

  • Sort Key: t2.distance, t2.score DESC, t2.merchant_short_id
  • Sort Method: quicksort Memory: 6021kB
2. 1.197 351.355 ↑ 2.2 5,871 1

Subquery Scan on t2 (cost=278,642.68..288,028.14 rows=12,769 width=879) (actual time=343.696..351.355 rows=5,871 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 998
3. 5.773 350.158 ↑ 5.6 6,869 1

WindowAgg (cost=278,642.68..284,101.57 rows=38,308 width=828) (actual time=343.694..350.158 rows=6,869 loops=1)

4. 26.083 344.385 ↑ 5.6 6,869 1

Sort (cost=278,642.68..278,738.45 rows=38,308 width=869) (actual time=343.682..344.385 rows=6,869 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: 8383kB
5. 15.047 318.302 ↑ 5.6 6,869 1

Nested Loop Left Join (cost=7,143.80..275,726.42 rows=38,308 width=869) (actual time=37.163..318.302 rows=6,869 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 1493
6. 0.000 244.721 ↑ 9.2 8,362 1

Gather (cost=7,143.37..180,491.26 rows=76,617 width=877) (actual time=37.136..244.721 rows=8,362 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1.471 278.327 ↑ 11.5 2,787 3

Hash Left Join (cost=6,143.37..171,829.56 rows=31,924 width=877) (actual time=21.578..278.327 rows=2,787 loops=3)

  • Hash Cond: ((merc.group_id)::text = (chain.external_id)::text)
8. 85.276 269.159 ↑ 11.5 2,787 3

Nested Loop (cost=2,291.66..167,894.06 rows=31,924 width=767) (actual time=13.752..269.159 rows=2,787 loops=3)

9. 163.849 183.879 ↑ 1.5 21,665 3

Parallel Bitmap Heap Scan on merchants merc (cost=2,291.40..161,429.19 rows=31,924 width=735) (actual time=12.918..183.879 rows=21,665 loops=3)

  • Recheck Cond: (channels && '{IFOOD}'::text[])
  • Filter: (available AND (COALESCE(connected, false) OR always_online))
  • Rows Removed by Filter: 37833
  • Heap Blocks: exact=15925
10. 20.030 20.030 ↓ 1.0 178,501 1

Bitmap Index Scan on search_platform_merchants_channels (cost=0.00..2,272.25 rows=178,366 width=0) (actual time=20.030..20.030 rows=178,501 loops=1)

11. 0.004 0.004 ↓ 0.0 0 64,994

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

  • Filter: (distance <= 15.0)
  • Rows Removed by Filter: 1
12. 3.742 7.697 ↓ 1.0 17,479 3

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1696kB
13. 3.955 3.955 ↓ 1.0 17,479 3

Seq Scan on merchant_chain chain (cost=0.00..2,068.10 rows=17,401 width=115) (actual time=0.014..3.955 rows=17,479 loops=3)

14. 58.534 58.534 ↑ 1.0 1 8,362

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

  • 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: 7248
Planning time : 1.146 ms
Execution time : 368.781 ms