explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V0uU : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #XHT8; plan #VT6p; plan #3B7m; plan #i57iN; plan #kmS; plan #1cmJ; plan #O7Gn; plan #KqxH; plan #FpgV; plan #y4xt; plan #4Rms; plan #aQqA; plan #aFCE

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 30.244 ↑ 2.7 93 1

Limit (cost=18,717.98..18,735.48 rows=250 width=554) (actual time=30.171..30.244 rows=93 loops=1)

2. 0.054 30.237 ↑ 32.6 93 1

Unique (cost=18,717.98..18,930.22 rows=3,032 width=554) (actual time=30.171..30.237 rows=93 loops=1)

3. 0.099 30.183 ↑ 32.6 93 1

Sort (cost=18,717.98..18,725.56 rows=3,032 width=554) (actual time=30.170..30.183 rows=93 loops=1)

  • Sort Key: lima_delta1.quebec_seven, lima_delta1.juliet_delta, lima_delta1.oscar_four, lima_delta1.november_seven, lima_delta1.mike_seven, lima_delta1.foxtrot_bravo, lima_delta1.golf_echo, lima_delta1.zulu_alpha, lima_delta1.november_romeo, romeo_three1.three, romeo_three1.six_xray, kilo_foxtrot1.quebec_seven, kilo_foxtrot1.juliet_delta, kilo_foxtrot1.three, kilo_foxtrot2.quebec_seven, kilo_foxtrot2.juliet_delta, kilo_foxtrot2.three, alpha_seven1.three, alpha_seven1.yankee_tango, uniform_delta1.quebec_seven, uniform_delta1.juliet_delta, uniform_delta1.three, papa_kilo1.whiskey_zulu, echo_seven1.victor_five, ((zulu_uniform1.oscar_sierra five_romeo NOT NULL)), (six_uniform(0, alpha_oscar1.six_uniform, lima_delta1.foxtrot_bravo, LOCALTIMESTAMP, lima_delta1.mike_seven)), ((six_uniform(1, alpha_oscar1.six_uniform, lima_delta1.foxtrot_bravo, LOCALTIMESTAMP, lima_delta1.mike_seven))::boolean)
  • Sort Method: quicksort Memory: 94kB
4. 1.829 30.084 ↑ 32.6 93 1

Hash Left Join (cost=2,122.43..18,542.64 rows=3,032 width=554) (actual time=7.407..30.084 rows=93 loops=1)

  • Hash Cond: (echo_seven1.delta_kilo = alpha_seven1.quebec_seven)
5. 0.047 27.735 ↑ 32.6 93 1

Hash Left Join (cost=1,847.93..16,713.83 rows=3,032 width=1,134) (actual time=6.800..27.735 rows=93 loops=1)

  • Hash Cond: (echo_seven1.four_delta = kilo_foxtrot2.quebec_seven)
6. 0.051 27.251 ↑ 32.6 93 1

Nested Loop Left Join (cost=1,643.63..16,501.55 rows=3,032 width=1,073) (actual time=6.356..27.251 rows=93 loops=1)

7. 0.039 26.828 ↑ 32.3 93 1

Nested Loop Left Join (cost=1,643.20..8,842.75 rows=3,000 width=1,049) (actual time=6.347..26.828 rows=93 loops=1)

  • Join Filter: (lima_delta1.juliet_hotel = india1.quebec_hotel)
8. 0.050 26.789 ↑ 32.3 93 1

Hash Left Join (cost=1,643.20..8,796.68 rows=3,000 width=1,057) (actual time=6.340..26.789 rows=93 loops=1)

  • Hash Cond: (lima_delta1.quebec_seven = zulu_uniform1.sierra_foxtrot)
9. 0.059 25.256 ↑ 32.3 93 1

Hash Left Join (cost=1,151.26..8,289.74 rows=3,000 width=390) (actual time=4.848..25.256 rows=93 loops=1)

  • Hash Cond: ((lima_delta1.juliet_hotel = alpha_oscar1.foxtrot_charlie) AND (kilo_foxtrot1.quebec_seven = alpha_oscar1.uniform_bravo))
10. 0.065 24.922 ↑ 32.3 93 1

Hash Left Join (cost=952.82..8,075.53 rows=3,000 width=354) (actual time=4.566..24.922 rows=93 loops=1)

  • Hash Cond: (lima_delta1.lima_echo = kilo_foxtrot1.quebec_seven)
11. 0.059 22.667 ↑ 32.3 93 1

Hash Left Join (cost=345.25..7,460.08 rows=3,000 width=295) (actual time=2.361..22.667 rows=93 loops=1)

  • Hash Cond: (lima_delta1.two_romeo = papa_kilo1.quebec_seven)
12. 0.044 20.347 ↑ 32.3 93 1

Hash Left Join (cost=2.12..7,109.07 rows=3,000 width=284) (actual time=0.087..20.347 rows=93 loops=1)

  • Hash Cond: (lima_delta1.four_sierra = romeo_three1.quebec_seven)
13. 0.063 20.293 ↑ 32.3 93 1

Nested Loop Left Join (cost=0.83..7,097.34 rows=3,000 width=275) (actual time=0.073..20.293 rows=93 loops=1)

  • Join Filter: (lima_delta1.juliet_hotel = uniform_delta1.quebec_seven)
14. 20.230 20.230 ↑ 32.3 93 1

Index Scan using papa_yankee on foxtrot_romeo quebec_sierra (cost=0.56..7,049.84 rows=3,000 width=179) (actual time=0.064..20.230 rows=93 loops=1)

  • Index Cond: ((lima_romeo)::text = 'quebec_quebec'::text)
  • Filter: (((NOT papa_bravo) OR (papa_bravo five_romeo NULL)) AND (juliet_hotel = 81))
  • Rows Removed by Filter: 26,906
15. 0.000 0.000 ↑ 1.0 1 93

Materialize (cost=0.27..2.50 rows=1 width=96) (actual time=0.000..0.000 rows=1 loops=93)

16. 0.006 0.006 ↑ 1.0 1 1

Index Scan using two_delta on echo_zulu mike_quebec (cost=0.27..2.49 rows=1 width=96) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (quebec_seven = 81)
17. 0.003 0.010 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=25) (actual time=0.010..0.010 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.007 0.007 ↑ 1.0 13 1

Seq Scan on tango four_uniform (cost=0.00..1.13 rows=13 width=25) (actual time=0.005..0.007 rows=13 loops=1)

19. 0.981 2.261 ↓ 1.0 8,167 1

Hash (cost=241.39..241.39 rows=8,139 width=27) (actual time=2.261..2.261 rows=8,167 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 569kB
20. 1.280 1.280 ↓ 1.0 8,167 1

Seq Scan on golf_five uniform_romeo (cost=0.00..241.39 rows=8,139 width=27) (actual time=0.003..1.280 rows=8,167 loops=1)

21. 0.713 2.190 ↑ 1.0 5,181 1

Hash (cost=542.81..542.81 rows=5,181 width=67) (actual time=2.190..2.190 rows=5,181 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 569kB
22. 1.477 1.477 ↑ 1.0 5,181 1

Seq Scan on sierra_three whiskey_six (cost=0.00..542.81 rows=5,181 width=67) (actual time=0.005..1.477 rows=5,181 loops=1)

23. 0.065 0.275 ↓ 1.0 352 1

Hash (cost=193.23..193.23 rows=347 width=52) (actual time=0.275..0.275 rows=352 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 65kB
24. 0.210 0.210 ↓ 1.0 352 1

Index Scan using five_victor on delta_alpha five_xray (cost=0.28..193.23 rows=347 width=52) (actual time=0.008..0.210 rows=352 loops=1)

  • Index Cond: (foxtrot_charlie = 81)
25. 0.594 1.483 ↑ 1.0 2,541 1

Hash (cost=459.75..459.75 rows=2,575 width=675) (actual time=1.482..1.483 rows=2,541 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 1,354kB
26. 0.889 0.889 ↑ 1.0 2,541 1

Seq Scan on juliet_november romeo_kilo (cost=0.00..459.75 rows=2,575 width=675) (actual time=0.005..0.889 rows=2,541 loops=1)

27. 0.000 0.000 ↓ 0.0 0 93

Materialize (cost=0.00..1.06 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=93)

28. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on hotel_three romeo_foxtrot (cost=0.00..1.06 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((quebec_hotel = 81) AND ((juliet_charlie)::text = 'kilo_papa'::text))
  • Rows Removed by Filter: 4
29. 0.372 0.372 ↑ 1.0 1 93

Index Scan using charlie on uniform_kilo victor_romeo (cost=0.43..2.54 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=93)

  • Index Cond: (sierra_foxtrot = lima_delta1.quebec_seven)
30. 0.120 0.437 ↑ 1.0 902 1

Hash (cost=193.02..193.02 rows=902 width=69) (actual time=0.436..0.437 rows=902 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 98kB
31. 0.317 0.317 ↑ 1.0 902 1

Seq Scan on oscar_oscar yankee_xray (cost=0.00..193.02 rows=902 width=69) (actual time=0.004..0.317 rows=902 loops=1)

32. 0.163 0.520 ↓ 1.0 1,193 1

Hash (cost=259.78..259.78 rows=1,178 width=41) (actual time=0.519..0.520 rows=1,193 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 107kB
33. 0.357 0.357 ↓ 1.0 1,193 1

Seq Scan on whiskey_tango hotel_golf (cost=0.00..259.78 rows=1,178 width=41) (actual time=0.005..0.357 rows=1,193 loops=1)

Planning time : 5.730 ms
Execution time : 30.382 ms