explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ex3x : DEV - idx e idx parcial

Settings
# exclusive inclusive rows x rows loops node
1. 0.529 34.744 ↑ 4.4 256 1

Sort (cost=22,835.58..22,838.42 rows=1,136 width=551) (actual time=34.638..34.744 rows=256 loops=1)

  • Sort Key: t2.distance, t2.score DESC, t2.merchant_short_id
  • Sort Method: quicksort Memory: 143kB
2. 0.301 34.215 ↑ 4.4 256 1

Subquery Scan on t2 (cost=21,942.97..22,777.93 rows=1,136 width=551) (actual time=33.179..34.215 rows=256 loops=1)

  • Filter: (t2.group_rn <= 1)
  • Rows Removed by Filter: 78
3. 0.599 33.914 ↑ 10.2 334 1

WindowAgg (cost=21,942.97..22,428.61 rows=3,408 width=500) (actual time=33.176..33.914 rows=334 loops=1)

4. 1.361 33.315 ↑ 10.2 334 1

Sort (cost=21,942.97..21,951.49 rows=3,408 width=616) (actual time=33.163..33.315 rows=334 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: 162kB
5. 0.781 31.954 ↑ 10.2 334 1

Nested Loop Left Join (cost=198.11..21,743.01 rows=3,408 width=616) (actual time=1.630..31.954 rows=334 loops=1)

6. 1.356 29.837 ↑ 10.2 334 1

Nested Loop (cost=197.83..20,206.36 rows=3,408 width=538) (actual time=1.617..29.837 rows=334 loops=1)

7. 4.692 22.775 ↑ 3.6 951 1

Nested Loop Left Join (cost=197.57..19,515.98 rows=3,408 width=506) (actual time=1.018..22.775 rows=951 loops=1)

  • Filter: COALESCE((true), false)
  • Rows Removed by Filter: 549
8. 9.757 10.583 ↑ 4.5 1,500 1

Bitmap Heap Scan on merchants merc (cost=197.14..4,807.71 rows=6,817 width=519) (actual time=0.992..10.583 rows=1,500 loops=1)

  • Recheck Cond: (channels && '{IFOOD}'::text[])
  • Filter: (available AND (COALESCE(connected, false) OR always_online))
  • Rows Removed by Filter: 13531
  • Heap Blocks: exact=1557
9. 0.826 0.826 ↓ 1.0 15,031 1

Bitmap Index Scan on idx_merchant_parcial1 (cost=0.00..195.44 rows=15,030 width=0) (actual time=0.825..0.826 rows=15,031 loops=1)

10. 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.005..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
11. 5.706 5.706 ↓ 0.0 0 951

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

  • Filter: (distance <= 15.0)
  • Rows Removed by Filter: 1
12. 1.336 1.336 ↑ 1.0 1 334

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

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