explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ANu8

Settings
# exclusive inclusive rows x rows loops node
1. 138.567 45,733.465 ↑ 8.0 25 1

GroupAggregate (cost=744,472.21..805,082.92 rows=200 width=28) (actual time=39,630.915..45,733.465 rows=25 loops=1)

  • Output: ov.dealership_id, count(ov.*), count(ov.*) FILTER (WHERE ((e.reynolds_style_id IS NULL) OR (e.reynolds_style_id = ''''::text))), CASE WHEN (count(ov.*) > 0) THEN ((count(ov.*) FILTER (WHERE ((e.reynolds_style_id IS NULL) OR (e.reynolds_style_id = (...)
  • Group Key: ov.dealership_id
  • Buffers: shared hit=3508400, temp read=35325 written=35333
2. 297.926 45,594.898 ↑ 1.2 910,059 1

Merge Left Join (cost=744,472.21..783,823.60 rows=1,062,791 width=61) (actual time=39,588.795..45,594.898 rows=910,059 loops=1)

  • Output: ov.dealership_id, ov.*, e.reynolds_style_id
  • Inner Unique: true
  • Merge Cond: ((ov.dealership_id = e.dealership_id) AND (ov.vin = e.vin))
  • Buffers: shared hit=3508400, temp read=35325 written=35333
3. 291.130 39,988.730 ↓ 1.6 910,059 1

Subquery Scan on ov (cost=556,836.02..580,989.60 rows=573,327 width=72) (actual time=34,565.075..39,988.730 rows=910,059 loops=1)

  • Output: ov.dealership_id, ov.*, ov.vin
  • Filter: ((ov.dms_cust_no IS NOT NULL) AND (ov.dms_cust_no <> ''::text))
  • Rows Removed by Filter: 170259
  • Buffers: shared hit=3399561, temp read=30472 written=30479
4. 563.526 39,697.600 ↓ 1.9 1,080,318 1

Unique (cost=556,836.02..573,750.80 rows=579,104 width=43) (actual time=34,565.057..39,697.600 rows=1,080,318 loops=1)

  • Output: a.dealership_id, a.vin, a.dms_cust_no, a.event_tstamp, a.rih_tstamp
  • Buffers: shared hit=3399561, temp read=30472 written=30479
5. 26,802.848 39,134.074 ↓ 1.9 4,375,242 1

Sort (cost=556,836.02..562,474.28 rows=2,255,304 width=43) (actual time=34,565.054..39,134.074 rows=4,375,242 loops=1)

  • Output: a.dealership_id, a.vin, a.dms_cust_no, a.event_tstamp, a.rih_tstamp
  • Sort Key: a.dealership_id, a.vin, a.event_tstamp DESC, a.rih_tstamp DESC
  • Sort Method: external merge Disk: 243776kB
  • Buffers: shared hit=3399561, temp read=30472 written=30479
6. 10,917.872 12,331.226 ↓ 1.9 4,375,242 1

Hash Left Join (cost=15,514.22..249,465.80 rows=2,255,304 width=43) (actual time=166.824..12,331.226 rows=4,375,242 loops=1)

  • Output: a.dealership_id, a.vin, a.dms_cust_no, a.event_tstamp, a.rih_tstamp
  • Hash Cond: ((a.dealership_id = vttoc.dealership_id) AND (a.event_id = vttoc.event_id))
  • Filter: CASE WHEN (vttoc.event_id IS NOT NULL) THEN "Xtream"."fnIsVTTUnique"(a.dealership_id, a.vin, (a.event_tstamp)::date, false) ELSE true END
  • Rows Removed by Filter: 53244
  • Buffers: shared hit=3399561
7. 1,247.439 1,247.439 ↑ 1.0 4,428,486 1

Seq Scan on "Xtream"."Event" a (cost=0.00..195,704.67 rows=4,510,608 width=59) (actual time=0.014..1,247.439 rows=4,428,486 loops=1)

  • Output: a.event_id, a.dealership_id, a.vin, a.dms_cust_no, a.department, a.event_tstamp, a.event_type, a.ownership_change_flag, a.rih_tstamp, a.record_added_tstamp, a.record_last_updated_tstamp, a.year, a.make, a.model (...)
  • Filter: ((a.event_type <> 'VTTRequest'::text) AND (a.event_type <> 'VTTReceive'::text))
  • Rows Removed by Filter: 1361082
  • Buffers: shared hit=108839
8. 68.781 165.915 ↓ 1.0 262,114 1

Hash (cost=11,614.78..11,614.78 rows=259,963 width=20) (actual time=165.915..165.915 rows=262,114 loops=1)

  • Output: vttoc.dealership_id, vttoc.event_id
  • Buckets: 262144 Batches: 1 Memory Usage: 15359kB
  • Buffers: shared hit=4646
9. 97.134 97.134 ↓ 1.0 262,114 1

Seq Scan on "Xtream"."OwnershipChange" vttoc (cost=0.00..11,614.78 rows=259,963 width=20) (actual time=41.360..97.134 rows=262,114 loops=1)

  • Output: vttoc.dealership_id, vttoc.event_id
  • Filter: (vttoc.code = 6)
  • Rows Removed by Filter: 295367
  • Buffers: shared hit=4646
10. 86.971 5,308.242 ↓ 2.7 203,587 1

Unique (cost=187,636.18..188,227.21 rows=74,149 width=43) (actual time=5,023.710..5,308.242 rows=203,587 loops=1)

  • Output: e.dealership_id, e.vin, e.reynolds_style_id, e.event_tstamp, e.rih_tstamp
  • Buffers: shared hit=108839, temp read=4853 written=4854
11. 4,145.436 5,221.271 ↓ 8.7 683,983 1

Sort (cost=187,636.18..187,833.19 rows=78,804 width=43) (actual time=5,023.706..5,221.271 rows=683,983 loops=1)

  • Output: e.dealership_id, e.vin, e.reynolds_style_id, e.event_tstamp, e.rih_tstamp
  • Sort Key: e.dealership_id, e.vin, e.event_tstamp DESC, e.rih_tstamp DESC
  • Sort Method: external merge Disk: 38824kB
  • Buffers: shared hit=108839, temp read=4853 written=4854
12. 1,075.835 1,075.835 ↓ 8.7 683,983 1

Seq Scan on "Xtream"."Event" e (cost=0.00..181,227.06 rows=78,804 width=43) (actual time=32.337..1,075.835 rows=683,983 loops=1)

  • Output: e.dealership_id, e.vin, e.reynolds_style_id, e.event_tstamp, e.rih_tstamp
  • Filter: ((e.reynolds_style_id IS NOT NULL) AND (e.reynolds_style_id <> ''::text))
  • Rows Removed by Filter: 5105585
  • Buffers: shared hit=108839