explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sH9e

Settings
# exclusive inclusive rows x rows loops node
1. 19,787.292 19,787.292 ↓ 200.0 648,531 1

CTE Scan on delta_xray (cost=1,367,746.560..1,382,336.150 rows=3,242 width=1,137) (actual time=15,448.211..19,787.292 rows=648,531 loops=1)

  • Filter: (charlie_oscar = 1)
  • Buffers: shared hit=145476 read=55090 dirtied=11, temp read=83546 written=147598
2.          

CTE victor_hotel

3. 626.718 1,677.738 ↓ 1.0 653,332 1

WindowAgg (cost=253,761.310..266,824.950 rows=653,182 width=557) (actual time=941.880..1,677.738 rows=653,332 loops=1)

  • Buffers: shared read=23397, temp read=20878 written=20884
4. 646.764 1,051.020 ↓ 1.0 653,332 1

Sort (cost=253,761.310..255,394.270 rows=653,182 width=549) (actual time=941.868..1,051.020 rows=653,332 loops=1)

  • Sort Key: mike_bravo.charlie_alpha, mike_bravo.kilo DESC
  • Sort Method: external merge Disk: 167024kB
  • Buffers: shared read=23397, temp read=20878 written=20884
5. 404.256 404.256 ↓ 1.0 653,332 1

Seq Scan on alpha_golf mike_bravo (cost=0.000..29,928.820 rows=653,182 width=549) (actual time=0.017..404.256 rows=653,332 loops=1)

  • Buffers: shared read=23397
6.          

CTE yankee_alpha

7. 620.483 2,759.511 ↓ 1.0 648,531 1

WindowAgg (cost=126,779.710..142,990.360 rows=648,426 width=73) (actual time=2,002.224..2,759.511 rows=648,531 loops=1)

  • Buffers: shared hit=57704, temp read=3575 written=3576
8. 1,609.285 2,139.028 ↓ 1.0 648,531 1

Sort (cost=126,779.710..128,400.780 rows=648,426 width=33) (actual time=2,002.165..2,139.028 rows=648,531 loops=1)

  • Sort Key: november.seven_whiskey, november.seven_papa
  • Sort Method: external merge Disk: 28600kB
  • Buffers: shared hit=57704, temp read=3575 written=3576
9. 529.743 529.743 ↓ 1.0 648,531 1

Seq Scan on november (cost=0.000..64,185.260 rows=648,426 width=33) (actual time=0.012..529.743 rows=648,531 loops=1)

  • Buffers: shared hit=57701
10.          

CTE juliet

11. 350.300 2,433.216 ↓ 1.0 648,531 1

WindowAgg (cost=126,779.710..139,748.230 rows=648,426 width=41) (actual time=1,952.844..2,433.216 rows=648,531 loops=1)

  • Buffers: shared hit=57701, temp read=3575 written=3576
12. 1,639.032 2,082.916 ↓ 1.0 648,531 1

Sort (cost=126,779.710..128,400.780 rows=648,426 width=33) (actual time=1,952.836..2,082.916 rows=648,531 loops=1)

  • Sort Key: tango.seven_whiskey, tango.seven_papa
  • Sort Method: external merge Disk: 28600kB
  • Buffers: shared hit=57701, temp read=3575 written=3576
13. 443.884 443.884 ↓ 1.0 648,531 1

Seq Scan on november tango (cost=0.000..64,185.260 rows=648,426 width=33) (actual time=0.008..443.884 rows=648,531 loops=1)

  • Buffers: shared hit=57701
14.          

CTE delta_xray

15. 2,265.109 18,233.088 ↓ 1.0 648,531 1

WindowAgg (cost=782,519.590..818,183.020 rows=648,426 width=721) (actual time=15,448.206..18,233.088 rows=648,531 loops=1)

  • Buffers: shared hit=145476 read=55090 dirtied=11, temp read=83546 written=116071
16. 2,467.189 15,967.979 ↓ 1.0 648,531 1

Sort (cost=782,519.590..784,140.650 rows=648,426 width=606) (actual time=15,448.187..15,967.979 rows=648,531 loops=1)

  • Sort Key: golf1.seven_whiskey, seven_uniform.lima
  • Sort Method: external merge Disk: 227512kB
  • Buffers: shared hit=145476 read=55090 dirtied=11, temp read=83546 written=116071
17. 978.508 13,500.790 ↓ 1.0 648,531 1

WindowAgg (cost=426,213.750..544,834.140 rows=648,426 width=606) (actual time=10,596.244..13,500.790 rows=648,531 loops=1)

  • Buffers: shared hit=145470 read=55090 dirtied=11, temp read=55107 written=87626
18. 266.913 12,522.282 ↓ 1.0 648,531 1

Merge Left Join (cost=426,213.750..533,486.680 rows=648,426 width=598) (actual time=10,596.229..12,522.282 rows=648,531 loops=1)

  • Merge Cond: (golf1.kilo = seven_uniform.charlie_alpha)
  • Buffers: shared hit=145470 read=55090 dirtied=11, temp read=55107 written=87626
19. 267.101 9,333.606 ↓ 1.0 648,531 1

Merge Left Join (cost=350,650.780..446,576.260 rows=648,426 width=590) (actual time=7,769.951..9,333.606 rows=648,531 loops=1)

  • Merge Cond: (golf1.kilo = quebec_xray.charlie_alpha)
  • Buffers: shared hit=87769 read=55090 dirtied=11, temp read=51532 written=79650
20. 433.967 5,806.289 ↓ 1.0 648,531 1

Merge Left Join (cost=275,087.810..359,665.830 rows=648,426 width=582) (actual time=4,607.297..5,806.289 rows=648,531 loops=1)

  • Merge Cond: (golf1.kilo = whiskey1.charlie_alpha)
  • Buffers: shared hit=30065 read=55090 dirtied=11, temp read=47957 written=70002
21. 0.000 2,286.151 ↓ 1.0 648,531 1

Gather Merge (cost=260,200.590..343,108.560 rows=648,426 width=230) (actual time=1,647.684..2,286.151 rows=648,531 loops=1)

  • Workers Planned: 4
  • Workers Launched: 3
  • Buffers: shared hit=90402 read=162988 dirtied=19, temp read=27643 written=27647
22. 680.412 7,619.556 ↓ 1.0 162,133 4

Merge Left Join (cost=259,200.530..264,874.710 rows=162,106 width=230) (actual time=1,617.869..1,904.889 rows=162,133 loops=4)

  • Merge Cond: (golf1.kilo = romeo.xray)
  • Buffers: shared hit=90402 read=162988 dirtied=19, temp read=27643 written=27647
23. 586.056 3,857.180 ↓ 1.0 162,133 4

Sort (cost=160,052.810..160,458.080 rows=162,106 width=213) (actual time=945.381..964.295 rows=162,133 loops=4)

  • Sort Key: golf1.kilo
  • Sort Method: external merge Disk: 37816kB
  • Worker 0: Sort Method: quicksort Memory: 46909kB
  • Worker 1: Sort Method: quicksort Memory: 51398kB
  • Worker 2: Sort Method: quicksort Memory: 51552kB
  • Buffers: shared hit=231 read=132931 dirtied=19, temp read=4727 written=4727
24. 400.072 3,271.124 ↓ 1.0 162,133 4

Parallel Hash Left Join (cost=84,814.570..146,025.310 rows=162,106 width=213) (actual time=377.594..817.781 rows=162,133 loops=4)

  • Hash Cond: (golf1.seven_lima = mike_xray.kilo)
  • Buffers: shared hit=219 read=132931 dirtied=19
25. 882.796 1,466.204 ↓ 1.0 162,133 4

Parallel Hash Left Join (cost=5,060.480..65,845.690 rows=162,106 width=176) (actual time=23.726..366.551 rows=162,133 loops=4)

  • Hash Cond: ((golf1.seven_whiskey)::text = (quebec_sierra.victor_charlie)::text)
  • Buffers: shared hit=86 read=60472 dirtied=19
26. 489.492 489.492 ↓ 1.0 162,133 4

Parallel Seq Scan on november quebec_lima (cost=0.000..59,322.070 rows=162,106 width=168) (actual time=0.021..122.373 rows=162,133 loops=4)

  • Buffers: shared hit=1 read=57700
27. 46.884 93.916 ↑ 2.4 38,332 4

Parallel Hash (cost=3,933.240..3,933.240 rows=90,179 width=29) (actual time=23.478..23.479 rows=38,332 loops=4)

  • Buckets: 262144 Batches: 1 Memory Usage: 11936kB
  • Buffers: shared hit=34 read=2772 dirtied=19
28. 47.032 47.032 ↑ 2.4 38,332 4

Parallel Seq Scan on delta_kilo quebec_sierra (cost=0.000..3,933.240 rows=90,179 width=29) (actual time=0.021..11.758 rows=38,332 loops=4)

  • Filter: ((victor_charlie)::text <> ''::text)
  • Buffers: shared hit=34 read=2772 dirtied=19
29. 555.056 1,404.848 ↓ 1.0 324,196 4

Parallel Hash (cost=75,701.820..75,701.820 rows=324,182 width=41) (actual time=351.212..351.212 rows=324,196 loops=4)

  • Buckets: 2097152 Batches: 1 Memory Usage: 115424kB
  • Buffers: shared hit=1 read=72459
30. 849.792 849.792 ↓ 1.0 324,196 4

Parallel Seq Scan on alpha_tango mike_xray (cost=0.000..75,701.820 rows=324,182 width=41) (actual time=0.034..212.448 rows=324,196 loops=4)

  • Buffers: shared hit=1 read=72459
31. 1,828.988 3,081.964 ↑ 1.0 648,516 4

Sort (cost=99,147.650..100,768.980 rows=648,531 width=21) (actual time=672.477..770.491 rows=648,516 loops=4)

  • Sort Key: romeo.xray
  • Sort Method: external sort Disk: 22920kB
  • Worker 0: Sort Method: external sort Disk: 22920kB
  • Worker 1: Sort Method: external sort Disk: 22920kB
  • Worker 2: Sort Method: external sort Disk: 22920kB
  • Buffers: shared hit=90171 read=30057, temp read=22916 written=22920
32. 1,252.976 1,252.976 ↑ 1.0 648,516 4

Seq Scan on india romeo (cost=0.000..36,542.310 rows=648,531 width=21) (actual time=0.020..313.244 rows=648,516 loops=4)

  • Buffers: shared hit=90171 read=30057
33. 463.297 3,086.171 ↓ 178.2 582,028 1

Sort (cost=14,887.220..14,895.390 rows=3,266 width=356) (actual time=2,959.607..3,086.171 rows=582,028 loops=1)

  • Sort Key: whiskey1.charlie_alpha
  • Sort Method: external sort Disk: 66496kB
  • Buffers: shared read=23397, temp read=37501 written=59545
34. 2,622.874 2,622.874 ↓ 178.2 582,028 1

CTE Scan on victor_hotel yankee_bravo (cost=0.000..14,696.590 rows=3,266 width=356) (actual time=941.885..2,622.874 rows=582,028 loops=1)

  • Filter: (five = 1)
  • Rows Removed by Filter: 71304
  • Buffers: shared read=23397, temp read=20878 written=42921
35. 265.170 3,260.216 ↓ 1.0 648,531 1

Sort (cost=75,562.970..77,184.040 rows=648,426 width=12) (actual time=3,162.648..3,260.216 rows=648,531 loops=1)

  • Sort Key: quebec_xray.charlie_alpha
  • Sort Method: quicksort Memory: 47876kB
  • Buffers: shared hit=57704, temp read=3575 written=9648
36. 2,995.046 2,995.046 ↓ 1.0 648,531 1

CTE Scan on yankee_alpha quebec_xray (cost=0.000..12,968.520 rows=648,426 width=12) (actual time=2,002.227..2,995.046 rows=648,531 loops=1)

  • Buffers: shared hit=57704, temp read=3575 written=9648
37. 289.115 2,921.763 ↓ 1.0 648,531 1

Sort (cost=75,562.970..77,184.040 rows=648,426 width=12) (actual time=2,826.272..2,921.763 rows=648,531 loops=1)

  • Sort Key: seven_uniform.charlie_alpha
  • Sort Method: quicksort Memory: 47876kB
  • Buffers: shared hit=57701, temp read=3575 written=7976
38. 2,632.648 2,632.648 ↓ 1.0 648,531 1

CTE Scan on juliet seven_uniform (cost=0.000..12,968.520 rows=648,426 width=12) (actual time=1,952.845..2,632.648 rows=648,531 loops=1)

  • Buffers: shared hit=57701, temp read=3575 written=7976
Planning time : 2.524 ms