explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rnWA : Optimization for: Optimization for: plan #xqbp; plan #4dN

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 1,887.071 ↑ 1.0 10 1

Limit (cost=3,529,270.560..3,529,270.660 rows=10 width=44) (actual time=1,887.039..1,887.071 rows=10 loops=1)

2. 0.012 1,887.065 ↑ 318.8 10 1

Unique (cost=3,529,270.560..3,529,302.440 rows=3,188 width=44) (actual time=1,887.038..1,887.065 rows=10 loops=1)

3. 296.631 1,887.053 ↑ 33.9 94 1

Sort (cost=3,529,270.560..3,529,278.530 rows=3,188 width=44) (actual time=1,887.037..1,887.053 rows=94 loops=1)

  • Sort Key: (((juliet_india1.six_mike ->> 'hotel'::text))::double precision) DESC, (lima_sierra(kilo5.lima_tango, 'uniform_romeo'::character varying)), kilo2.xray_india
  • Sort Method: external sort Disk: 10328kB
4. 179.220 1,590.422 ↓ 78.9 251,412 1

Nested Loop (cost=6.090..3,529,085.040 rows=3,188 width=44) (actual time=5.306..1,590.422 rows=251,412 loops=1)

  • Join Filter: (kilo2.quebec_seven = quebec_sierra3.romeo_lima)
5. 240.477 824.172 ↓ 34.8 58,703 1

Nested Loop Left Join (cost=5.520..3,474,703.990 rows=1,685 width=55) (actual time=5.161..824.172 rows=58,703 loops=1)

  • Join Filter: ((uniform_delta0.juliet_victor = kilo5.juliet_victor) AND (kilo2.xray_india = kilo5.xray_india))
  • Rows Removed by Join Filter: 1761073
6. 7.342 524.992 ↓ 34.8 58,703 1

Nested Loop (cost=1.150..3,474,396.300 rows=1,685 width=52) (actual time=5.107..524.992 rows=58,703 loops=1)

7. 0.021 0.021 ↑ 1.0 3 1

Seq Scan on oscar_yankee foxtrot (cost=0.000..1.660 rows=3 width=29) (actual time=0.010..0.021 rows=3 loops=1)

  • Filter: (juliet_victor = 1095)
  • Rows Removed by Filter: 61
8. 34.531 517.629 ↓ 34.8 19,568 3

Nested Loop (cost=1.150..1,158,125.920 rows=562 width=44) (actual time=4.319..172.543 rows=19,568 loops=3)

9. 21.075 248.628 ↓ 10.3 15,631 3

Nested Loop (cost=0.580..1,123,133.890 rows=1,518 width=40) (actual time=4.295..82.876 rows=15,631 loops=3)

10. 17.553 17.553 ↓ 6.2 10,000 3

Function Scan on charlie yankee_charlie (cost=0.000..16.000 rows=1,600 width=32) (actual time=4.062..5.851 rows=10,000 loops=3)

11. 210.000 210.000 ↓ 2.0 2 30,000

Index Scan using romeo_sierra on delta yankee_xray (cost=0.570..701.940 rows=1 width=8) (actual time=0.006..0.007 rows=2 loops=30,000)

  • Index Cond: (xray_india = ((juliet_india1.six_mike ->> 'yankee_foxtrot'::text))::integer)
  • Filter: ((lima_sierra(papa_three_two, yankee_kilo) <= 'lima_xray'::date) AND (lima_sierra(papa_three_two, yankee_kilo) >= 'xray_three'::date))
  • Rows Removed by Filter: 2
12. 234.470 234.470 ↑ 1.0 1 46,894

Index Scan using alpha_romeo on tango three_november (cost=0.570..23.040 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=46,894)

  • Index Cond: (romeo_lima = kilo2.quebec_seven)
  • Filter: (oscar_india ~~ 'six_kilo'::text)
  • Rows Removed by Filter: 1
13. 58.649 58.703 ↓ 3.3 30 58,703

Materialize (cost=4.370..42.330 rows=9 width=23) (actual time=0.000..0.001 rows=30 loops=58,703)

14. 0.036 0.054 ↓ 3.3 30 1

Bitmap Heap Scan on victor india (cost=4.370..42.280 rows=9 width=23) (actual time=0.028..0.054 rows=30 loops=1)

  • Recheck Cond: (juliet_victor = 1095)
  • Filter: ((lima_tango)::text = ANY ('papa_three_six'::text[]))
  • Heap Blocks: exact=18
15. 0.018 0.018 ↓ 2.7 30 1

Bitmap Index Scan on golf (cost=0.000..4.370 rows=11 width=0) (actual time=0.018..0.018 rows=30 loops=1)

  • Index Cond: (juliet_victor = 1095)
16. 587.030 587.030 ↓ 1.3 4 58,703

Index Scan using three_hotel on five uniform_yankee (cost=0.570..32.220 rows=3 width=4) (actual time=0.006..0.010 rows=4 loops=58,703)

  • Index Cond: (romeo_lima = alpha_quebec4.romeo_lima)
  • Filter: ((lima_romeo ~~ 'sierra'::text) OR (lima_romeo ~~ 'romeo_two'::text))
  • Rows Removed by Filter: 6
Planning time : 1.527 ms
Execution time : 1,889.830 ms