explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LCz7

Settings
# exclusive inclusive rows x rows loops node
1. 20.130 1,634,371.472 ↓ 3,890.7 101,158 1

Unique (cost=587,043.440..587,043.700 rows=26 width=53) (actual time=1,634,346.706..1,634,371.472 rows=101,158 loops=1)

  • Functions: 105
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 19.926 ms, Inlining 83.154 ms, Optimization 694.906 ms, Emission 416.309 ms, Total 1214.294 ms
2. 110.961 1,634,351.342 ↓ 3,890.9 101,163 1

Sort (cost=587,043.440..587,043.500 rows=26 width=53) (actual time=1,634,346.705..1,634,351.342 rows=101,163 loops=1)

  • Sort Key: echo_lima.uniform_uniform, whiskey_hotel.lima_yankee, (((golf_five((whiskey_hotel.kilo_quebec * alpha_oscar.kilo_quebec)) * golf_five(alpha_quebec.kilo_quebec)) * echo_lima.kilo_quebec))
  • Sort Method: quicksort Memory: 10976kB
3. 2,528.433 1,634,240.381 ↓ 3,890.9 101,163 1

Nested Loop Semi Join (cost=94.740..587,042.830 rows=26 width=53) (actual time=1,198.323..1,634,240.381 rows=101,163 loops=1)

  • Join Filter: ((juliet_four.alpha_alpha = bravo_six.mike_five) AND ((quebec1.uniform_uniform = lima_sierra(echo_lima.two, (CASE WHEN (echo_lima.bravo_sierra_uniform = 'india'::text) THEN 500374 ELSE NULL::integer END)::bigint)) OR ((lima_sierra(echo_lima.two, (CASE WHEN (echo_lima.bravo_sierra_uniform = 'india'::text) THEN 500374 ELSE NULL::integer END)::bigint) five_romeo NULL) AND (bravo_six.four_foxtrot <> 'four_uniform_quebec'::double precision))))
  • Rows Removed by Join Filter: 27823808
4. 128.331 1,630,686.558 ↓ 967.3 102,539 1

Nested Loop Semi Join (cost=6.770..584,398.860 rows=106 width=99) (actual time=1,197.191..1,630,686.558 rows=102,539 loops=1)

  • Join Filter: ((NOT (alpha_bravo.alpha_kilo five_romeo DISTINCT FROM lima_delta2five_hotel.alpha_kilo)) AND (lima_delta2five_hotel.sierra_five = alpha_bravo.sierra_five))
  • Rows Removed by Join Filter: 1627478
5. 28.332 1,630,455.688 ↓ 826.9 102,539 1

Nested Loop Semi Join (cost=6.770..584,314.920 rows=124 width=105) (actual time=1,197.171..1,630,455.688 rows=102,539 loops=1)

6. 5,492.579 1,629,904.736 ↓ 607.7 104,524 1

Nested Loop (cost=6.500..584,257.730 rows=172 width=125) (actual time=1,197.136..1,629,904.736 rows=104,524 loops=1)

  • Join Filter: (NOT romeo_sierra_xray(((golf_five((whiskey_hotel.kilo_quebec * alpha_oscar.kilo_quebec)) * golf_five(alpha_quebec.kilo_quebec)) * echo_lima.kilo_quebec)))
  • Rows Removed by Join Filter: 548498
7. 3,630.080 1,611,449.565 ↓ 1,920.4 480,096 1

Nested Loop (cost=6.220..583,920.770 rows=250 width=109) (actual time=1,196.787..1,611,449.565 rows=480,096 loops=1)

  • Join Filter: (lima_delta2five_hotel.bravo_sierra_uniform = whiskey_hotel."golf_papa")
  • Rows Removed by Join Filter: 3817008
8. 5,208.112 96,843.820 ↓ 224.5 8,836,115 1

Nested Loop (cost=5.800..560,945.160 rows=39,353 width=81) (actual time=1,196.644..96,843.820 rows=8,836,115 loops=1)

9. 817.677 14,325.828 ↓ 219.6 6,442,490 1

Nested Loop (cost=5.390..543,354.140 rows=29,334 width=62) (actual time=1,196.288..14,325.828 rows=6,442,490 loops=1)

10. 680.778 5,154.015 ↓ 239.4 397,816 1

Hash Left Join (cost=4.830..541,323.700 rows=1,662 width=55) (actual time=1,196.246..5,154.015 rows=397,816 loops=1)

  • Hash Cond: ((lima_delta2five_hotel.sierra_five = whiskey_five.sierra_five) AND (lima_delta2five_hotel.alpha_kilo = whiskey_five.alpha_kilo))
  • Filter: (((((('four_victor'::text || delta_foxtrot_alpha.charlie_lima) || 'mike_whiskey'::text) || whiskey_five.alpha_kilo) five_romeo NULL) OR (echo_lima.seven ~ ((('four_victor'::text || delta_foxtrot_alpha.charlie_lima) || 'mike_whiskey'::text) || whiskey_five.alpha_kilo))) AND ((((('four_victor'::text || delta_foxtrot_alpha.lima_quebec) || 'mike_whiskey'::text) || whiskey_five.alpha_kilo) five_romeo NULL) OR (echo_lima.seven !~ ((('four_victor'::text || delta_foxtrot_alpha.lima_quebec) || 'mike_whiskey'::text) || whiskey_five.alpha_kilo))))
  • Rows Removed by Filter: 39875
11. 829.440 4,473.194 ↓ 2.6 437,691 1

Hash Join (cost=1.940..540,007.750 rows=167,426 width=65) (actual time=1,195.813..4,473.194 rows=437,691 loops=1)

  • Hash Cond: (echo_lima.bravo_sierra_uniform = (lima_delta2five_hotel.sierra_five)::text)
  • Join Filter: ((lima_delta2five_hotel.alpha_kilo five_romeo NULL) OR (echo_lima.seven ~~ juliet_golf('juliet_delta', lima_delta2five_hotel.alpha_kilo)))
  • Rows Removed by Join Filter: 271815
12. 3,643.716 3,643.716 ↑ 3.2 3,600,100 1

Seq Scan on juliet_uniform echo_lima (cost=0.000..466,041.310 rows=11,379,362 width=56) (actual time=1,195.588..3,643.716 rows=3,600,100 loops=1)

  • Filter: (CASE WHEN ((whiskey_three = 'victor'::text) AND (bravo_sierra_uniform = 'bravo_november'::text)) THEN 'yankee_uniform'::text WHEN ((whiskey_three = 'victor'::text) AND (bravo_sierra_uniform = ANY ('sierra_zulu'::text[]))) THEN 'charlie_sierra'::text ELSE whiskey_three END <> 'victor'::text)
  • Rows Removed by Filter: 7835431
13. 0.015 0.038 ↑ 1.0 42 1

Hash (cost=1.420..1.420 rows=42 width=9) (actual time=0.037..0.038 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.023 0.023 ↑ 1.0 42 1

Seq Scan on tango echo_juliet (cost=0.000..1.420 rows=42 width=9) (actual time=0.019..0.023 rows=42 loops=1)

15. 0.006 0.043 ↑ 1.1 10 1

Hash (cost=2.720..2.720 rows=11 width=34) (actual time=0.043..0.043 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.014 0.037 ↑ 1.1 10 1

Hash Join (cost=1.230..2.720 rows=11 width=34) (actual time=0.031..0.037 rows=10 loops=1)

  • Hash Cond: (whiskey_five.alpha_kilo = delta_foxtrot_alpha.alpha_kilo)
17. 0.008 0.008 ↑ 1.0 38 1

Seq Scan on whiskey_five (cost=0.000..1.380 rows=38 width=6) (actual time=0.006..0.008 rows=38 loops=1)

18. 0.005 0.015 ↑ 1.0 10 1

Hash (cost=1.100..1.100 rows=10 width=31) (actual time=0.014..0.015 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.010 0.010 ↑ 1.0 10 1

Seq Scan on delta_foxtrot_alpha (cost=0.000..1.100 rows=10 width=31) (actual time=0.009..0.010 rows=10 loops=1)

20. 8,354.136 8,354.136 ↑ 1.1 16 397,816

Index Scan using oscar_four on kilo_tango juliet_four (cost=0.560..1.040 rows=18 width=17) (actual time=0.009..0.021 rows=16 loops=397,816)

  • Index Cond: (("six" = 'three_mike'::text) AND ("uniform_victor" = CASE WHEN (echo_lima.bravo_sierra_uniform = ANY ('uniform_golf'::text[])) THEN ('delta_foxtrot_golf'::text || echo_lima.yankee_lima) WHEN (echo_lima.bravo_sierra_uniform = ANY ('uniform_three'::text[])) THEN (echo_lima.yankee_lima || 'bravo_sierra_foxtrot'::text) ELSE echo_lima.yankee_lima END))
21. 77,309.880 77,309.880 ↑ 4.0 1 6,442,490

Index Only Scan using four_sierra on foxtrot_papa alpha_oscar (cost=0.410..0.560 rows=4 width=35) (actual time=0.012..0.012 rows=1 loops=6,442,490)

  • Index Cond: (charlie_zulu = juliet_four.charlie_zulu)
  • Heap Fetches: 3061725
22. 1,510,975.665 1,510,975.665 ↓ 0.0 0 8,836,115

Index Scan using hotel on oscar_lima whiskey_hotel (cost=0.410..0.560 rows=2 width=47) (actual time=0.108..0.171 rows=0 loops=8,836,115)

  • Index Cond: ((lima_yankee)::bpchar = (alpha_oscar.lima_yankee)::bpchar)
  • Filter: ("golf_mike" = 'bravo_foxtrot'::text)
  • Rows Removed by Filter: 0
23. 12,962.592 12,962.592 ↑ 1.0 1 480,096

Index Scan using charlie_alpha on four_uniform_yankee alpha_quebec (cost=0.280..0.830 rows=1 width=32) (actual time=0.019..0.027 rows=1 loops=480,096)

  • Index Cond: (uniform_uniform = echo_lima.november_four)
  • Filter: (NOT romeo_sierra_xray(golf_five(kilo_quebec)))
  • Rows Removed by Filter: 0
24. 203.101 522.620 ↑ 1.0 1 104,524

Nested Loop (cost=0.280..0.320 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=104,524)

  • Join Filter: (juliet_bravo.lima_tango = lima_two.lima_tango)
  • Rows Removed by Join Filter: 0
25. 209.048 209.048 ↑ 1.0 1 104,524

Index Only Scan using four_india on romeo_sierra_hotel juliet_bravo (cost=0.140..0.160 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=104,524)

  • Index Cond: (three_five2 = whiskey_hotel."lima_juliet")
  • Heap Fetches: 0
26. 110.471 110.471 ↑ 1.0 1 110,471

Index Scan using foxtrot_india on romeo_papa lima_two (cost=0.140..0.160 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=110,471)

  • Index Cond: (uniform_uniform = alpha_quebec.delta_quebec)
27. 102.516 102.539 ↑ 2.2 17 102,539

Materialize (cost=0.000..1.570 rows=38 width=6) (actual time=0.000..0.001 rows=17 loops=102,539)

28. 0.023 0.023 ↑ 1.0 38 1

Seq Scan on whiskey_five alpha_bravo (cost=0.000..1.380 rows=38 width=6) (actual time=0.008..0.023 rows=38 loops=1)

29. 1,024.226 1,025.390 ↑ 2.9 272 102,539

Materialize (cost=87.970..111.010 rows=793 width=32) (actual time=0.000..0.010 rows=272 loops=102,539)

30. 0.228 1.164 ↑ 1.7 475 1

Merge Join (cost=87.970..107.050 rows=793 width=32) (actual time=0.888..1.164 rows=475 loops=1)

  • Merge Cond: (((bravo_six.bravo_bravo)::text) = (lima_sierra(quebec1.four_golf4217, quebec2.four_golf4217, CASE WHEN (quebec1.uniform_uniform = 500544) THEN 'whiskey_foxtrot'::text ELSE NULL::text END)))
31. 0.253 0.387 ↑ 1.0 320 1

Sort (cost=30.280..31.080 rows=320 width=40) (actual time=0.361..0.387 rows=320 loops=1)

  • Sort Key: ((bravo_six.bravo_bravo)::text)
  • Sort Method: quicksort Memory: 50kB
32. 0.050 0.134 ↑ 1.0 320 1

Subquery Scan on bravo_six (cost=0.000..16.960 rows=320 width=40) (actual time=0.010..0.134 rows=320 loops=1)

33. 0.014 0.084 ↑ 1.0 320 1

Append (cost=0.000..13.760 rows=320 width=40) (actual time=0.006..0.084 rows=320 loops=1)

34. 0.006 0.006 ↑ 1.0 15 1

Seq Scan on echo_echo (cost=0.000..1.150 rows=15 width=16) (actual time=0.005..0.006 rows=15 loops=1)

35. 0.021 0.064 ↑ 1.0 305 1

Subquery Scan on echo_uniform (cost=0.000..10.860 rows=305 width=16) (actual time=0.009..0.064 rows=305 loops=1)

36. 0.043 0.043 ↑ 1.0 305 1

Seq Scan on five_papa (cost=0.000..7.050 rows=305 width=12) (actual time=0.006..0.043 rows=305 loops=1)

37. 0.322 0.549 ↑ 1.0 477 1

Sort (cost=57.690..58.930 rows=496 width=16) (actual time=0.512..0.549 rows=477 loops=1)

  • Sort Key: (lima_sierra(quebec1.four_golf4217, quebec2.four_golf4217, CASE WHEN (quebec1.uniform_uniform = 500544) THEN 'whiskey_foxtrot'::text ELSE NULL::text END))
  • Sort Method: quicksort Memory: 54kB
38. 0.074 0.227 ↑ 1.1 447 1

Hash Left Join (cost=18.210..35.480 rows=496 width=16) (actual time=0.132..0.227 rows=447 loops=1)

  • Hash Cond: (quebec1.xray = quebec2.uniform_uniform)
  • Filter: (lima_sierra(quebec1.four_golf4217, quebec2.four_golf4217, CASE WHEN (quebec1.uniform_uniform = 500544) THEN 'whiskey_foxtrot'::text ELSE NULL::text END) five_romeo NOT NULL)
  • Rows Removed by Filter: 52
39. 0.046 0.046 ↑ 1.0 498 1

Seq Scan on five_papa foxtrot_four (cost=0.000..11.980 rows=498 width=20) (actual time=0.005..0.046 rows=498 loops=1)

40. 0.054 0.107 ↑ 1.0 498 1

Hash (cost=11.980..11.980 rows=498 width=12) (actual time=0.107..0.107 rows=498 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
41. 0.053 0.053 ↑ 1.0 498 1

Seq Scan on five_papa november_mike (cost=0.000..11.980 rows=498 width=12) (actual time=0.004..0.053 rows=498 loops=1)

Planning time : 13.977 ms
Execution time : 1,634,395.756 ms