explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Q0A : 1

Settings
# exclusive inclusive rows x rows loops node
1. 15.126 285.742 ↓ 0.0 0 1

Update on pamlmeasigc ami (cost=9,480.33..9,490.26 rows=1 width=547) (actual time=285.742..285.742 rows=0 loops=1)

  • Buffers: shared hit=83218 read=247 dirtied=160
2.          

CTE tmpmeasdata

3. 1.087 265.256 ↓ 4.6 1,050 1

HashAggregate (cost=9,198.66..9,200.96 rows=230 width=120) (actual time=264.823..265.256 rows=1,050 loops=1)

  • Group Key: ami_1.amlmeashdrid, ami_1.amlmeaspomid, ami_1.pom_row, ami_1.sizecol, ami_1.amlmeasigcid, (sum(amic.incr)), (sum((amic.incr * 2.54)))
  • Buffers: shared hit=69653 read=118
4. 0.258 264.169 ↓ 4.8 1,100 1

Append (cost=4,593.01..9,194.64 rows=230 width=120) (actual time=125.992..264.169 rows=1,100 loops=1)

  • Buffers: shared hit=69653 read=118
5. 0.240 126.248 ↓ 1.3 150 1

GroupAggregate (cost=4,593.01..4,596.17 rows=115 width=120) (actual time=125.992..126.248 rows=150 loops=1)

  • Group Key: ami_1.amlmeasigcid
  • Buffers: shared hit=34769 read=118
6. 0.259 126.008 ↓ 2.6 300 1

Sort (cost=4,593.01..4,593.29 rows=115 width=59) (actual time=125.969..126.008 rows=300 loops=1)

  • Sort Key: ami_1.amlmeasigcid
  • Sort Method: quicksort Memory: 67kB
  • Buffers: shared hit=34769 read=118
7. 30.158 125.749 ↓ 2.6 300 1

Hash Join (cost=241.90..4,589.07 rows=115 width=59) (actual time=3.022..125.749 rows=300 loops=1)

  • Hash Cond: ((ami_1.amlmeaspomid = amic.amlmeaspomid) AND (ami_1.pom_row = amic.pom_row))
  • Join Filter: ((ami_1.sizecol <= amic.sizecol) AND (amic.sizecol <= (SubPlan 2)))
  • Rows Removed by Join Filter: 21750
  • Buffers: shared hit=34766 read=118
8. 0.655 0.655 ↑ 1.6 1,050 1

Seq Scan on pamlmeasigc ami_1 (cost=0.00..179.45 rows=1,635 width=56) (actual time=0.202..0.655 rows=1,050 loops=1)

  • Filter: (amlmeashdrid = '9667bf9f-99f0-44b3-984f-431495df6d50'::uuid)
  • Rows Removed by Filter: 2158
  • Buffers: shared hit=95 read=22
9. 0.980 2.536 ↑ 1.6 3,208 1

Hash (cost=166.96..166.96 rows=4,996 width=27) (actual time=2.536..2.536 rows=3,208 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 253kB
  • Buffers: shared hit=24 read=93
10. 1.556 1.556 ↑ 1.6 3,208 1

Seq Scan on pamlmeasigc amic (cost=0.00..166.96 rows=4,996 width=27) (actual time=0.004..1.556 rows=3,208 loops=1)

  • Buffers: shared hit=24 read=93
11.          

SubPlan (forHash Join)

12. 92.400 92.400 ↑ 1.0 1 11,550

Seq Scan on pamlmeaslbl aml_1 (cost=0.00..3.87 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=11,550)

  • Filter: ((issample = 1) AND (ami_1.amlmeashdrid = amlmeashdrid))
  • Rows Removed by Filter: 62
  • Buffers: shared hit=34647 read=3
13. 4.446 137.663 ↓ 8.3 950 1

GroupAggregate (cost=4,593.01..4,596.17 rows=115 width=120) (actual time=132.020..137.663 rows=950 loops=1)

  • Group Key: ami_2.amlmeasigcid
  • Buffers: shared hit=34884
14. 6.532 133.217 ↓ 82.6 9,500 1

Sort (cost=4,593.01..4,593.29 rows=115 width=59) (actual time=131.999..133.217 rows=9,500 loops=1)

  • Sort Key: ami_2.amlmeasigcid
  • Sort Method: quicksort Memory: 1720kB
  • Buffers: shared hit=34884
15. 32.035 126.685 ↓ 82.6 9,500 1

Hash Join (cost=241.90..4,589.07 rows=115 width=59) (actual time=1.886..126.685 rows=9,500 loops=1)

  • Hash Cond: ((ami_2.amlmeaspomid = amic_1.amlmeaspomid) AND (ami_2.pom_row = amic_1.pom_row))
  • Join Filter: ((ami_2.sizecol >= amic_1.sizecol) AND (amic_1.sizecol >= (SubPlan 1)))
  • Rows Removed by Join Filter: 12550
  • Buffers: shared hit=34884
16. 0.490 0.490 ↑ 1.6 1,050 1

Seq Scan on pamlmeasigc ami_2 (cost=0.00..179.45 rows=1,635 width=56) (actual time=0.026..0.490 rows=1,050 loops=1)

  • Filter: (amlmeashdrid = '9667bf9f-99f0-44b3-984f-431495df6d50'::uuid)
  • Rows Removed by Filter: 2158
  • Buffers: shared hit=117
17. 0.965 1.760 ↑ 1.6 3,208 1

Hash (cost=166.96..166.96 rows=4,996 width=27) (actual time=1.760..1.760 rows=3,208 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 253kB
  • Buffers: shared hit=117
18. 0.795 0.795 ↑ 1.6 3,208 1

Seq Scan on pamlmeasigc amic_1 (cost=0.00..166.96 rows=4,996 width=27) (actual time=0.004..0.795 rows=3,208 loops=1)

  • Buffers: shared hit=117
19.          

SubPlan (forHash Join)

20. 92.400 92.400 ↑ 1.0 1 11,550

Seq Scan on pamlmeaslbl aml (cost=0.00..3.87 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=11,550)

  • Filter: ((issample = 1) AND (ami_2.amlmeashdrid = amlmeashdrid))
  • Rows Removed by Filter: 62
  • Buffers: shared hit=34650
21. 1.305 270.616 ↓ 1,050.0 1,050 1

Hash Join (cost=279.37..289.29 rows=1 width=547) (actual time=267.842..270.616 rows=1,050 loops=1)

  • Hash Cond: ((tmd.amlmeasigcid = ami.amlmeasigcid) AND (tmd.amlmeaspomid = ami.amlmeaspomid) AND (tmd.amlmeashdrid = ami.amlmeashdrid) AND (tmd.pom_row = ami.pom_row) AND (tmd.sizecol = ami.sizecol))
  • Buffers: shared hit=69770 read=118
22. 266.373 266.373 ↓ 4.6 1,050 1

CTE Scan on tmpmeasdata tmd (cost=0.00..4.60 rows=230 width=264) (actual time=264.851..266.373 rows=1,050 loops=1)

  • Buffers: shared hit=69653 read=118
23. 1.809 2.938 ↑ 1.6 3,208 1

Hash (cost=166.96..166.96 rows=4,996 width=363) (actual time=2.938..2.938 rows=3,208 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 559kB
  • Buffers: shared hit=117
24. 1.129 1.129 ↑ 1.6 3,208 1

Seq Scan on pamlmeasigc ami (cost=0.00..166.96 rows=4,996 width=363) (actual time=0.009..1.129 rows=3,208 loops=1)

  • Buffers: shared hit=117