explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EyrT : Optimization for: old_query; plan #sV1M

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.063 214.545 ↓ 2.0 2 1

GroupAggregate (cost=9,414.02..9,414.09 rows=1 width=129) (actual time=214.524..214.545 rows=2 loops=1)

  • Group Key: bravo_tango_oscar.kilo_romeo, ((((('zulu_papa'::text || (juliet_kilo.kilo_oscar)::text) || 'three_tango'::text) || (juliet_kilo.quebec_seven)::text) || ''yankee_four'1345'::bigint ELSE NULL::bigint END), juliet_kilo.delta_juliet, (CASE WHEN ((echo_victor.six_four = 4) OR (delta_yankee.six_four = 4)) THEN four_bravo.charlie_five ELSE NULL::bigint END), (CASE WHEN (four_bravo.xray five_romeo NULL) THEN uniform_alpha.bravo_six WHEN (uniform_alpha.bravo_six = six_whiskey.whiskey_tango) THEN four_foxtrot.bravo_six ELSE uniform_alpha.bravo_six END)
  • Buffers: shared hit=1,094 read=6,926
2. 0.051 214.482 ↓ 10.0 10 1

Sort (cost=9,414.02..9,414.02 rows=1 width=142) (actual time=214.480..214.482 rows=10 loops=1)

  • Sort Key: bravo_tango_oscar.kilo_romeo, ((((('zulu_papa'::text || (juliet_kilo.kilo_oscar)::text) || 'three_tango'::text) || (juliet_kilo.quebec_seven)::text) || ''yankee_four'1345'::bigint ELSE NULL::bigint END), juliet_kilo.delta_juliet, (CASE WHEN ((echo_victor.six_four = 4) OR (delta_yankee.six_four = 4)) THEN four_bravo.charlie_five ELSE NULL::bigint END), (CASE WHEN (four_bravo.xray five_romeo NULL) THEN uniform_alpha.bravo_six WHEN (uniform_alpha.bravo_six = six_whiskey.whiskey_tango) THEN four_foxtrot.bravo_six ELSE uniform_alpha.bravo_six END)
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=1,094 read=6,926
3. 0.049 214.431 ↓ 10.0 10 1

Nested Loop (cost=118.33..9,414.01 rows=1 width=142) (actual time=210.315..214.431 rows=10 loops=1)

  • Buffers: shared hit=1,094 read=6,926
4. 0.032 214.322 ↓ 10.0 10 1

Nested Loop (cost=118.05..9,409.74 rows=1 width=214) (actual time=210.284..214.322 rows=10 loops=1)

  • Buffers: shared hit=1,073 read=6,926
5. 0.071 214.240 ↓ 10.0 10 1

Nested Loop (cost=117.76..9,406.95 rows=1 width=214) (actual time=210.270..214.240 rows=10 loops=1)

  • Filter: ((yankee_yankee.four_zulu five_romeo NULL) OR ((yankee_yankee.juliet_oscar)::text !~~* 'three_sierra'::text))
  • Buffers: shared hit=1,052 read=6,926
6. 0.022 214.119 ↓ 10.0 10 1

Nested Loop (cost=117.49..9,404.52 rows=1 width=222) (actual time=210.230..214.119 rows=10 loops=1)

  • Buffers: shared hit=1,022 read=6,926
7. 0.036 213.547 ↓ 10.0 10 1

Nested Loop (cost=117.21..9,399.96 rows=1 width=230) (actual time=209.936..213.547 rows=10 loops=1)

  • Filter: ((oscar_victor.charlie_papa five_romeo NULL) OR (oscar_victor.mike_four = 8,085))
  • Buffers: shared hit=994 read=6,924
8. 2.234 213.441 ↓ 10.0 10 1

Nested Loop (cost=116.79..9,396.71 rows=1 width=238) (actual time=209.911..213.441 rows=10 loops=1)

  • Buffers: shared hit=954 read=6,924
9. 0.028 211.137 ↓ 10.0 10 1

Nested Loop (cost=97.61..9,375.52 rows=1 width=234) (actual time=209.600..211.137 rows=10 loops=1)

  • Buffers: shared hit=854 read=6,924
10. 0.030 211.069 ↓ 10.0 10 1

Nested Loop (cost=97.33..9,374.42 rows=1 width=242) (actual time=209.577..211.069 rows=10 loops=1)

  • Buffers: shared hit=833 read=6,924
11. 0.022 210.959 ↓ 10.0 10 1

Nested Loop (cost=97.04..9,371.74 rows=1 width=226) (actual time=209.527..210.959 rows=10 loops=1)

  • Buffers: shared hit=803 read=6,924
12. 0.025 210.677 ↓ 10.0 10 1

Nested Loop (cost=96.76..9,370.64 rows=1 width=226) (actual time=209.297..210.677 rows=10 loops=1)

  • Buffers: shared hit=783 read=6,923
13. 0.027 210.372 ↓ 10.0 10 1

Nested Loop (cost=96.48..9,368.36 rows=1 width=222) (actual time=209.057..210.372 rows=10 loops=1)

  • Buffers: shared hit=754 read=6,922
14. 0.032 210.285 ↓ 10.0 10 1

Nested Loop (cost=96.19..9,365.67 rows=1 width=206) (actual time=209.035..210.285 rows=10 loops=1)

  • Buffers: shared hit=724 read=6,922
15. 0.027 210.203 ↓ 10.0 10 1

Nested Loop (cost=96.07..9,365.52 rows=1 width=198) (actual time=209.011..210.203 rows=10 loops=1)

  • Buffers: shared hit=704 read=6,922
16. 0.044 208.926 ↓ 10.0 10 1

Nested Loop (cost=95.78..9,363.47 rows=1 width=206) (actual time=208.285..208.926 rows=10 loops=1)

  • Buffers: shared hit=679 read=6,917
17. 0.056 208.752 ↓ 10.0 10 1

Nested Loop (cost=95.35..9,356.34 rows=1 width=205) (actual time=208.271..208.752 rows=10 loops=1)

  • Buffers: shared hit=656 read=6,912
18. 0.023 208.492 ↓ 2.0 2 1

Nested Loop (cost=94.93..9,316.69 rows=1 width=103) (actual time=208.215..208.492 rows=2 loops=1)

  • Buffers: shared hit=649 read=6,905
19. 206.901 206.901 ↓ 2.0 2 1

Seq Scan on mike_kilo bravo_tango_oscar (cost=0.00..9,213.74 rows=1 width=16) (actual time=206.846..206.901 rows=2 loops=1)

  • Filter: ((bravo_tango_oscar.kilo_romeo >= 'november_echo'::timestamp without time zone) AND (lima(bravo_tango_oscar.hotel_sierra, 0) <> november_november ('india_lima'::integer[])))
  • Buffers: shared hit=643 read=6,899
20. 0.504 1.568 ↑ 1.0 1 2

Index Scan using three_victor on foxtrot_romeo juliet_kilo (cost=94.93..102.95 rows=1 width=87) (actual time=0.784..0.784 rows=1 loops=2)

  • Index Cond: (juliet_kilo.romeo_three = bravo_tango_oscar.papa)
  • Filter: ((NOT (mike_mike delta_four 2)) AND (juliet_kilo.juliet_charlie = 0) AND (juliet_kilo.three_november = 1))
  • Buffers: shared hit=6 read=6
21.          

SubPlan (for Index Scan)

22. 0.001 1.064 ↓ 0.0 0 1

Unique (cost=94.49..94.50 rows=1 width=8) (actual time=1.064..1.064 rows=0 loops=1)

  • Buffers: shared read=4
23. 0.025 1.063 ↓ 0.0 0 1

Sort (cost=94.49..94.50 rows=1 width=8) (actual time=1.063..1.063 rows=0 loops=1)

  • Sort Key: bravo_tango_india.papa
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=4
24. 0.002 1.038 ↓ 0.0 0 1

Nested Loop (cost=4.77..94.48 rows=1 width=8) (actual time=1.038..1.038 rows=0 loops=1)

  • Buffers: shared read=4
25. 0.040 1.036 ↓ 0.0 0 1

Hash Join (cost=4.48..93.92 rows=1 width=16) (actual time=1.036..1.036 rows=0 loops=1)

  • Buffers: shared read=4
26. 0.267 0.267 ↑ 4,708.0 1 1

Seq Scan on india_seven bravo_tango_india (cost=0.00..77.08 rows=4,708 width=16) (actual time=0.267..0.267 rows=1 loops=1)

  • Buffers: shared read=1
27. 0.002 0.729 ↓ 0.0 0 1

Hash (cost=4.47..4.47 rows=1 width=8) (actual time=0.728..0.729 rows=0 loops=1)

  • Buffers: shared read=3
28. 0.727 0.727 ↓ 0.0 0 1

Index Scan using kilo_delta on foxtrot_romeo kilo_uniform (cost=0.42..4.47 rows=1 width=8) (actual time=0.727..0.727 rows=0 loops=1)

  • Index Cond: (two_papa1.november_hotel = 8,092)
  • Buffers: shared read=3
29. 0.000 0.000 ↓ 0.0 0

Index Scan using seven on echo_romeo oscar_oscar (cost=0.29..0.56 rows=1 width=8) (never executed)

  • Index Cond: (oscar_oscar.mike_quebec = bravo_tango_india.echo_alpha)
  • Filter: ((NOT oscar_oscar.uniform_charlie) AND (oscar_oscar.juliet_charlie <> 999))
30. 0.204 0.204 ↓ 5.0 5 2

Index Scan using juliet_whiskey on tango_lima four_bravo (cost=0.43..39.64 rows=1 width=126) (actual time=0.042..0.102 rows=5 loops=2)

  • Index Cond: (four_bravo.papa = juliet_kilo.romeo_three)
  • Filter: ((four_bravo.golf_romeo_three five_romeo NOT NULL) AND (four_bravo.juliet_charlie = 0))
  • Buffers: shared hit=7 read=7
31. 0.130 0.130 ↑ 1.0 1 10

Index Scan using foxtrot_whiskey on tango_lima three_juliet (cost=0.43..7.12 rows=1 width=9) (actual time=0.013..0.013 rows=1 loops=10)

  • Index Cond: (four_bravo.xray = three_juliet.kilo_golf)
  • Buffers: shared hit=23 read=5
32. 1.250 1.250 ↑ 1.0 1 10

Index Only Scan using november_seven on zulu_tango kilo_foxtrot (cost=0.29..2.05 rows=1 width=8) (actual time=0.125..0.125 rows=1 loops=10)

  • Index Cond: (kilo_foxtrot.juliet_uniform = four_bravo.golf_hotel)
  • Heap Fetches: 9
  • Buffers: shared hit=25 read=5
33. 0.050 0.050 ↑ 1.0 1 10

Index Scan using quebec_november on tango_romeo six_whiskey (cost=0.13..0.15 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (six_whiskey.november_xray = juliet_kilo.delta_juliet)
  • Buffers: shared hit=20
34. 0.060 0.060 ↑ 1.0 1 10

Index Scan using tango_zulu on kilo_whiskey uniform_alpha (cost=0.29..2.69 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (uniform_alpha.bravo_six = juliet_kilo.kilo_five)
  • Buffers: shared hit=30
35. 0.280 0.280 ↑ 1.0 1 10

Index Scan using hotel_uniform on charlie_delta echo_victor (cost=0.28..2.28 rows=1 width=12) (actual time=0.028..0.028 rows=1 loops=10)

  • Index Cond: (uniform_alpha.romeo_india = echo_victor.charlie_lima)
  • Buffers: shared hit=29 read=1
36. 0.260 0.260 ↑ 1.0 1 10

Index Only Scan using hotel_uniform on charlie_delta six_sierra (cost=0.28..1.10 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=10)

  • Index Cond: (six_sierra.charlie_lima = uniform_alpha.three_zulu)
  • Buffers: shared hit=20 read=1
37. 0.080 0.080 ↑ 1.0 1 10

Index Scan using tango_zulu on kilo_whiskey four_foxtrot (cost=0.29..2.69 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=10)

  • Index Cond: (four_foxtrot.bravo_six = juliet_kilo.golf_victor)
  • Buffers: shared hit=30
38. 0.040 0.040 ↑ 1.0 1 10

Index Only Scan using hotel_uniform on charlie_delta victor (cost=0.28..1.10 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (victor.charlie_lima = four_foxtrot.romeo_india)
  • Buffers: shared hit=21
39. 0.000 0.070 ↑ 1.0 1 10

Index Scan using hotel_uniform on charlie_delta delta_yankee (cost=19.18..21.18 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (delta_yankee.charlie_lima = (delta_four 1))
  • Buffers: shared hit=30
40.          

SubPlan (for Index Scan)

41. 0.030 2.180 ↑ 1.0 1 10

Limit (cost=18.89..18.90 rows=1 width=16) (actual time=0.217..0.218 rows=1 loops=10)

  • Buffers: shared hit=70
42. 0.130 2.150 ↑ 1.0 1 10

Sort (cost=18.89..18.90 rows=1 width=16) (actual time=0.215..0.215 rows=1 loops=10)

  • Sort Key: golf_romeo_six.tango_mike
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=70
43. 2.020 2.020 ↑ 1.0 1 10

Seq Scan on oscar_echo golf_romeo_six (cost=0.00..18.88 rows=1 width=16) (actual time=0.147..0.202 rows=1 loops=10)

  • Filter: (((golf_romeo_six.tango_mike five_romeo NULL) OR (golf_romeo_six.tango_mike = four_bravo.charlie_five)) AND (golf_romeo_six.juliet_charlie = 0) AND (golf_romeo_six.whiskey_romeo = four_bravo.india_two))
  • Buffers: shared hit=70
44. 0.070 0.070 ↑ 1.0 1 10

Index Scan using charlie_sierra on foxtrot_yankee oscar_victor (cost=0.42..3.24 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (juliet_kilo.romeo_golf = oscar_victor.charlie_papa)
  • Buffers: shared hit=40
45. 0.550 0.550 ↓ 0.0 0 10

Index Scan using quebec_yankee on delta_sierra delta_sierra (cost=0.28..2.42 rows=1 width=8) (actual time=0.055..0.055 rows=0 loops=10)

  • Index Cond: (delta_sierra.four_zulu = lima(four_bravo.sierra_alpha, four_bravo.oscar_romeo))
  • Filter: lima(delta_sierra.zulu_victor, false)
  • Buffers: shared hit=28 read=2
46. 0.050 0.050 ↑ 1.0 1 10

Index Scan using quebec_yankee on delta_sierra yankee_yankee (cost=0.28..2.42 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (yankee_yankee.four_zulu = four_bravo.sierra_alpha)
  • Buffers: shared hit=30
47. 0.050 0.050 ↑ 1.0 1 10

Index Scan using hotel_yankee on quebec_three bravo_romeo (cost=0.29..2.79 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (bravo_romeo.india_juliet = four_bravo.echo_tango)
  • Buffers: shared hit=21
48. 0.060 0.060 ↓ 0.0 0 10

Index Scan using juliet_romeo on two_tango five_five (cost=0.28..2.26 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=10)

  • Index Cond: (five_five.charlie_seven = bravo_romeo.uniform_india)
  • Filter: (NOT five_five.sierra_november)
  • Buffers: shared hit=21
Planning time : 25.067 ms
Execution time : 215.265 ms