explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ql3V

Settings
# exclusive inclusive rows x rows loops node
1. 398.074 16,940.034 ↓ 1.4 1,360,903 1

Unique (cost=1,022,349.92..1,042,188.64 rows=991,936 width=73) (actual time=15,970.291..16,940.034 rows=1,360,903 loops=1)

  • Buffers: shared hit=7,643,708 read=80,835, temp read=90,377 written=91,666
2. 2,792.222 16,541.960 ↓ 1.6 1,559,307 1

Sort (cost=1,022,349.92..1,024,829.76 rows=991,936 width=73) (actual time=15,970.289..16,541.96 rows=1,559,307 loops=1)

  • Sort Key: h.id, a.formatted_address, a.street_name, a.street_type, a.suburb, a.street_no, a.unit_no
  • Sort Method: external merge Disk: 126,624kB
  • Buffers: shared hit=7,643,708 read=80,835, temp read=90,377 written=91,666
3. 454.591 13,749.738 ↓ 1.6 1,559,307 1

Gather (cost=661,533.08..835,402.65 rows=991,936 width=73) (actual time=12,786.765..13,749.738 rows=1,559,307 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=7,643,708 read=80,835, temp read=57,800 written=59,024
4. 1,035.834 13,295.147 ↓ 1.3 519,769 3 / 3

Hash Join (cost=660,533.08..735,209.05 rows=413,307 width=73) (actual time=12,662.995..13,295.147 rows=519,769 loops=3)

  • Buffers: shared hit=7,643,708 read=80,835, temp read=57,800 written=59,024
5. 343.233 343.233 ↑ 1.7 382,690 3 / 3

Seq Scan on address a (cost=0..51,573.53 rows=664,353 width=74) (actual time=0.028..343.233 rows=382,690 loops=3)

  • Buffers: shared read=44,930
6. 496.714 11,916.080 ↓ 1.3 519,769 3 / 3

Hash (cost=653,347.74..653,347.74 rows=413,307 width=15) (actual time=11,916.08..11,916.08 rows=519,769 loops=3)

  • Buffers: shared hit=7,643,648 read=35,899, temp read=37,783 written=45,576
7. 1,396.160 11,419.366 ↓ 1.3 519,769 3 / 3

Hash Join (cost=592,846.92..653,347.74 rows=413,307 width=15) (actual time=10,445.431..11,419.366 rows=519,769 loops=3)

  • Buffers: shared hit=7,643,648 read=35,899, temp read=32,382 written=33,228
8. 1,257.637 1,955.463 ↑ 1.5 383,207 3 / 3

Hash Join (cost=38,134.95..70,400.39 rows=574,588 width=32) (actual time=1,014.408..1,955.463 rows=383,207 loops=3)

  • Buffers: shared hit=6 read=35,897, temp read=12,549 written=12,812
9. 208.062 208.062 ↑ 1.2 557,116 3 / 3

Seq Scan on valuation_record_parcel_link vpl (cost=0..20,919.87 rows=694,687 width=14) (actual time=0.194..208.062 rows=557,116 loops=3)

  • Buffers: shared hit=3 read=13,970
10. 202.102 489.764 ↑ 1.3 319,575 3 / 3

Hash (cost=30,396.79..30,396.79 rows=400,173 width=30) (actual time=489.764..489.764 rows=319,575 loops=3)

  • Buffers: shared hit=3 read=21,927, temp written=5,272
11. 287.662 287.662 ↑ 1.3 319,575 3 / 3

Seq Scan on valuation_record vr (cost=0..30,396.79 rows=400,173 width=30) (actual time=0.027..287.662 rows=319,575 loops=3)

  • Filter: (((vr.creation_status)::text = 'A'::text) AND ((vr.effective_to IS NULL) OR (vr.effective_to >= CURRENT_TIMESTAMP)))
  • Buffers: shared hit=3 read=21,927
12. 609.389 8,067.743 ↑ 1.8 631,362 3 / 3

Hash (cost=532,389.1..532,389.1 rows=1,154,470 width=31) (actual time=8,067.742..8,067.743 rows=631,362 loops=3)

  • Buffers: shared hit=7,643,642 read=2, temp written=12,528
13. 1,298.826 7,458.354 ↑ 1.8 631,362 3 / 3

Merge Join (cost=5.54..532,389.1 rows=1,154,470 width=31) (actual time=4.729..7,458.354 rows=631,362 loops=3)

  • Buffers: shared hit=7,643,642 read=2
14. 487.904 5,693.321 ↑ 1.4 591,712 3 / 3

Nested Loop (cost=0.86..476,381.58 rows=803,971 width=30) (actual time=0.294..5,693.321 rows=591,712 loops=3)

  • Buffers: shared hit=7,590,772 read=1
15. 798.816 798.816 ↑ 1.2 489,622 3 / 3

Index Scan using idx_hgp_par_id on holding_group_parcel hgp (cost=0.43..113,807.66 rows=610,475 width=15) (actual time=0.016..798.816 rows=489,622 loops=3)

  • Buffers: shared hit=1,402,013
16. 4,406.601 4,406.601 ↑ 1.0 1 1,468,867 / 3

Index Scan using idx_holding_holding_group_id on holding h (cost=0.43..0.58 rows=1 width=31) (actual time=0.008..0.009 rows=1 loops=1,468,867)

  • Index Cond: (h.holding_group_id = hgp.holding_group_id)
  • Buffers: shared hit=6,188,759 read=1
17. 466.207 466.207 ↓ 1.2 1,487,952 3 / 3

Index Only Scan using idx_vltn_prcl_srvyprcl_id on valuation_parcel vrp (cost=0.43..39,476.05 rows=1,283,975 width=15) (actual time=0.634..466.207 rows=1,487,952 loops=3)

  • Heap Fetches: 220,237
  • Buffers: shared hit=52,870 read=1
Planning time : 6.203 ms
Execution time : 17,112.463 ms