explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MNcH

Settings
# exclusive inclusive rows x rows loops node
1. 182,714.723 182,714.723 ↓ 2.0 53,344 1

CTE Scan on three_hotel (cost=25,687,687.780..25,810,576.660 rows=27,309 width=718) (actual time=181,311.812..182,714.723 rows=53,344 loops=1)

  • Filter: (november_bravo = 1)
  • Rows Removed by Filter: 257059
  • Buffers: shared hit=25084 read=159692, temp read=32665 written=46756
2.          

CTE alpha_zulu

3. 22,025.511 22,102.075 ↑ 1.0 262,611 1

Bitmap Heap Scan on november_quebec (cost=9,378.820..136,930.570 rows=263,020 width=151) (actual time=99.112..22,102.075 rows=262,611 loops=1)

  • Recheck Cond: (xray && 'juliet'::seven_five)
  • Heap Blocks: exact=89938
  • Buffers: shared hit=6730 read=85019
4. 76.564 76.564 ↑ 1.0 262,611 1

Bitmap Index Scan on india (cost=0.000..9,313.070 rows=263,020 width=0) (actual time=76.564..76.564 rows=262,611 loops=1)

  • Index Cond: (xray && 'juliet'::seven_five)
  • Buffers: shared hit=1 read=1810
5.          

CTE papa

6. 123,819.138 140,900.795 ↓ 1.0 287,693 1

Bitmap Heap Scan on yankee (cost=9,817.450..198,901.360 rows=275,987 width=117) (actual time=17,102.052..140,900.795 rows=287,693 loops=1)

  • Recheck Cond: (xray && 'juliet'::seven_five)
  • Filter: (lima = 'november_golf'::bpchar)
  • Rows Removed by Filter: 400
  • Heap Blocks: exact=57399
  • Buffers: shared hit=1 read=59420
7. 17,081.657 17,081.657 ↓ 1.0 288,093 1

Bitmap Index Scan on charlie (cost=0.000..9,748.460 rows=276,272 width=0) (actual time=17,081.657..17,081.657 rows=288,093 loops=1)

  • Index Cond: (xray && 'juliet'::seven_five)
  • Buffers: shared read=2022
8.          

CTE quebec

9. 0.566 1,694.988 ↑ 242.0 3 1

GroupAggregate (cost=41,524.950..208,105.210 rows=726 width=23) (actual time=1,189.557..1,694.988 rows=3 loops=1)

  • Group Key: delta_victor110.oscar_victor, delta_victor110.hotel
  • Buffers: shared hit=18353 read=15253, temp read=5306 written=961
10. 137.097 1,694.422 ↑ 953.3 3,332 1

Merge Join (cost=41,524.950..184,274.680 rows=3,176,436 width=23) (actual time=1,188.842..1,694.422 rows=3,332 loops=1)

  • Merge Cond: (((delta_victor110.oscar_victor)::text = (alpha_zulu.uniform_oscar)::text) AND (delta_victor110.hotel = alpha_zulu.hotel))
  • Buffers: shared hit=18353 read=15253, temp read=5306 written=961
11. 484.791 484.791 ↑ 1.2 830,082 1

Index Scan using three_zulu on november_seven (cost=0.420..81,709.740 rows=959,046 width=23) (actual time=0.034..484.791 rows=830,082 loops=1)

  • Buffers: shared hit=18353 read=15253
12. 27.622 1,072.534 ↑ 1.0 254,960 1

Materialize (cost=41,524.530..42,839.630 rows=263,020 width=82) (actual time=1,004.034..1,072.534 rows=254,960 loops=1)

  • Buffers: temp read=5306 written=961
13. 666.708 1,044.912 ↑ 1.0 251,631 1

Sort (cost=41,524.530..42,182.080 rows=263,020 width=82) (actual time=1,004.029..1,044.912 rows=251,631 loops=1)

  • Sort Key: alpha_zulu.uniform_oscar, alpha_zulu.hotel
  • Sort Method: external merge Disk: 7672kB
  • Buffers: temp read=5306 written=961
14. 378.204 378.204 ↑ 1.0 262,611 1

CTE Scan on alpha_zulu (cost=0.000..5,260.400 rows=263,020 width=82) (actual time=45.405..378.204 rows=262,611 loops=1)

  • Buffers: temp read=4346 written=1
15.          

CTE three_hotel

16. 335.329 181,736.995 ↑ 17.6 310,403 1

WindowAgg (cost=24,734,121.040..25,143,750.640 rows=5,461,728 width=1,024) (actual time=181,311.802..181,736.995 rows=310,403 loops=1)

  • Buffers: shared hit=25084 read=159692, temp read=32665 written=37202
17. 5,684.641 181,401.666 ↑ 17.6 310,403 1

Sort (cost=24,734,121.040..24,747,775.360 rows=5,461,728 width=1,024) (actual time=181,311.773..181,401.666 rows=310,403 loops=1)

  • Sort Key: (CASE WHEN (mike_hotel1.delta_six five NOT NULL) THEN mike_hotel1.oscar_uniform ELSE papa.oscar_uniform END), (CASE uniform_bravo((mike_hotel1.two)::text) WHEN 'four_uniform'::text THEN 1 ELSE 2 END), (CASE uniform_bravo((mike_hotel1.two)::text) WHEN 'kilo'::text THEN 1 ELSE 2 END), (CASE uniform_bravo((mike_hotel1.two)::text) WHEN 'oscar_yankee'::text THEN 1 ELSE 2 END), (uniform_bravo((mike_hotel1.two)::text)), (uniform_bravo((papa.alpha_mike)::text)), mike_hotel1.mike_india, mike_hotel1.six_november, mike_hotel1.six_whiskey, mike_hotel1.whiskey DESC
  • Sort Method: external sort Disk: 75232kB
  • Buffers: shared hit=25084 read=159692, temp read=32665 written=37202
18. 961.033 175,717.025 ↑ 17.6 310,403 1

WindowAgg (cost=18,958,815.990..19,231,902.390 rows=5,461,728 width=1,024) (actual time=173,877.711..175,717.025 rows=310,403 loops=1)

  • Buffers: shared hit=25084 read=159692, temp read=23261 written=27798
19. 1,750.766 174,755.992 ↑ 17.6 310,403 1

Sort (cost=18,958,815.990..18,972,470.310 rows=5,461,728 width=1,024) (actual time=173,877.694..174,755.992 rows=310,403 loops=1)

  • Sort Key: (CASE WHEN (mike_hotel1.delta_six five NOT NULL) THEN mike_hotel1.oscar_uniform ELSE papa.oscar_uniform END)
  • Sort Method: external merge Disk: 72120kB
  • Buffers: shared hit=25084 read=159692, temp read=23261 written=27798
20. 391.333 173,005.226 ↑ 17.6 310,403 1

Hash Left Join (cost=179,947.460..13,456,597.330 rows=5,461,728 width=1,024) (actual time=171,299.967..173,005.226 rows=310,403 loops=1)

  • Hash Cond: (((mike_hotel1.uniform_oscar)::text = (quebec.oscar_victor)::text) AND (mike_hotel1.hotel = quebec.hotel))
  • Buffers: shared hit=25084 read=159692, temp read=14237 written=18774
21. 316.352 170,918.876 ↑ 17.6 310,403 1

Merge Full Join (cost=179,922.050..12,431,506.620 rows=5,461,728 width=992) (actual time=169,604.871..170,918.876 rows=310,403 loops=1)

  • Merge Cond: (mike_hotel1.delta_six = papa.delta_six)
  • Filter: (((mike_hotel1.sierra = 'four_uniform'::bpchar) AND ((uniform_bravo((mike_hotel1.two)::text) = ANY ('seven_two'::text[])) OR (uniform_bravo((mike_hotel1.two)::text) five NULL))) OR (mike_hotel1.sierra = ANY ('mike_mike'::bpchar[])) OR (mike_hotel1.uniform_oscar five NULL))
  • Buffers: shared hit=6731 read=144439, temp read=8931 written=17813
22. 1,622.851 24,139.040 ↑ 1.0 262,611 1

Sort (cost=90,972.530..91,630.080 rows=263,020 width=524) (actual time=23,403.725..24,139.040 rows=262,611 loops=1)

  • Sort Key: mike_hotel1.delta_six
  • Sort Method: external merge Disk: 35024kB
  • Buffers: shared hit=6730 read=85019, temp read=4384 written=8728
23. 22,516.189 22,516.189 ↑ 1.0 262,611 1

CTE Scan on alpha_zulu four_lima (cost=0.000..5,260.400 rows=263,020 width=524) (actual time=99.128..22,516.189 rows=262,611 loops=1)

  • Buffers: shared hit=6730 read=85019, temp written=4344
24. 46.830 146,463.484 ↓ 1.1 310,389 1

Materialize (cost=88,949.520..90,329.450 rows=275,987 width=468) (actual time=146,201.118..146,463.484 rows=310,389 loops=1)

  • Buffers: shared hit=1 read=59420, temp read=4547 written=9085
25. 4,895.853 146,416.654 ↓ 1.0 287,693 1

Sort (cost=88,949.520..89,639.480 rows=275,987 width=468) (actual time=146,201.109..146,416.654 rows=287,693 loops=1)

  • Sort Key: papa.delta_six
  • Sort Method: external merge Disk: 36336kB
  • Buffers: shared hit=1 read=59420, temp read=4547 written=9085
26. 141,520.801 141,520.801 ↓ 1.0 287,693 1

CTE Scan on papa (cost=0.000..5,519.740 rows=275,987 width=468) (actual time=17,102.057..141,520.801 rows=287,693 loops=1)

  • Buffers: shared hit=1 read=59420, temp written=4538
27. 0.008 1,695.017 ↑ 242.0 3 1

Hash (cost=14.520..14.520 rows=726 width=114) (actual time=1,695.017..1,695.017 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=18353 read=15253, temp read=5306 written=961
28. 1,695.009 1,695.009 ↑ 242.0 3 1

CTE Scan on quebec (cost=0.000..14.520 rows=726 width=114) (actual time=1,189.561..1,695.009 rows=3 loops=1)

  • Buffers: shared hit=18353 read=15253, temp read=5306 written=961