explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cNqI

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 25,275.126 ↑ 1.0 722 1

Limit (cost=138,770.25..138,772.05 rows=722 width=287) (actual time=25,275.017..25,275.126 rows=722 loops=1)

  • Buffers: shared hit=20,954,778 read=286,097 dirtied=537
2. 597.923 25,275.074 ↑ 11.4 722 1

Sort (cost=138,770.25..138,790.75 rows=8,199 width=287) (actual time=25,275.016..25,275.074 rows=722 loops=1)

  • Sort Key: store.name
  • Sort Method: top-N heapsort Memory: 614kB
  • Buffers: shared hit=20,954,778 read=286,097 dirtied=537
3. 470.236 24,677.151 ↓ 126.1 1,033,759 1

Nested Loop Left Join (cost=2.83..138,339.97 rows=8,199 width=287) (actual time=1.778..24,677.151 rows=1,033,759 loops=1)

  • Buffers: shared hit=20,954,778 read=286,097 dirtied=537
4. 40.359 22,139.397 ↓ 126.1 1,033,759 1

Nested Loop Left Join (cost=2.41..133,650.06 rows=8,199 width=230) (actual time=1.730..22,139.397 rows=1,033,759 loops=1)

  • Buffers: shared hit=17,349,140 read=267,653 dirtied=403
5. 383.739 15,896.484 ↓ 126.1 1,033,759 1

Nested Loop Left Join (cost=1.98..126,357.12 rows=8,199 width=235) (actual time=1.687..15,896.484 rows=1,033,759 loops=1)

  • Buffers: shared hit=13,274,084 read=219,394 dirtied=268
6. 1,100.910 10,351.030 ↓ 125.9 1,032,343 1

Nested Loop (cost=1.55..120,252.10 rows=8,199 width=208) (actual time=1.163..10,351.030 rows=1,032,343 loops=1)

  • Buffers: shared hit=9,162,210 read=163,244 dirtied=7
7. 1,244.743 7,185.434 ↓ 125.7 1,032,343 1

Nested Loop (cost=1.12..114,854.54 rows=8,211 width=141) (actual time=1.128..7,185.434 rows=1,032,343 loops=1)

  • Buffers: shared hit=5,089,350 read=103,127 dirtied=7
8. 131.164 3,554.221 ↓ 125.8 1,193,235 1

Nested Loop (cost=0.70..109,188.35 rows=9,484 width=35) (actual time=0.545..3,554.221 rows=1,193,235 loops=1)

  • Buffers: shared hit=550,499 read=22,660 dirtied=2
9. 0.141 0.141 ↑ 1.0 68 1

Index Scan using group_id_idx on "group" gp (cost=0.14..141.28 rows=68 width=35) (actual time=0.022..0.141 rows=68 loops=1)

  • Buffers: shared hit=29 read=5
10. 3,422.916 3,422.916 ↓ 3.4 17,548 68

Index Only Scan using rel_group_store_pkey on rel_group_store rel (cost=0.56..1,085.13 rows=5,185 width=32) (actual time=0.421..50.337 rows=17,548 loops=68)

  • Index Cond: (group_id = gp.id)
  • Heap Fetches: 555,008
  • Buffers: shared hit=550,470 read=22,655 dirtied=2
11. 2,386.470 2,386.470 ↑ 1.0 1 1,193,235

Index Scan using merchant_stores_id_idx1 on merchant_stores store (cost=0.42..0.60 rows=1 width=106) (actual time=0.002..0.002 rows=1 loops=1,193,235)

  • Index Cond: (id = rel.store_id)
  • Buffers: shared hit=4,538,851 read=80,467 dirtied=5
12. 2,064.686 2,064.686 ↑ 1.0 1 1,032,343

Index Scan using merchant_address_pkey on merchant_addresses address (cost=0.43..0.66 rows=1 width=99) (actual time=0.002..0.002 rows=1 loops=1,032,343)

  • Index Cond: (id = store.id)
  • Buffers: shared hit=4,072,860 read=60,117
13. 5,161.715 5,161.715 ↑ 1.0 1 1,032,343

Index Scan using merchant_contacts_merchant_id_idx on merchant_contacts ct (cost=0.43..0.64 rows=1 width=43) (actual time=0.005..0.005 rows=1 loops=1,032,343)

  • Index Cond: (merchant_id = store.id)
  • Filter: (removed IS FALSE)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4,111,874 read=56,150 dirtied=261
14. 6,202.554 6,202.554 ↑ 1.0 1 1,033,759

Index Scan using merchant_phones_contact_id_index on merchant_phones phone (cost=0.43..0.79 rows=1 width=27) (actual time=0.006..0.006 rows=1 loops=1,033,759)

  • Index Cond: (ct.id = contact_id)
  • Buffers: shared hit=4,075,056 read=48,259 dirtied=135
15. 2,067.518 2,067.518 ↓ 0.0 0 1,033,759

Index Scan using resources_merchant_id_index on merchant_resources resource (cost=0.42..0.57 rows=1 width=73) (actual time=0.002..0.002 rows=0 loops=1,033,759)

  • Index Cond: (merchant_id = store.id)
  • Filter: (type = 2)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3,605,638 read=18,444 dirtied=134
Planning time : 5.506 ms
Execution time : 25,275.295 ms