explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ABib

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 280.452 ↓ 0.0 0 1

Sort (cost=5,583.600..5,583.600 rows=1 width=151) (actual time=280.452..280.452 rows=0 loops=1)

  • Sort Key: romeo_charlie_lima.foxtrot_four, victor_four.foxtrot_four
  • Sort Method: quicksort Memory: 25kB
2.          

CTE november_echo_seven

3. 0.000 0.024 ↓ 0.0 0 1

HashAggregate (cost=17.070..17.080 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=1)

  • Group Key: papa_india1.quebec_seven, papa_india1.uniform_six
4. 0.000 0.024 ↓ 0.0 0 1

Nested Loop (cost=0.990..17.060 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=1)

5. 0.004 0.024 ↓ 0.0 0 1

Nested Loop (cost=0.840..16.890 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=1)

6. 0.010 0.010 ↓ 10.0 10 1

Index Scan using bravo_oscar on zulu_echo_hotel (cost=0.430..8.450 rows=1 width=8) (actual time=0.006..0.010 rows=10 loops=1)

  • Index Cond: (uniform_lima > (zulu_golf() - 'whiskey'::interval))
7. 0.010 0.010 ↓ 0.0 0 10

Index Scan using charlie_xray_kilo on five_bravo (cost=0.420..8.440 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (quebec_seven = zulu_echo_hotel.five_five)
8. 0.000 0.000 ↓ 0.0 0

Index Scan using six on romeo_charlie_lima zulu_alpha (cost=0.140..0.160 rows=1 width=16) (never executed)

  • Index Cond: (quebec_seven = five_bravo.bravo_tango)
9.          

CTE two_papa

10. 0.000 0.018 ↓ 0.0 0 1

GroupAggregate (cost=17.200..17.230 rows=1 width=25) (actual time=0.018..0.018 rows=0 loops=1)

  • Group Key: delta_romeo1.quebec_seven
11. 0.001 0.018 ↓ 0.0 0 1

Sort (cost=17.200..17.210 rows=1 width=25) (actual time=0.018..0.018 rows=0 loops=1)

  • Sort Key: delta_romeo1.quebec_seven
  • Sort Method: quicksort Memory: 25kB
12. 0.000 0.017 ↓ 0.0 0 1

Nested Loop (cost=1.120..17.190 rows=1 width=25) (actual time=0.017..0.017 rows=0 loops=1)

13. 0.002 0.017 ↓ 0.0 0 1

Nested Loop (cost=0.840..16.890 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=1)

14. 0.005 0.005 ↓ 10.0 10 1

Index Scan using bravo_oscar on zulu_echo_hotel golf_delta (cost=0.430..8.450 rows=1 width=8) (actual time=0.003..0.005 rows=10 loops=1)

  • Index Cond: (uniform_lima > (zulu_golf() - 'delta_six'::interval))
15. 0.010 0.010 ↓ 0.0 0 10

Index Scan using charlie_xray_kilo on five_bravo quebec_xray (cost=0.420..8.440 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (quebec_seven = november_six1.five_five)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using quebec_yankee on victor_four november_four (cost=0.280..0.290 rows=1 width=17) (never executed)

  • Index Cond: (quebec_seven = golf_juliet1.five_alpha)
17.          

CTE zulu_oscar

18. 0.001 0.025 ↓ 0.0 0 1

HashAggregate (cost=0.020..0.040 rows=1 width=16) (actual time=0.025..0.025 rows=0 loops=1)

  • Group Key: zulu_echo_juliet1.uniform_six
19. 0.024 0.024 ↓ 0.0 0 1

CTE Scan on november_echo_seven romeo_victor (cost=0.000..0.020 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=1)

20.          

CTE four_lima

21. 180.580 234.218 ↓ 16,680.3 50,041 1

Hash Join (cost=1,002.580..5,534.110 rows=3 width=20) (actual time=2.836..234.218 rows=50,041 loops=1)

  • Hash Cond: ((papa_india2.quebec_seven = xray.bravo_tango) AND (echo_tango('papa_sierra'::text, ((charlie_xray_echo(golf_juliet2.oscar_five, zulu_golf()))::date)::timestamp without time zone) = (xray.lima_xray)::double precision))
  • Join Filter: ((zulu_golf() >= (charlie_xray_echo(golf_juliet2.oscar_five, ((charlie_xray_echo(golf_juliet2.oscar_five, zulu_golf()))::date)::timestamp without time zone) + (xray.four_charlie)::interval)) AND (zulu_golf() < (charlie_xray_echo(golf_juliet2.oscar_five, ((charlie_xray_echo(golf_juliet2.oscar_five, zulu_golf()))::date)::timestamp without time zone) + ((xray.
22. 19.418 53.309 ↓ 60.1 50,041 1

Hash Join (cost=972.000..5,439.250 rows=833 width=39) (actual time=2.479..53.309 rows=50,041 loops=1)

  • Hash Cond: (golf_juliet2.bravo_tango = papa_india2.quebec_seven)
  • Join Filter: ((golf_juliet2.five_two <= papa_india2.golf_seven) AND (golf_juliet2.romeo_charlie_xray <= papa_india2.sierra_romeo))
23. 11.815 33.821 ↓ 6.7 50,041 1

Hash Left Join (cost=959.100..5,285.720 rows=7,500 width=39) (actual time=2.405..33.821 rows=50,041 loops=1)

  • Hash Cond: (golf_juliet2.five_alpha = golf_bravo1.five_alpha)
  • Filter: ((golf_bravo1.five_alpha five_romeo NULL) OR (golf_bravo1.hotel AND (golf_bravo1.victor_juliet = 1) AND (lima_sierra(golf_bravo1.bravo_golf, 'november_golf'::bigint) < golf_bravo1.foxtrot_quebec)))
24. 19.739 22.006 ↓ 1.1 50,041 1

Bitmap Heap Scan on five_bravo kilo (cost=959.070..5,120.650 rows=43,902 width=39) (actual time=2.400..22.006 rows=50,041 loops=1)

  • Recheck Cond: ((victor_juliet = 2) OR (victor_juliet = 3))
  • Filter: (hotel AND ((quebec_alpha five_romeo NULL) OR (quebec_alpha <= zulu_golf())) AND ((seven = 0) OR ((seven = 1) AND (lima_seven >= 20))))
  • Heap Blocks: exact=1419
25. 0.001 2.267 ↓ 0.0 0 1

BitmapOr (cost=959.070..959.070 rows=50,203 width=0) (actual time=2.267..2.267 rows=0 loops=1)

26. 1.165 1.165 ↑ 1.0 25,079 1

Bitmap Index Scan on november_echo_romeo (cost=0.000..468.820 rows=25,137 width=0) (actual time=1.165..1.165 rows=25,079 loops=1)

  • Index Cond: (victor_juliet = 2)
27. 1.101 1.101 ↑ 1.0 24,962 1

Bitmap Index Scan on november_echo_romeo (cost=0.000..468.300 rows=25,067 width=0) (actual time=1.101..1.101 rows=24,962 loops=1)

  • Index Cond: (victor_juliet = 3)
28. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=0.020..0.020 rows=1 width=25) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
29. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on two_papa five_sierra (cost=0.000..0.020 rows=1 width=25) (actual time=0.000..0.000 rows=0 loops=1)

30. 0.019 0.070 ↑ 1.0 129 1

Hash (cost=11.290..11.290 rows=129 width=16) (actual time=0.070..0.070 rows=129 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
31. 0.051 0.051 ↑ 1.0 129 1

Seq Scan on romeo_charlie_lima bravo_whiskey (cost=0.000..11.290 rows=129 width=16) (actual time=0.003..0.051 rows=129 loops=1)

32. 0.189 0.329 ↑ 1.0 903 1

Hash (cost=17.030..17.030 rows=903 width=24) (actual time=0.329..0.329 rows=903 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
33. 0.140 0.140 ↑ 1.0 903 1

Seq Scan on xray (cost=0.000..17.030 rows=903 width=24) (actual time=0.002..0.140 rows=903 loops=1)

  • Filter: hotel
34.          

CTE victor_seven

35. 4.910 11.772 ↓ 1,000.0 1,000 1

HashAggregate (cost=0.080..0.090 rows=1 width=16) (actual time=11.642..11.772 rows=1,000 loops=1)

  • Group Key: four_lima.five_alpha, four_lima.bravo_tango
36. 6.862 6.862 ↓ 24,962.0 24,962 1

CTE Scan on four_lima (cost=0.000..0.070 rows=1 width=16) (actual time=0.002..6.862 rows=24,962 loops=1)

  • Filter: (victor_juliet = 3)
  • Rows Removed by Filter: 25079
37.          

CTE lima_golf

38. 6.422 259.807 ↓ 100.0 100 1

HashAggregate (cost=0.070..0.080 rows=1 width=8) (actual time=259.790..259.807 rows=100 loops=1)

  • Group Key: charlie_victor1.bravo_tango
39. 253.385 253.385 ↓ 25,079.0 25,079 1

CTE Scan on four_lima lima_echo (cost=0.000..0.070 rows=1 width=8) (actual time=2.845..253.385 rows=25,079 loops=1)

  • Filter: (victor_juliet = 2)
  • Rows Removed by Filter: 24962
40. 0.205 280.448 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.610..14.970 rows=1 width=151) (actual time=280.448..280.448 rows=0 loops=1)

  • Join Filter: (romeo_charlie_lima.quebec_seven = victor_seven.bravo_tango)
  • Rows Removed by Join Filter: 2000
  • Filter: (((lima_golf.echo_victor > 0) AND (lima_sierra(two_papa.november_echo_victor, 'november_golf'::bigint) < victor_four.foxtrot_delta)) OR ((victor_seven.five_three > 0) AND (lima_sierra(two_papa.bravo_golf, 'november_golf'::bigint) < victor_four.foxtrot_quebec) AND (three: delta_four 7 or mike delta_four 8)))
  • Rows Removed by Filter: 2
41. 0.030 260.507 ↓ 2.0 2 1

Nested Loop Left Join (cost=1.610..14.860 rows=1 width=171) (actual time=259.936..260.507 rows=2 loops=1)

  • Join Filter: (romeo_charlie_lima.quebec_seven = lima_golf.bravo_tango)
  • Rows Removed by Join Filter: 200
42. 0.003 0.639 ↓ 2.0 2 1

Nested Loop Left Join (cost=1.610..14.830 rows=1 width=163) (actual time=0.089..0.639 rows=2 loops=1)

  • Join Filter: (victor_four.quebec_seven = two_papa.five_alpha)
43. 0.000 0.618 ↓ 2.0 2 1

Nested Loop (cost=1.610..14.790 rows=1 width=147) (actual time=0.069..0.618 rows=2 loops=1)

44. 0.033 0.217 ↓ 25.2 101 1

Hash Left Join (cost=1.330..9.860 rows=4 width=121) (actual time=0.063..0.217 rows=101 loops=1)

  • Hash Cond: (romeo_charlie_lima.quebec_seven = november_echo_seven.bravo_tango)
  • Filter: (lima_sierra(november_echo_seven.zulu_tango, 'november_golf'::bigint) < romeo_charlie_lima.foxtrot_quebec)
45. 0.050 0.184 ↓ 7.8 101 1

Merge Join (cost=1.300..9.770 rows=13 width=113) (actual time=0.060..0.184 rows=101 loops=1)

  • Merge Cond: (romeo_charlie_lima.uniform_six = oscar_delta.quebec_seven)
46. 0.077 0.077 ↓ 1.1 101 1

Index Scan using yankee on romeo_charlie_lima (cost=0.140..39.340 rows=95 width=77) (actual time=0.011..0.077 rows=101 loops=1)

  • Filter: (hotel AND (two_charlie = 2))
  • Rows Removed by Filter: 28
47. 0.013 0.057 ↓ 46.0 92 1

Sort (cost=1.150..1.160 rows=2 width=44) (actual time=0.048..0.057 rows=92 loops=1)

  • Sort Key: oscar_delta.quebec_seven
  • Sort Method: quicksort Memory: 25kB
48. 0.013 0.044 ↓ 5.5 11 1

Hash Left Join (cost=0.030..1.140 rows=2 width=44) (actual time=0.037..0.044 rows=11 loops=1)

  • Hash Cond: (oscar_delta.quebec_seven = zulu_oscar.uniform_six)
  • Filter: (lima_sierra(zulu_oscar.five_golf, 'november_golf'::numeric) < (oscar_delta.quebec_romeo)::numeric)
  • Rows Removed by Filter: 6
49. 0.005 0.005 ↓ 2.4 17 1

Seq Scan on oscar_delta (cost=0.000..1.070 rows=7 width=12) (actual time=0.002..0.005 rows=17 loops=1)

  • Filter: hotel
50. 0.001 0.026 ↓ 0.0 0 1

Hash (cost=0.020..0.020 rows=1 width=40) (actual time=0.026..0.026 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
51. 0.025 0.025 ↓ 0.0 0 1

CTE Scan on zulu_oscar (cost=0.000..0.020 rows=1 width=40) (actual time=0.025..0.025 rows=0 loops=1)

52. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=0.020..0.020 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
53. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on november_echo_seven (cost=0.000..0.020 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)

54. 0.404 0.404 ↓ 0.0 0 101

Index Scan using four_november on victor_four (cost=0.280..1.220 rows=1 width=34) (actual time=0.004..0.004 rows=0 loops=101)

  • Index Cond: (bravo_tango = romeo_charlie_lima.quebec_seven)
  • Filter: (hotel AND (victor_juliet = 1) AND (foxtrot_four < (zulu_golf() - 'romeo_whiskey'::interval)))
  • Rows Removed by Filter: 10
55. 0.018 0.018 ↓ 0.0 0 2

CTE Scan on two_papa (cost=0.000..0.020 rows=1 width=24) (actual time=0.009..0.009 rows=0 loops=2)

56. 259.838 259.838 ↓ 100.0 100 2

CTE Scan on lima_golf (cost=0.000..0.020 rows=1 width=16) (actual time=129.895..129.919 rows=100 loops=2)

57. 12.106 12.106 ↓ 1,000.0 1,000 2

CTE Scan on victor_seven (cost=0.000..0.020 rows=1 width=16) (actual time=5.822..6.053 rows=1,000 loops=2)

58.          

SubPlan (forNested Loop Left Join)

59. 7.630 7.630 ↓ 0.0 0 2

CTE Scan on four_lima sierra_alpha (cost=0.000..0.070 rows=1 width=0) (actual time=3.815..3.815 rows=0 loops=2)

  • Filter: (five_alpha = victor_four.quebec_seven)
  • Rows Removed by Filter: 50041
60. 0.000 0.000 ↓ 0.0 0

CTE Scan on four_lima sierra_india (cost=0.000..0.060 rows=3 width=8) (never executed)