explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MlnY : New

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 145.004 ↓ 0.0 0 1

Limit (cost=2,474.120..2,474.200 rows=1 width=63) (actual time=145.004..145.004 rows=0 loops=1)

  • Buffers: shared hit=2,991
2. 0.000 145.004 ↓ 0.0 0 1

Subquery Scan on november_charlie (cost=2,474.120..2,474.200 rows=1 width=63) (actual time=145.004..145.004 rows=0 loops=1)

  • Buffers: shared hit=2,991
3. 0.001 145.004 ↓ 0.0 0 1

Unique (cost=2,474.120..2,474.190 rows=1 width=1,889) (actual time=145.004..145.004 rows=0 loops=1)

  • Buffers: shared hit=2,991
4. 0.050 145.003 ↓ 0.0 0 1

Sort (cost=2,474.120..2,474.130 rows=1 width=1,889) (actual time=145.003..145.003 rows=0 loops=1)

  • Sort Key: ((((((kilo.two_india)::text || 'victor_two'::text) || (victor_xray.foxtrot_lima)::text) || 'charlie_charlie'::text) || (victor_xray.hotel_echo)::text)), victor_xray.foxtrot_lima, victor_xray.sierra_papa, kilo.juliet_victor, (CASE WHEN ((alpha_four(five_papa.zulu_hotel)) five_romeo NULL) THEN 'seven_golf_whiskey'::bigint ELSE (alpha_four(five_papa.zulu_hotel)) END), whiskey_november.quebec_six_bravo, (lima_sierra(lima_bravo.yankee, 'echo_whiskey'::bigint)), lima_bravo.november_juliet_charlie, lima_bravo.whiskey_oscar, (lima_sierra(lima_bravo.seven_kilo, 'india_xray'::character varying)), charlie_juliet.juliet_golf, kilo.november_golf, kilo.papa_juliet, kilo.delta_four, kilo.papa_delta, kilo.four_victor, (lima_sierra(kilo.hotel_bravo, 'india_xray'::character varying)), kilo.bravo_victor, oscar_mike1.echo_xray, (CASE WHEN (victor_six0.xray_tango five_romeo NULL) THEN 'delta_foxtrot'::character varying ELSE victor_six0.xray_tango END), (CASE hotel_papa.zulu_yankee WHEN 16 THEN 'alpha_papa'::text WHEN 17 THEN 'lima_echo'::text ELSE 'india_xray'::text END), (CASE oscar_mike1.uniform_kilo WHEN 'alpha_golf'::text THEN CASE seven_golf_victor.quebec_four WHEN 'uniform_five'::bpchar THEN 'victor_alpha'::text ELSE 'oscar_india'::text END ELSE 'two_seven'::text END), echo_kilo.mike_india, echo_kilo.bravo_xray
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,991
5. 0.000 144.953 ↓ 0.0 0 1

Nested Loop (cost=2,176.170..2,474.110 rows=1 width=1,889) (actual time=144.953..144.953 rows=0 loops=1)

  • Filter: (CASE hotel_papa.zulu_yankee WHEN 16 THEN 'alpha_papa'::text WHEN 17 THEN 'lima_echo'::text ELSE 'india_xray'::text END = ANY ('quebec_echo'::text[]))
  • Buffers: shared hit=2,991
6. 0.002 144.953 ↓ 0.0 0 1

Nested Loop (cost=2,175.880..2,465.760 rows=1 width=1,788) (actual time=144.952..144.953 rows=0 loops=1)

  • Buffers: shared hit=2,991
7. 1.093 144.951 ↓ 0.0 0 1

Nested Loop (cost=2,175.880..2,464.650 rows=1 width=1,570) (actual time=144.951..144.951 rows=0 loops=1)

  • Filter: (lima_sierra(lima_bravo.yankee, 'echo_whiskey'::bigint) = 'echo_whiskey'::bigint)
  • Buffers: shared hit=2,991
8. 0.178 143.102 ↓ 378.0 378 1

Nested Loop (cost=2,175.880..2,462.970 rows=1 width=1,210) (actual time=16.268..143.102 rows=378 loops=1)

  • Buffers: shared hit=2,613
9. 0.101 2.560 ↓ 63.0 63 1

Hash Join (cost=146.710..187.190 rows=1 width=1,080) (actual time=2.383..2.560 rows=63 loops=1)

  • Buffers: shared hit=77
10. 0.109 0.146 ↓ 3.9 63 1

Bitmap Heap Scan on four_sierra victor_xray (cost=17.880..58.290 rows=16 width=53) (actual time=0.056..0.146 rows=63 loops=1)

  • Filter: (victor_xray.golf_quebec_tango = 'seven_juliet'::numeric)
  • Heap Blocks: exact=3
  • Buffers: shared hit=14
11. 0.037 0.037 ↑ 1.0 63 1

Bitmap Index Scan on six_six (cost=0.000..17.880 rows=63 width=0) (actual time=0.037..0.037 rows=63 loops=1)

  • Index Cond: ((victor_xray.sierra_papa)::text = ANY ('mike_three'::text[]))
  • Buffers: shared hit=11
12. 0.046 2.313 ↓ 1.5 164 1

Hash (cost=127.420..127.420 rows=113 width=1,046) (actual time=2.313..2.313 rows=164 loops=1)

  • Buffers: shared hit=63
13. 0.013 2.267 ↓ 1.5 164 1

Subquery Scan on echo_kilo (cost=122.050..127.420 rows=113 width=1,046) (actual time=1.257..2.267 rows=164 loops=1)

  • Buffers: shared hit=63
14. 1.014 2.254 ↓ 1.5 164 1

Aggregate (cost=122.050..126.290 rows=113 width=1,601) (actual time=1.256..2.254 rows=164 loops=1)

  • Filter: (((hotel_kilo((india_whiskey.bravo_xray)::text, 'sierra_uniform'::text ORDER mike_tango (india_whiskey.bravo_xray)::text))::character varying(4000))::text ~~ 'six_yankee'::text)
  • Buffers: shared hit=63
15. 0.226 1.240 ↓ 3.9 441 1

Sort (cost=122.050..122.330 rows=113 width=97) (actual time=1.205..1.240 rows=441 loops=1)

  • Sort Key: oscar_delta.hotel_echo
  • Sort Method: quicksort Memory: 88kB
  • Buffers: shared hit=63
16. 0.111 1.014 ↓ 3.9 441 1

Hash Join (cost=54.050..118.200 rows=113 width=97) (actual time=0.466..1.014 rows=441 loops=1)

  • Buffers: shared hit=63
17. 0.153 0.847 ↓ 3.9 441 1

Hash Join (cost=50.190..112.780 rows=113 width=60) (actual time=0.395..0.847 rows=441 loops=1)

  • Buffers: shared hit=61
18. 0.328 0.328 ↑ 1.0 442 1

Seq Scan on four_sierra oscar_delta (cost=0.000..59.250 rows=442 width=53) (actual time=0.015..0.328 rows=442 loops=1)

  • Filter: (oscar_delta.golf_quebec_tango = 'seven_juliet'::numeric)
  • Buffers: shared hit=38
19. 0.119 0.366 ↑ 1.0 441 1

Hash (cost=44.670..44.670 rows=441 width=21) (actual time=0.366..0.366 rows=441 loops=1)

  • Buffers: shared hit=23
20. 0.247 0.247 ↑ 1.0 441 1

Seq Scan on uniform_papa india_yankee (cost=0.000..44.670 rows=441 width=21) (actual time=0.045..0.247 rows=441 loops=1)

  • Filter: (india_yankee.golf_quebec_tango = 'seven_juliet'::numeric)
  • Buffers: shared hit=23
21. 0.040 0.056 ↑ 1.0 83 1

Hash (cost=2.830..2.830 rows=83 width=51) (actual time=0.056..0.056 rows=83 loops=1)

  • Buffers: shared hit=2
22. 0.016 0.016 ↑ 1.0 83 1

Seq Scan on two_xray india_whiskey (cost=0.000..2.830 rows=83 width=51) (actual time=0.008..0.016 rows=83 loops=1)

  • Buffers: shared hit=2
23. 0.504 140.364 ↓ 6.0 6 63

Nested Loop (cost=2,029.170..2,275.770 rows=1 width=130) (actual time=0.633..2.228 rows=6 loops=63)

  • Filter: (CASE oscar_mike1.uniform_kilo WHEN 'alpha_golf'::text THEN CASE seven_golf_victor.quebec_four WHEN 'uniform_five'::bpchar THEN 'victor_alpha'::text ELSE 'oscar_india'::text END ELSE 'two_seven'::text END = ANY ('victor_zulu'::text[]))
  • Buffers: shared hit=2,536
24. 1.890 139.482 ↓ 6.0 6 63

Nested Loop (cost=2,028.890..2,275.450 rows=1 width=108) (actual time=0.628..2.214 rows=6 loops=63)

  • Buffers: shared hit=1,402
25. 0.126 136.458 ↓ 6.0 6 63

Nested Loop (cost=2,028.890..2,273.300 rows=1 width=100) (actual time=0.621..2.166 rows=6 loops=63)

  • Buffers: shared hit=1,024
26. 42.945 135.954 ↑ 1.0 1 63

Hash Join (cost=2,028.610..2,261.040 rows=1 width=92) (actual time=0.616..2.158 rows=1 loops=63)

  • Buffers: shared hit=772
27. 82.919 85.428 ↓ 1.2 11,802 63

Aggregate (cost=479.440..577.300 rows=9,786 width=16) (actual time=0.089..1.356 rows=11,802 loops=63)

  • Buffers: shared hit=235
28. 2.509 2.509 ↓ 1.0 11,802 1

Seq Scan on five_papa five_papa (cost=0.000..423.020 rows=11,283 width=16) (actual time=0.010..2.509 rows=11,802 loops=1)

  • Filter: ((five_papa.lima_romeo five_romeo NULL) AND (five_papa.foxtrot_bravo = 0))
  • Buffers: shared hit=235
29. 0.006 7.581 ↑ 1.0 1 1

Hash (cost=1,549.160..1,549.160 rows=1 width=84) (actual time=7.580..7.581 rows=1 loops=1)

  • Buffers: shared hit=537
30. 0.009 7.575 ↑ 1.0 1 1

Merge Join (cost=1,193.040..1,549.160 rows=1 width=84) (actual time=7.573..7.575 rows=1 loops=1)

  • Buffers: shared hit=537
31. 0.162 7.547 ↓ 2.5 146 1

Merge Join (cost=1,184.730..2,935.420 rows=59 width=44) (actual time=6.725..7.547 rows=146 loops=1)

  • Filter: ((CASE WHEN (victor_six0.xray_tango five_romeo NULL) THEN 'delta_foxtrot'::character varying ELSE victor_six0.xray_tango END)::text = 'delta_foxtrot'::text)
  • Buffers: shared hit=534
32. 0.345 0.345 ↑ 26.8 439 1

Index Scan using echo_two on tango juliet_juliet (cost=0.290..1,344.610 rows=11,762 width=12) (actual time=0.013..0.345 rows=439 loops=1)

  • Index Cond: (oscar_mike1.foxtrot_bravo = 0)
  • Filter: ((oscar_mike1.echo_xray)::text <> 'delta_sierra'::text)
  • Buffers: shared hit=311
33. 0.054 7.040 ↓ 7.6 439 1

Materialize (cost=1,184.440..1,560.260 rows=58 width=10) (actual time=6.665..7.040 rows=439 loops=1)

  • Buffers: shared hit=223
34. 0.055 6.986 ↓ 7.6 439 1

Subquery Scan on november_november (cost=1,184.440..1,560.110 rows=58 width=10) (actual time=6.664..6.986 rows=439 loops=1)

  • Filter: (victor_six0.xray_romeo = 1)
  • Buffers: shared hit=223
35. 0.181 6.931 ↑ 26.3 439 1

WindowAgg (cost=1,184.440..1,415.620 rows=11,559 width=26) (actual time=6.659..6.931 rows=439 loops=1)

  • Buffers: shared hit=223
36. 3.659 6.750 ↑ 26.3 439 1

Sort (cost=1,184.440..1,213.340 rows=11,559 width=18) (actual time=6.652..6.750 rows=439 loops=1)

  • Sort Key: quebec_six_foxtrot.juliet_victor, quebec_six_foxtrot.bravo_lima
  • Sort Method: quicksort Memory: 1,309kB
  • Buffers: shared hit=223
37. 3.091 3.091 ↓ 1.0 11,838 1

Seq Scan on quebec_six_foxtrot quebec_six_foxtrot (cost=0.000..404.400 rows=11,559 width=18) (actual time=0.017..3.091 rows=11,838 loops=1)

  • Filter: ((quebec_six_foxtrot.hotel_six five_romeo NULL) AND (quebec_six_foxtrot.foxtrot_bravo = 0))
  • Buffers: shared hit=223
38. 0.005 0.019 ↑ 1.0 1 1

Sort (cost=8.310..8.320 rows=1 width=40) (actual time=0.019..0.019 rows=1 loops=1)

  • Sort Key: kilo.juliet_victor
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
39. 0.014 0.014 ↑ 1.0 1 1

Index Scan using six_zulu on hotel_charlie kilo (cost=0.280..8.300 rows=1 width=40) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: ((kilo.two_india = 'golf_lima'::bigint) AND (kilo.foxtrot_bravo = 0))
  • Filter: ((kilo.quebec_xray_uniform_charlie)::text = 'delta_foxtrot'::text)
  • Buffers: shared hit=3
40. 0.378 0.378 ↓ 2.0 6 63

Index Only Scan using alpha_hotel on foxtrot_echo whiskey_november (cost=0.280..12.220 rows=3 width=16) (actual time=0.005..0.006 rows=6 loops=63)

  • Index Cond: ((whiskey_november.two_india = 'golf_lima'::bigint) AND (whiskey_november.foxtrot_bravo = 0))
  • Heap Fetches: 378
  • Buffers: shared hit=252
41. 1.134 1.134 ↑ 1.0 51 378

Seq Scan on november_juliet_alpha quebec_sierra (cost=0.000..1.510 rows=51 width=16) (actual time=0.001..0.003 rows=51 loops=378)

  • Buffers: shared hit=378
42. 0.378 0.378 ↑ 1.0 1 378

Index Scan using quebec_xray_uniform_six on four_six seven_golf_victor (cost=0.280..0.310 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=378)

  • Index Cond: ((seven_golf_victor.juliet_victor = oscar_mike1.juliet_victor) AND (seven_golf_victor.foxtrot_bravo = 0))
  • Buffers: shared hit=1,134
43. 0.756 0.756 ↑ 1.0 27 378

Seq Scan on hotel_romeo lima_bravo (cost=0.000..1.270 rows=27 width=368) (actual time=0.001..0.002 rows=27 loops=378)

  • Buffers: shared hit=378
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on six_whiskey charlie_juliet (cost=0.000..1.050 rows=5 width=242) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using five_golf on bravo_zulu hotel_papa (cost=0.290..8.310 rows=1 width=16) (never executed)

  • Index Cond: ((hotel_papa.two_india = 'golf_lima'::bigint) AND (hotel_papa.foxtrot_bravo = 0) AND (hotel_papa.golf_quebec_romeo = 'alpha_mike'::numeric))
Planning time : 6.282 ms
Execution time : 145.663 ms