explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o816 : 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: Optimization for: Optimization for: plan #gWgH; plan #lj5Ms; plan #B8eq; plan #Yty9; plan #Lheu; plan #27c; plan #qUXb; plan #6xFD; plan #YOFp; plan #tvR9; plan #vDLc; plan #4jS3; plan #a2yB; plan #elHgV; plan #6VaS

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.012 54.588 ↑ 2.1 121 1

Limit (cost=19,062.35..19,079.85 rows=250 width=554) (actual time=54.449..54.588 rows=121 loops=1)

2. 0.106 54.576 ↑ 27.1 121 1

Unique (cost=19,062.35..19,291.67 rows=3,276 width=554) (actual time=54.448..54.576 rows=121 loops=1)

3. 0.184 54.470 ↑ 27.1 121 1

Sort (cost=19,062.35..19,070.54 rows=3,276 width=554) (actual time=54.446..54.470 rows=121 loops=1)

  • Sort Key: lima_delta1.mike_seven, lima_delta1.quebec_seven, lima_delta1.juliet_delta, lima_delta1.oscar_four, lima_delta1.november_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: 111kB
4. 3.399 54.286 ↑ 27.1 121 1

Hash Left Join (cost=2,339.12..18,871.07 rows=3,276 width=554) (actual time=16.126..54.286 rows=121 loops=1)

  • Hash Cond: (echo_seven1.delta_kilo = alpha_seven1.quebec_seven)
5. 0.071 50.038 ↑ 27.1 121 1

Hash Left Join (cost=2,064.61..16,917.18 rows=3,276 width=1,134) (actual time=15.153..50.038 rows=121 loops=1)

  • Hash Cond: (echo_seven1.four_delta = kilo_foxtrot2.quebec_seven)
6. 0.150 49.237 ↑ 27.1 121 1

Nested Loop Left Join (cost=1,859.31..16,703.24 rows=3,276 width=1,073) (actual time=14.415..49.237 rows=121 loops=1)

7. 0.070 48.603 ↑ 26.8 121 1

Hash Left Join (cost=1,858.88..9,092.15 rows=3,242 width=1,049) (actual time=14.401..48.603 rows=121 loops=1)

  • Hash Cond: (lima_delta1.quebec_seven = zulu_uniform1.sierra_foxtrot)
8. 0.096 46.283 ↑ 26.8 121 1

Hash Left Join (cost=1,366.94..8,583.99 rows=3,242 width=382) (actual time=12.138..46.283 rows=121 loops=1)

  • Hash Cond: ((kilo_foxtrot1.quebec_seven = alpha_oscar1.uniform_bravo) AND (lima_delta1.juliet_hotel = alpha_oscar1.foxtrot_charlie))
9. 0.092 42.661 ↑ 26.8 121 1

Hash Left Join (cost=1,031.57..8,231.60 rows=3,242 width=354) (actual time=8.593..42.661 rows=121 loops=1)

  • Hash Cond: (lima_delta1.lima_echo = kilo_foxtrot1.quebec_seven)
10. 0.087 38.822 ↑ 26.8 121 1

Hash Left Join (cost=424.00..7,615.50 rows=3,242 width=295) (actual time=4.827..38.822 rows=121 loops=1)

  • Hash Cond: (lima_delta1.two = papa_kilo1.quebec_seven)
11. 0.062 34.350 ↑ 26.8 121 1

Hash Left Join (cost=80.87..7,263.87 rows=3,242 width=284) (actual time=0.427..34.350 rows=121 loops=1)

  • Hash Cond: (lima_delta1.four_sierra = romeo_three1.quebec_seven)
12. 0.062 34.275 ↑ 26.8 121 1

Hash Left Join (cost=79.58..7,251.29 rows=3,242 width=275) (actual time=0.407..34.275 rows=121 loops=1)

  • Hash Cond: (lima_delta1.juliet_hotel = uniform_delta1.quebec_seven)
13. 0.696 33.864 ↑ 26.8 121 1

Hash Left Join (cost=1.65..7,164.76 rows=3,242 width=179) (actual time=0.051..33.864 rows=121 loops=1)

  • Hash Cond: (lima_delta1.juliet_hotel = india1.quebec_hotel)
  • Filter: ((lima_delta1.juliet_hotel = 81) OR (india1.yankee_zulu = 81))
  • Rows Removed by Filter: 3,736
14. 33.158 33.158 ↑ 2.6 3,857 1

Index Scan using papa_yankee on foxtrot_romeo quebec_sierra (cost=0.56..7,124.62 rows=9,970 width=179) (actual time=0.030..33.158 rows=3,857 loops=1)

  • Index Cond: ((lima_romeo)::text = 'quebec_quebec'::text)
  • Filter: (((NOT papa_bravo) OR (papa_bravo five_romeo NULL)) AND ((mike_seven)::date >= 'four_india'::date))
  • Rows Removed by Filter: 23,335
15. 0.003 0.010 ↑ 1.0 3 1

Hash (cost=1.05..1.05 rows=3 width=16) (actual time=0.009..0.010 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on hotel_three romeo_foxtrot (cost=0.00..1.05 rows=3 width=16) (actual time=0.005..0.007 rows=3 loops=1)

  • Filter: ((juliet_charlie)::text = 'kilo_papa'::text)
  • Rows Removed by Filter: 1
17. 0.115 0.349 ↑ 1.0 397 1

Hash (cost=72.97..72.97 rows=397 width=96) (actual time=0.349..0.349 rows=397 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
18. 0.234 0.234 ↑ 1.0 397 1

Seq Scan on echo_zulu mike_quebec (cost=0.00..72.97 rows=397 width=96) (actual time=0.004..0.234 rows=397 loops=1)

19. 0.005 0.013 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.008 0.008 ↑ 1.0 13 1

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

21. 1.996 4.385 ↓ 1.0 8,167 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 569kB
22. 2.389 2.389 ↓ 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.012..2.389 rows=8,167 loops=1)

23. 1.380 3.747 ↑ 1.0 5,181 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 569kB
24. 2.367 2.367 ↑ 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..2.367 rows=5,181 loops=1)

25. 1.512 3.526 ↓ 1.0 5,539 1

Hash (cost=252.95..252.95 rows=5,495 width=52) (actual time=3.526..3.526 rows=5,539 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 501kB
26. 2.014 2.014 ↓ 1.0 5,539 1

Seq Scan on delta_alpha five_xray (cost=0.00..252.95 rows=5,495 width=52) (actual time=0.009..2.014 rows=5,539 loops=1)

27. 1.028 2.250 ↑ 1.0 2,541 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 1,354kB
28. 1.222 1.222 ↑ 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.007..1.222 rows=2,541 loops=1)

29. 0.484 0.484 ↑ 1.0 1 121

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

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

Hash (cost=194.02..194.02 rows=902 width=69) (actual time=0.730..0.730 rows=902 loops=1)

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

Seq Scan on oscar_oscar yankee_xray (cost=0.00..194.02 rows=902 width=69) (actual time=0.006..0.499 rows=902 loops=1)

32. 0.309 0.849 ↓ 1.0 1,193 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 107kB
33. 0.540 0.540 ↓ 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.007..0.540 rows=1,193 loops=1)

Planning time : 16.976 ms
Execution time : 54.790 ms