explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4CWE

Settings
# exclusive inclusive rows x rows loops node
1. 15.649 125,895.904 ↓ 2.0 1,975 1

Merge Left Join (cost=55,762.350..56,777.450 rows=1,000 width=404) (actual time=125,890.057..125,895.904 rows=1,975 loops=1)

  • Merge Cond: ((hotel_quebec.lima_quebec = papa_kilo.lima_quebec) AND ((kilo_foxtrot.zulu_seven)::text = (papa_kilo.zulu_seven)::text) AND ((kilo_foxtrot.romeo_charlie)::text = (papa_kilo.romeo_charlie)::text))
2.          

CTE four

3. 0.762 3.571 ↓ 1.0 1,422 1

Hash Right Join (cost=109.430..150.640 rows=1,416 width=185) (actual time=2.283..3.571 rows=1,422 loops=1)

  • Hash Cond: (five.oscar_november = romeo_quebec.oscar_november)
4. 0.447 0.677 ↑ 1.0 1,006 1

Hash Left Join (cost=16.590..36.330 rows=1,006 width=25) (actual time=0.137..0.677 rows=1,006 loops=1)

  • Hash Cond: (five.romeo_four = oscar_mike1.romeo_four)
5. 0.107 0.107 ↑ 1.0 1,006 1

Seq Scan on november_bravo five (cost=0.000..17.060 rows=1,006 width=25) (actual time=0.006..0.107 rows=1,006 loops=1)

6. 0.078 0.123 ↑ 1.0 382 1

Hash (cost=11.820..11.820 rows=382 width=9) (actual time=0.123..0.123 rows=382 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
7. 0.045 0.045 ↑ 1.0 382 1

Seq Scan on golf_yankee juliet (cost=0.000..11.820 rows=382 width=9) (actual time=0.003..0.045 rows=382 loops=1)

8. 0.302 2.132 ↓ 1.0 711 1

Hash (cost=83.980..83.980 rows=708 width=175) (actual time=2.132..2.132 rows=711 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 109kB
9. 0.311 1.830 ↓ 1.0 711 1

Hash Left Join (cost=64.340..83.980 rows=708 width=175) (actual time=0.650..1.830 rows=711 loops=1)

  • Hash Cond: (papa_hotel.tango_three = lima_india.tango_three)
10. 0.283 1.204 ↓ 1.0 711 1

Hash Left Join (cost=32.410..50.180 rows=708 width=167) (actual time=0.330..1.204 rows=711 loops=1)

  • Hash Cond: (romeo_quebec.xray_oscar = seven_golf.xray_oscar)
11. 0.348 0.914 ↓ 1.0 711 1

Hash Right Join (cost=31.320..45.270 rows=708 width=68) (actual time=0.318..0.914 rows=711 loops=1)

  • Hash Cond: (papa_hotel.oscar_november = romeo_quebec.oscar_november)
12. 0.260 0.260 ↑ 1.0 708 1

Seq Scan on six papa_hotel (cost=0.000..12.080 rows=708 width=18) (actual time=0.006..0.260 rows=708 loops=1)

13. 0.156 0.306 ↑ 1.0 503 1

Hash (cost=25.030..25.030 rows=503 width=59) (actual time=0.306..0.306 rows=503 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
14. 0.150 0.150 ↑ 1.0 503 1

Seq Scan on quebec_three_alpha romeo_quebec (cost=0.000..25.030 rows=503 width=59) (actual time=0.013..0.150 rows=503 loops=1)

15. 0.002 0.007 ↑ 1.0 4 1

Hash (cost=1.040..1.040 rows=4 width=109) (actual time=0.007..0.007 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on golf_lima seven_golf (cost=0.000..1.040 rows=4 width=109) (actual time=0.004..0.005 rows=4 loops=1)

17. 0.171 0.315 ↑ 1.0 708 1

Hash (cost=23.080..23.080 rows=708 width=17) (actual time=0.315..0.315 rows=708 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
18. 0.144 0.144 ↑ 1.0 708 1

Seq Scan on echo lima_india (cost=0.000..23.080 rows=708 width=17) (actual time=0.003..0.144 rows=708 loops=1)

19.          

CTE november_whiskey

20. 5,996.577 39,977.642 ↓ 3.7 146,100 1

GroupAggregate (cost=9,384.250..10,984.250 rows=40,000 width=324) (actual time=31,711.566..39,977.642 rows=146,100 loops=1)

  • Group Key: alpha_yankee1.golf_quebec, quebec_three_three.zulu_seven, quebec_three_three.romeo_charlie
21. 30,182.811 33,981.065 ↓ 367.4 14,697,660 1

Sort (cost=9,384.250..9,484.250 rows=40,000 width=312) (actual time=31,711.469..33,981.065 rows=14,697,660 loops=1)

  • Sort Key: alpha_yankee1.golf_quebec, quebec_three_three.zulu_seven, quebec_three_three.romeo_charlie
  • Sort Method: external sort Disk: 893472kB
22. 2,704.272 3,798.254 ↓ 367.4 14,697,660 1

Nested Loop (cost=76.210..583.210 rows=40,000 width=312) (actual time=67.593..3,798.254 rows=14,697,660 loops=1)

23. 18.606 71.282 ↓ 146.1 29,220 1

Sort (cost=26.650..27.150 rows=200 width=369) (actual time=62.122..71.282 rows=29,220 loops=1)

  • Sort Key: ((xray_yankee_hotel((alpha_yankee1.golf_quebec)::timestamp with time zone, 'papa_uniform'::text))::integer)
  • Sort Method: quicksort Memory: 2138kB
24. 35.279 52.676 ↓ 146.1 29,220 1

Subquery Scan on two (cost=12.500..19.000 rows=200 width=369) (actual time=10.154..52.676 rows=29,220 loops=1)

25. 14.025 17.397 ↓ 146.1 29,220 1

HashAggregate (cost=12.500..15.000 rows=200 width=8) (actual time=10.142..17.397 rows=29,220 loops=1)

  • Group Key: uniform_yankee1.uniform_juliet
26. 3.372 3.372 ↓ 29.2 29,220 1

Function Scan on xray_yankee_delta sierra_bravo_charlie (cost=0.000..10.000 rows=1,000 width=4) (actual time=1.546..3.372 rows=29,220 loops=1)

27. 1,017.049 1,022.700 ↓ 2.5 503 29,220

Materialize (cost=49.560..54.560 rows=200 width=308) (actual time=0.000..0.035 rows=503 loops=29,220)

28. 0.075 5.651 ↓ 2.5 503 1

Subquery Scan on quebec_three_three (cost=49.560..53.560 rows=200 width=308) (actual time=5.465..5.651 rows=503 loops=1)

29. 0.971 5.576 ↓ 2.5 503 1

HashAggregate (cost=49.560..51.560 rows=200 width=340) (actual time=5.464..5.576 rows=503 loops=1)

  • Group Key: four.zulu_seven, four.romeo_charlie, four.oscar_november, four.papa_oscar, four.uniform_oscar, four.tango_kilo
30. 4.605 4.605 ↓ 1.0 1,422 1

CTE Scan on four (cost=0.000..28.320 rows=1,416 width=340) (actual time=2.285..4.605 rows=1,422 loops=1)

31.          

CTE delta

32. 2,947.114 25,179.952 ↓ 3.7 146,100 1

GroupAggregate (cost=9,324.610..10,419.110 rows=39,800 width=308) (actual time=20,816.633..25,179.952 rows=146,100 loops=1)

  • Group Key: alpha_yankee2.golf_quebec, golf_juliet1.zulu_seven, golf_juliet1.romeo_charlie
33. 19,430.604 22,232.838 ↓ 278.3 11,074,380 1

Sort (cost=9,324.610..9,424.110 rows=39,800 width=308) (actual time=20,816.570..22,232.838 rows=11,074,380 loops=1)

  • Sort Key: alpha_yankee2.golf_quebec, golf_juliet1.zulu_seven, golf_juliet1.romeo_charlie
  • Sort Method: external sort Disk: 669776kB
34. 1,971.232 2,802.234 ↓ 278.3 11,074,380 1

Nested Loop (cost=63.820..568.290 rows=39,800 width=308) (actual time=63.748..2,802.234 rows=11,074,380 loops=1)

35. 18.104 71.282 ↓ 146.1 29,220 1

Sort (cost=26.650..27.150 rows=200 width=369) (actual time=63.134..71.282 rows=29,220 loops=1)

  • Sort Key: ((xray_yankee_hotel((alpha_yankee2.golf_quebec)::timestamp with time zone, 'papa_uniform'::text))::integer)
  • Sort Method: quicksort Memory: 2138kB
36. 33.490 53.178 ↓ 146.1 29,220 1

Subquery Scan on whiskey_delta_hotel (cost=12.500..19.000 rows=200 width=369) (actual time=12.172..53.178 rows=29,220 loops=1)

37. 15.773 19.688 ↓ 146.1 29,220 1

HashAggregate (cost=12.500..15.000 rows=200 width=8) (actual time=12.155..19.688 rows=29,220 loops=1)

  • Group Key: uniform_yankee2.uniform_juliet
38. 3.915 3.915 ↓ 29.2 29,220 1

Function Scan on xray_yankee_delta whiskey_delta_india (cost=0.000..10.000 rows=1,000 width=4) (actual time=2.008..3.915 rows=29,220 loops=1)

39. 758.982 759.720 ↓ 1.9 379 29,220

Materialize (cost=37.170..42.150 rows=199 width=304) (actual time=0.000..0.026 rows=379 loops=29,220)

40. 0.057 0.738 ↓ 1.9 379 1

Subquery Scan on zulu_zulu (cost=37.170..41.150 rows=199 width=304) (actual time=0.608..0.738 rows=379 loops=1)

41. 0.432 0.681 ↓ 1.9 379 1

HashAggregate (cost=37.170..39.160 rows=199 width=336) (actual time=0.607..0.681 rows=379 loops=1)

  • Group Key: sierra_bravo_seven1.zulu_seven, sierra_bravo_seven1.romeo_charlie, sierra_bravo_seven1.romeo_four, sierra_bravo_seven1.papa_oscar, sierra_bravo_seven1.uniform_oscar
42. 0.249 0.249 ↓ 1.0 711 1

CTE Scan on four papa_foxtrot (cost=0.000..28.320 rows=708 width=336) (actual time=0.003..0.249 rows=711 loops=1)

  • Filter: quebec_uniform
  • Rows Removed by Filter: 711
43.          

CTE foxtrot

44. 9,476.855 60,019.984 ↓ 3.7 146,100 1

GroupAggregate (cost=11,029.290..12,929.290 rows=40,000 width=372) (actual time=47,568.701..60,019.984 rows=146,100 loops=1)

  • Group Key: alpha_yankee3.golf_quebec, alpha_quebec.zulu_seven, alpha_quebec.romeo_charlie
45. 44,982.601 50,543.129 ↓ 519.4 20,775,420 1

Sort (cost=11,029.290..11,129.290 rows=40,000 width=404) (actual time=47,568.597..50,543.129 rows=20,775,420 loops=1)

  • Sort Key: alpha_yankee3.golf_quebec, alpha_quebec.zulu_seven, alpha_quebec.romeo_charlie
  • Sort Method: external sort Disk: 1598296kB
46. 4,058.392 5,560.528 ↓ 519.4 20,775,420 1

Nested Loop (cost=79.750..586.750 rows=40,000 width=404) (actual time=61.467..5,560.528 rows=20,775,420 loops=1)

47. 19.754 70.356 ↓ 146.1 29,220 1

Sort (cost=26.650..27.150 rows=200 width=369) (actual time=60.227..70.356 rows=29,220 loops=1)

  • Sort Key: ((xray_yankee_hotel((alpha_yankee3.golf_quebec)::timestamp with time zone, 'papa_uniform'::text))::integer)
  • Sort Method: quicksort Memory: 2138kB
48. 32.897 50.602 ↓ 146.1 29,220 1

Subquery Scan on yankee (cost=12.500..19.000 rows=200 width=369) (actual time=10.479..50.602 rows=29,220 loops=1)

49. 13.763 17.705 ↓ 146.1 29,220 1

HashAggregate (cost=12.500..15.000 rows=200 width=8) (actual time=10.463..17.705 rows=29,220 loops=1)

  • Group Key: uniform_yankee3.uniform_juliet
50. 3.942 3.942 ↓ 29.2 29,220 1

Function Scan on xray_yankee_delta oscar_delta (cost=0.000..10.000 rows=1,000 width=4) (actual time=2.137..3.942 rows=29,220 loops=1)

51. 1,430.278 1,431.780 ↓ 3.6 711 29,220

Materialize (cost=53.100..58.100 rows=200 width=400) (actual time=0.000..0.049 rows=711 loops=29,220)

52. 0.111 1.502 ↓ 3.6 711 1

Subquery Scan on alpha_quebec (cost=53.100..57.100 rows=200 width=400) (actual time=1.235..1.502 rows=711 loops=1)

53. 1.228 1.391 ↓ 3.6 711 1

HashAggregate (cost=53.100..55.100 rows=200 width=400) (actual time=1.234..1.391 rows=711 loops=1)

  • Group Key: sierra_bravo_seven2.zulu_seven, sierra_bravo_seven2.romeo_charlie, sierra_bravo_seven2.seven_papa, sierra_bravo_seven2.sierra_seven, sierra_bravo_seven2.tango_three, sierra_bravo_seven2.papa_oscar, sierra_bravo_seven2.uniform_oscar
54. 0.163 0.163 ↓ 1.0 1,422 1

CTE Scan on four november_romeo (cost=0.000..28.320 rows=1,416 width=400) (actual time=0.002..0.163 rows=1,422 loops=1)

55. 14.857 65,536.206 ↓ 2.0 1,975 1

Merge Left Join (cost=10,582.530..11,090.080 rows=1,000 width=332) (actual time=65,533.111..65,536.206 rows=1,975 loops=1)

  • Merge Cond: ((hotel_quebec.lima_quebec = quebec_sierra.lima_quebec) AND ((kilo_foxtrot.zulu_seven)::text = (quebec_sierra.zulu_seven)::text) AND ((kilo_foxtrot.romeo_charlie)::text = (quebec_sierra.romeo_charlie)::text))
56. 0.966 40,088.856 ↓ 2.0 1,975 1

Sort (cost=1,030.210..1,032.710 rows=1,000 width=324) (actual time=40,088.741..40,088.856 rows=1,975 loops=1)

  • Sort Key: hotel_quebec.lima_quebec, kilo_foxtrot.zulu_seven, kilo_foxtrot.romeo_charlie
  • Sort Method: quicksort Memory: 326kB
57. 22.562 40,087.890 ↓ 2.0 1,975 1

Hash Right Join (cost=20.380..980.380 rows=1,000 width=324) (actual time=36,616.747..40,087.890 rows=1,975 loops=1)

  • Hash Cond: (kilo_foxtrot.lima_quebec = hotel_quebec.lima_quebec)
58. 40,059.546 40,059.546 ↓ 3.7 146,100 1

CTE Scan on november_whiskey kilo_foxtrot (cost=0.000..800.000 rows=40,000 width=324) (actual time=31,711.570..40,059.546 rows=146,100 loops=1)

59. 0.052 5.782 ↓ 79.0 395 1

Hash (cost=20.320..20.320 rows=5 width=4) (actual time=5.782..5.782 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
60. 0.051 5.730 ↓ 79.0 395 1

Subquery Scan on hotel_quebec (cost=20.260..20.320 rows=5 width=4) (actual time=5.655..5.730 rows=395 loops=1)

61. 0.097 5.679 ↓ 79.0 395 1

Sort (cost=20.260..20.270 rows=5 width=369) (actual time=5.654..5.679 rows=395 loops=1)

  • Sort Key: ((xray_yankee_hotel((hotel_papa.golf_quebec)::timestamp with time zone, 'papa_uniform'::text))::integer)
  • Sort Method: quicksort Memory: 43kB
62. 0.465 5.582 ↓ 79.0 395 1

Subquery Scan on hotel_papa (cost=20.060..20.200 rows=5 width=369) (actual time=5.004..5.582 rows=395 loops=1)

63. 0.098 5.117 ↓ 79.0 395 1

Group (cost=20.060..20.100 rows=5 width=8) (actual time=4.994..5.117 rows=395 loops=1)

  • Group Key: sierra_juliet.uniform_juliet
64. 0.064 5.019 ↓ 79.0 395 1

Sort (cost=20.060..20.070 rows=5 width=4) (actual time=4.992..5.019 rows=395 loops=1)

  • Sort Key: sierra_juliet.uniform_juliet
  • Sort Method: quicksort Memory: 43kB
65. 4.955 4.955 ↓ 79.0 395 1

Function Scan on xray_yankee_delta sierra_juliet (cost=0.000..20.000 rows=5 width=4) (actual time=3.674..4.955 rows=395 loops=1)

  • Filter: ((('november_juliet'::date + uniform_juliet) >= 'romeo_seven'::date) AND (('november_juliet'::date + uniform_juliet) <= 'kilo_alpha'::date))
  • Rows Removed by Filter: 28825
66. 12.470 25,432.493 ↓ 2.3 91,616 1

Materialize (cost=9,552.320..9,751.320 rows=39,800 width=308) (actual time=25,407.091..25,432.493 rows=91,616 loops=1)

67. 158.539 25,420.023 ↓ 2.3 91,616 1

Sort (cost=9,552.320..9,651.820 rows=39,800 width=308) (actual time=25,407.088..25,420.023 rows=91,616 loops=1)

  • Sort Key: quebec_sierra.lima_quebec, quebec_sierra.zulu_seven, quebec_sierra.romeo_charlie
  • Sort Method: external sort Disk: 8152kB
68. 25,261.484 25,261.484 ↓ 3.7 146,100 1

CTE Scan on delta quebec_sierra (cost=0.000..796.000 rows=39,800 width=308) (actual time=20,816.637..25,261.484 rows=146,100 loops=1)

69. 12.836 60,344.049 ↓ 2.3 91,616 1

Materialize (cost=10,696.540..10,896.540 rows=40,000 width=372) (actual time=60,315.868..60,344.049 rows=91,616 loops=1)

70. 202.639 60,331.213 ↓ 2.3 91,616 1

Sort (cost=10,696.540..10,796.540 rows=40,000 width=372) (actual time=60,315.863..60,331.213 rows=91,616 loops=1)

  • Sort Key: papa_kilo.lima_quebec, papa_kilo.zulu_seven, papa_kilo.romeo_charlie
  • Sort Method: external sort Disk: 9304kB
71. 60,128.574 60,128.574 ↓ 3.7 146,100 1

CTE Scan on foxtrot papa_kilo (cost=0.000..800.000 rows=40,000 width=372) (actual time=47,568.705..60,128.574 rows=146,100 loops=1)