explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F6eH : Optimization for: plan #3kls

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 314.070 2,332.771 ↓ 51.0 51 1

Limit (cost=1,657,308.220..1,657,308.250 rows=1 width=96) (actual time=2,328.318..2,332.771 rows=51 loops=1)

  • Functions: 94
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.766 ms, Inlining 60.997 ms, Optimization 343.606 ms, Emission 234.149 ms, Total 645.518 ms
2. 3.832 2,018.701 ↓ 51.0 51 1

WindowAgg (cost=1,657,308.220..1,657,308.250 rows=1 width=96) (actual time=2,018.695..2,018.701 rows=51 loops=1)

3. 2.587 2,014.869 ↓ 24,993.0 24,993 1

Subquery Scan on two_golf (cost=1,657,308.220..1,657,308.230 rows=1 width=88) (actual time=2,009.294..2,014.869 rows=24,993 loops=1)

4. 11.650 2,012.282 ↓ 24,993.0 24,993 1

Sort (cost=1,657,308.220..1,657,308.220 rows=1 width=96) (actual time=2,009.286..2,012.282 rows=24,993 loops=1)

  • Sort Key: quebec.sierra DESC
  • Sort Method: external merge Disk: 2600kB
5. 7.507 2,000.632 ↓ 24,993.0 24,993 1

Merge Join (cost=1,645,139.600..1,657,308.210 rows=1 width=96) (actual time=1,877.991..2,000.632 rows=24,993 loops=1)

  • Merge Cond: (xray_whiskey.papa = quebec.papa)
6. 44.328 1,953.915 ↑ 24.3 24,993 1

Unique (cost=1,636,427.220..1,640,990.310 rows=608,412 width=16) (actual time=1,844.834..1,953.915 rows=24,993 loops=1)

7. 197.248 1,909.587 ↓ 1.0 611,744 1

Sort (cost=1,636,427.220..1,637,948.250 rows=608,412 width=16) (actual time=1,844.832..1,909.587 rows=611,744 loops=1)

  • Sort Key: xray_whiskey.papa, xray_mike.victor_lima
  • Sort Method: external merge Disk: 15608kB
8. 11.441 1,712.339 ↓ 1.0 611,744 1

Gather (cost=10,290.270..1,567,576.490 rows=608,412 width=16) (actual time=61.396..1,712.339 rows=611,744 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 17.057 1,700.898 ↑ 3.0 203,915 3 / 3

Parallel Append (cost=9,290.270..1,505,735.290 rows=608,412 width=16) (actual time=371.398..1,700.898 rows=203,915 loops=3)

10. 22.264 305.215 ↓ 2.4 113,273 1 / 3

Parallel Hash Join (cost=16,824.450..263,304.050 rows=46,962 width=16) (actual time=869.533..915.645 rows=113,273 loops=1)

  • Hash Cond: (xray_mike.golf_victor = xray_whiskey.golf_victor)
11. 191.867 196.841 ↓ 2.4 113,273 1 / 3

Parallel Bitmap Heap Scan on xray_mike (cost=2,114.060..247,418.380 rows=46,962 width=16) (actual time=22.420..590.522 rows=113,273 loops=1)

  • Recheck Cond: (victor_lima = 120)
  • Rows Removed by Index Recheck: 3942996
  • Filter: (yankee five NULL)
12. 4.974 4.974 ↓ 1.0 113,273 1 / 3

Bitmap Index Scan on foxtrot_six (cost=0.000..2,085.880 rows=112,709 width=0) (actual time=14.922..14.922 rows=113,273 loops=1)

  • Index Cond: (victor_lima = 120)
13. 14.120 86.110 ↓ 2.4 290,815 1 / 3

Parallel Hash (cost=12,603.730..12,603.730 rows=121,173 width=16) (actual time=258.330..258.330 rows=290,815 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2752kB
14. 71.990 71.990 ↓ 2.4 290,815 1 / 3

Parallel Seq Scan on xray_whiskey (cost=0.000..12,603.730 rows=121,173 width=16) (actual time=164.505..215.969 rows=290,815 loops=1)

  • Filter: (yankee five NULL)
15. 1,360.644 1,378.626 ↑ 1.2 166,157 3 / 3

Parallel Bitmap Heap Scan on hotel (cost=9,290.270..1,233,305.060 rows=206,543 width=16) (actual time=81.566..1,378.626 rows=166,157 loops=3)

  • Recheck Cond: (victor_lima = 120)
  • Rows Removed by Index Recheck: 13084002
  • Filter: (yankee five NULL)
  • Heap Blocks: exact=12653 lossy=130635
16. 17.982 17.982 ↓ 1.0 498,471 1 / 3

Bitmap Index Scan on six_two (cost=0.000..9,166.340 rows=495,703 width=0) (actual time=53.945..53.945 rows=498,471 loops=1)

  • Index Cond: (victor_lima = 120)
17. 3.377 39.210 ↓ 24,995.0 24,995 1

Materialize (cost=8,712.380..8,712.390 rows=1 width=96) (actual time=33.140..39.210 rows=24,995 loops=1)

18. 9.996 35.833 ↓ 24,995.0 24,995 1

Sort (cost=8,712.380..8,712.380 rows=1 width=96) (actual time=33.139..35.833 rows=24,995 loops=1)

  • Sort Key: quebec.papa
  • Sort Method: external merge Disk: 2600kB
19. 18.453 25.837 ↓ 24,996.0 24,996 1

Nested Loop Left Join (cost=583.360..8,712.370 rows=1 width=96) (actual time=0.522..25.837 rows=24,996 loops=1)

  • Join Filter: (((quebec.foxtrot_bravo = victor_foxtrot.two_hotel) AND (victor_foxtrot.six_india = 1)) OR ((quebec.foxtrot_bravo = victor_foxtrot.six_india) AND (victor_foxtrot.two_hotel = 1)))
  • Rows Removed by Join Filter: 124980
  • Filter: (victor_foxtrot.golf_sierra five NULL)
20. 7.384 7.384 ↑ 1.0 24,996 1

Seq Scan on quebec (cost=0.000..2,466.960 rows=24,996 width=96) (actual time=0.013..7.384 rows=24,996 loops=1)

  • Filter: ((yankee five NULL) AND (NOT november))
21. 0.000 0.000 ↑ 2.0 5 24,996

Materialize (cost=583.360..621.330 rows=10 width=24) (actual time=0.000..0.000 rows=5 loops=24,996)

22. 0.023 0.501 ↑ 2.0 5 1

Bitmap Heap Scan on victor_foxtrot (cost=583.360..621.280 rows=10 width=24) (actual time=0.486..0.501 rows=5 loops=1)

  • Recheck Cond: ((six_india = 1) OR (two_hotel = 1))
  • Filter: (yankee five NULL)
  • Heap Blocks: exact=5
23. 0.000 0.478 ↓ 0.0 0 1

BitmapOr (cost=583.360..583.360 rows=10 width=0) (actual time=0.478..0.478 rows=0 loops=1)

24. 0.472 0.472 ↓ 0.0 0 1

Bitmap Index Scan on three (cost=0.000..579.040 rows=6 width=0) (actual time=0.472..0.472 rows=0 loops=1)

  • Index Cond: (six_india = 1)
25. 0.006 0.006 ↓ 1.2 5 1

Bitmap Index Scan on three (cost=0.000..4.320 rows=4 width=0) (actual time=0.006..0.006 rows=5 loops=1)

  • Index Cond: (two_hotel = 1)
Planning time : 0.923 ms
Execution time : 2,339.760 ms