explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r9or : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #E3qw; plan #BFpM; plan #zYcE; plan #9E4V; plan #5PSt; plan #50k

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.096 5.425 ↓ 3.0 3 1

Result (cost=73.02..73.29 rows=1 width=2,173) (actual time=5.398..5.425 rows=3 loops=1)

  • Output: romeo_three1.juliet_delta, romeo_three1.oscar, romeo_three1.november_seven, romeo_three1.three_five, romeo_three1.foxtrot_bravo, romeo_three1.lima_romeo, romeo_three1.seven_seven, romeo_three2.juliet_delta, romeo_three2.three_zulu, CASE WHEN (quebec_echo(romeo_three2.quebec_bravo) > 1316) THEN romeo_three3.charlie ELSE NULL::character varying END, romeo_three1.mike
  • Buffers: shared hit=1,750
2.          

CTE sierra_golf

3. 3.344 4.063 ↓ 1,676.0 1,676 1

Bitmap Heap Scan on two_india zulu_xray (cost=66.71..67.82 rows=1 width=295) (actual time=0.915..4.063 rows=1,676 loops=1)

  • Output: romeo_three1kilo_oscar1.quebec_seven, romeo_three1kilo_oscar1.delta_uniform_lima, romeo_three1kilo_oscar1.three_five, romeo_three1kilo_oscar1.seven_seven, romeo_three1kilo_oscar1.november_seven, romeo_three1kilo_oscar1.mike, romeo_three1kilo_oscar1.foxtrot_bravo, romeo_three1kilo_oscar1.golf_echo, romeo_three1kilo_oscar1.zulu_alpha, romeo_three1kilo_oscar1.oscar, romeo_three1kilo_oscar1.victor_tango, romeo_three1kilo_oscar1.lima_echo, romeo_three1kilo_oscar1.juliet_hotel, romeo_three1kilo_oscar1.lima_romeo, romeo_three1kilo_oscar1.november_romeo, romeo_three1kilo_oscar1.yankee_papa, romeo_three1kilo_oscar1.four_sierra, romeo_three1kilo_oscar1.juliet_delta, romeo_three1kilo_oscar1.papa, romeo_three1kilo_oscar1.two_romeo, romeo_three1kilo_oscar1.yankee_tango, romeo_three1kilo_oscar1.quebec_xray, romeo_three1kilo_oscar1.whiskey_five, romeo_three1kilo_oscar1.delta_uniform_two, romeo_three1kilo_oscar1.seven_bravo
  • Recheck Cond: ((romeo_three1kilo_oscar1.lima_echo = 657) AND ((romeo_three1kilo_oscar1.golf_echo)::text = 'uniform_delta'::text))
  • Filter: (NOT romeo_three1kilo_oscar1.seven_seven)
  • Heap Blocks: exact=1,679
  • Buffers: shared hit=1,732
4. 0.066 0.719 ↓ 0.0 0 1

BitmapAnd (cost=66.71..66.71 rows=1 width=0) (actual time=0.718..0.719 rows=0 loops=1)

  • Buffers: shared hit=53
5. 0.297 0.297 ↓ 1.2 1,742 1

Bitmap Index Scan on three_india (cost=0.00..26.07 rows=1,511 width=0) (actual time=0.297..0.297 rows=1,742 loops=1)

  • Index Cond: (romeo_three1kilo_oscar1.lima_echo = 657)
  • Buffers: shared hit=12
6. 0.356 0.356 ↓ 1.2 1,742 1

Bitmap Index Scan on uniform_charlie (cost=0.00..40.39 rows=1,498 width=0) (actual time=0.356..0.356 rows=1,742 loops=1)

  • Index Cond: ((romeo_three1kilo_oscar1.golf_echo)::text = 'uniform_delta'::text)
  • Buffers: shared hit=41
7. 0.015 5.329 ↓ 3.0 3 1

Sort (cost=5.20..5.21 rows=1 width=2,188) (actual time=5.327..5.329 rows=3 loops=1)

  • Output: romeo_three1.juliet_delta, romeo_three1.oscar, romeo_three1.november_seven, romeo_three1.three_five, romeo_three1.foxtrot_bravo, romeo_three1.lima_romeo, romeo_three1.seven_seven, romeo_three2.juliet_delta, romeo_three2.three_zulu, romeo_three1.mike, romeo_three2.quebec_bravo, romeo_three3.charlie
  • Sort Key: romeo_three1.lima_romeo, romeo_three1.foxtrot_bravo DESC, romeo_three1.mike DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1,750
8. 0.006 5.314 ↓ 3.0 3 1

Nested Loop (cost=0.70..5.19 rows=1 width=2,188) (actual time=5.238..5.314 rows=3 loops=1)

  • Output: romeo_three1.juliet_delta, romeo_three1.oscar, romeo_three1.november_seven, romeo_three1.three_five, romeo_three1.foxtrot_bravo, romeo_three1.lima_romeo, romeo_three1.seven_seven, romeo_three2.juliet_delta, romeo_three2.three_zulu, romeo_three1.mike, romeo_three2.quebec_bravo, romeo_three3.charlie
  • Inner Unique: true
  • Buffers: shared hit=1,750
9. 0.006 5.275 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.42..2.68 rows=1 width=2,127) (actual time=5.205..5.275 rows=3 loops=1)

  • Output: romeo_three1.juliet_delta, romeo_three1.oscar, romeo_three1.november_seven, romeo_three1.three_five, romeo_three1.foxtrot_bravo, romeo_three1.lima_romeo, romeo_three1.seven_seven, romeo_three1.mike, romeo_three1.lima_echo, romeo_three3.charlie
  • Buffers: shared hit=1,741
10. 5.254 5.254 ↓ 3.0 3 1

CTE Scan on sierra_golf four_uniform (cost=0.00..0.03 rows=1 width=2,098) (actual time=5.190..5.254 rows=3 loops=1)

  • Output: romeo_three1.quebec_seven, romeo_three1.delta_uniform_lima, romeo_three1.three_five, romeo_three1.seven_seven, romeo_three1.november_seven, romeo_three1.mike, romeo_three1.foxtrot_bravo, romeo_three1.golf_echo, romeo_three1.zulu_alpha, romeo_three1.oscar, romeo_three1.victor_tango, romeo_three1.lima_echo, romeo_three1.juliet_hotel, romeo_three1.lima_romeo, romeo_three1.november_romeo, romeo_three1.yankee_papa, romeo_three1.four_sierra, romeo_three1.juliet_delta, romeo_three1.papa, romeo_three1.two_romeo, romeo_three1.yankee_tango, romeo_three1.quebec_xray, romeo_three1.whiskey_five, romeo_three1.delta_uniform_two, romeo_three1.seven_bravo
  • Filter: (((NOT romeo_three1.papa) OR (romeo_three1.papa five NULL)) AND (((romeo_three1.lima_romeo)::text = 'quebec_quebec'::text) OR ((romeo_three1.lima_romeo)::text = 'two_tango'::text) OR ((romeo_three1.lima_romeo)::text = 'golf_juliet'::text) OR ((romeo_three1.lima_romeo)::text = 'kilo_five'::text)))
  • Rows Removed by Filter: 1,673
  • Buffers: shared hit=1,732
11. 0.015 0.015 ↓ 0.0 0 3

Index Scan using whiskey_juliet on victor_uniform india_november (cost=0.42..2.64 rows=1 width=45) (actual time=0.005..0.005 rows=0 loops=3)

  • Output: romeo_three3.quebec_seven, romeo_three3.delta_uniform_lima, romeo_three3.sierra_foxtrot, romeo_three3.charlie
  • Index Cond: (romeo_three1.quebec_seven = romeo_three3.sierra_foxtrot)
  • Buffers: shared hit=9
12. 0.033 0.033 ↑ 1.0 1 3

Index Scan using sierra_delta on six foxtrot_juliet (cost=0.28..2.50 rows=1 width=85) (actual time=0.011..0.011 rows=1 loops=3)

  • Output: romeo_three2.quebec_seven, romeo_three2.delta_uniform_lima, romeo_three2.echo, romeo_three2.lima_seven, romeo_three2.romeo_kilo, romeo_three2.hotel, romeo_three2.lima_delta, romeo_three2.three_zulu, romeo_three2.juliet_delta, romeo_three2.quebec_bravo, romeo_three2.romeo_echo, romeo_three2.papa, romeo_three2.golf_romeo, romeo_three2.india_foxtrot, romeo_three2.uniform_five
  • Index Cond: (romeo_three2.quebec_seven = romeo_three1.lima_echo)
  • Filter: (romeo_three1.mike >= romeo_three2.echo)
  • Buffers: shared hit=9
Planning time : 0.350 ms
Execution time : 5.564 ms