explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mQgv

Settings
# exclusive inclusive rows x rows loops node
1. 4.178 37,096.614 ↑ 1.0 1 1

Aggregate (cost=220,857.94..220,857.95 rows=1 width=8) (actual time=37,096.612..37,096.614 rows=1 loops=1)

2.          

CTE papa_alpha

3. 26.243 174.893 ↓ 5,535.5 11,071 1

Unique (cost=3.00..3.17 rows=2 width=660) (actual time=139.662..174.893 rows=11,071 loops=1)

4. 82.305 148.650 ↓ 5,535.5 11,071 1

Sort (cost=3.00..3.00 rows=2 width=660) (actual time=139.658..148.650 rows=11,071 loops=1)

  • Sort Key: ('zulu_zulu'::text), quebec_three, whiskey_victor, india_bravo, golf_papa, four, hotel_romeo, golf_foxtrot_india, november_india, oscar_xray, two_seven, india_delta, november_echo, uniform_yankee_four, seven_golf, charlie_november, whiskey_tango, juliet_alpha, uniform_november, bravo_lima, mike_tango, charlie_xray, india_mike, hotel_whiskey, juliet_hotel, uniform_oscar, sierra_delta, echo_five, golf_five, charlie_romeo, victor_quebec, alpha_xray, foxtrot_seven
  • Sort Method: quicksort Memory: 8,538kB
5. 15.508 66.345 ↓ 5,535.5 11,071 1

Append (cost=0.00..2.99 rows=2 width=660) (actual time=0.171..66.345 rows=11,071 loops=1)

6. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=660) (actual time=0.002..0.002 rows=0 loops=1)

  • One-Time Filter: false
7. 50.835 50.835 ↓ 11,071.0 11,071 1

Index Scan using tango_victor on india_quebec lima_charlie (cost=0.95..2.97 rows=1 width=1,156) (actual time=0.166..50.835 rows=11,071 loops=1)

  • Index Cond: ((quebec_three = ANY ('zulu_seven'::text[])) AND (tango_golf.echo_echo(('delta'::timestamp with time zone - 'two_oscar_golf'::interval), 'alpha_six'::interval) < golf_papa) AND (golf_papa <= 'delta'::timestamp with time zone))
  • Filter: (india_bravo = 'alpha_six'::interval)
8.          

CTE mike_hotel

9. 9.454 18.643 ↑ 2.0 1 1

HashAggregate (cost=0.05..0.07 rows=2 width=40) (actual time=18.641..18.643 rows=1 loops=1)

  • Group Key: papa_alpha.quebec_three
10. 9.189 9.189 ↓ 5,535.5 11,071 1

CTE Scan on papa_alpha (cost=0.00..0.04 rows=2 width=40) (actual time=0.001..9.189 rows=11,071 loops=1)

11.          

CTE papa_tango

12. 91.823 34,590.075 ↓ 11,071.0 11,071 1

Hash Join (cost=0.07..159,466.77 rows=1 width=192) (actual time=25.456..34,590.075 rows=11,071 loops=1)

  • Hash Cond: (november_five.quebec_three = romeo_three.quebec_three)
  • Join Filter: (november_five.golf_papa > (romeo_three.golf_papa - 'seven_victor'::interval))
13. 15.573 15.573 ↓ 5,535.5 11,071 1

CTE Scan on papa_alpha november_five (cost=0.00..0.04 rows=2 width=128) (actual time=0.002..15.573 rows=11,071 loops=1)

14. 0.008 18.656 ↑ 2.0 1 1

Hash (cost=0.04..0.04 rows=2 width=40) (actual time=18.655..18.656 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 18.648 18.648 ↑ 2.0 1 1

CTE Scan on mike_hotel romeo_three (cost=0.00..0.04 rows=2 width=40) (actual time=18.645..18.648 rows=1 loops=1)

16.          

SubPlan (for Hash Join)

17. 44.284 17,137.908 ↑ 1.0 1 11,071

Aggregate (cost=79,844.65..79,844.66 rows=1 width=32) (actual time=1.547..1.548 rows=1 loops=11,071)

18. 44.284 17,093.624 ↓ 0.0 0 11,071

GroupAggregate (cost=79,844.34..79,844.39 rows=1 width=47) (actual time=1.543..1.544 rows=0 loops=11,071)

  • Group Key: echo_november.sierra_bravo, echo_november.whiskey_uniform
19. 55.355 17,049.340 ↓ 0.0 0 11,071

Sort (cost=79,844.34..79,844.35 rows=1 width=23) (actual time=1.540..1.540 rows=0 loops=11,071)

  • Sort Key: echo_november.sierra_bravo, echo_november.whiskey_uniform
  • Sort Method: quicksort Memory: 25kB
20. 16,993.985 16,993.985 ↓ 0.0 0 11,071

Index Scan using two_lima on november_four echo_november (cost=0.44..79,844.33 rows=1 width=23) (actual time=1.409..1.535 rows=0 loops=11,071)

  • Index Cond: ((november_five.whiskey_victor <= two_oscar_six) AND (two_oscar_six < november_five.golf_papa))
  • Filter: ((india_five five_romeo NOT NULL) AND ((quebec_three)::text = november_five.quebec_three) AND ((golf_foxtrot_india)::text = november_five.golf_foxtrot_india) AND ((november_india)::text = november_five.november_india) AND ((oscar_india)::text = 'golf_charlie_tango'::text))
  • Rows Removed by Filter: 2,229
21. 88.568 17,326.115 ↑ 1.0 1 11,071

Aggregate (cost=79,621.96..79,621.97 rows=1 width=32) (actual time=1.564..1.565 rows=1 loops=11,071)

22. 99.639 17,237.547 ↑ 1.0 1 11,071

GroupAggregate (cost=79,621.65..79,621.70 rows=1 width=47) (actual time=1.554..1.557 rows=1 loops=11,071)

  • Group Key: charlie_lima1.sierra_bravo, charlie_lima1.whiskey_uniform
23. 88.568 17,137.908 ↓ 2.0 2 11,071

Sort (cost=79,621.65..79,621.65 rows=1 width=23) (actual time=1.546..1.548 rows=2 loops=11,071)

  • Sort Key: charlie_lima1.sierra_bravo, charlie_lima1.whiskey_uniform
  • Sort Method: quicksort Memory: 25kB
24. 17,049.340 17,049.340 ↓ 2.0 2 11,071

Index Scan using two_lima on november_four whiskey_whiskey (cost=0.44..79,621.64 rows=1 width=23) (actual time=0.974..1.540 rows=2 loops=11,071)

  • Index Cond: ((november_five.whiskey_victor <= two_oscar_six) AND (two_oscar_six < november_five.golf_papa))
  • Filter: ((india_five five_romeo NOT NULL) AND ((quebec_three)::text = november_five.quebec_three) AND ((golf_foxtrot_india)::text = november_five.golf_foxtrot_india) AND ((november_india)::text = november_five.november_india))
  • Rows Removed by Filter: 2,228
25.          

CTE five_papa

26. 830.764 36,407.119 ↓ 2,680.0 5,360 1

GroupAggregate (cost=0.08..10,447.19 rows=2 width=636) (actual time=34,968.618..36,407.119 rows=5,360 loops=1)

  • Group Key: lima_three.quebec_three, lima_three.golf_foxtrot_india, lima_three.november_india
27. 55.789 35,043.704 ↓ 5,535.5 11,071 1

Merge Left Join (cost=0.08..0.14 rows=2 width=652) (actual time=34,967.238..35,043.704 rows=11,071 loops=1)

  • Merge Cond: ((lima_three.quebec_three = golf_foxtrot_xray.quebec_three) AND (lima_three.golf_foxtrot_india = golf_foxtrot_xray.golf_foxtrot_india) AND (lima_three.november_india = golf_foxtrot_xray.november_india) AND (lima_three.whiskey_victor = golf_foxtrot_xray.whiskey_victor) AND (lima_three.golf_papa = golf_foxtrot_xray.golf_papa))
28. 93.617 293.786 ↓ 5,535.5 11,071 1

Sort (cost=0.05..0.06 rows=2 width=604) (actual time=283.535..293.786 rows=11,071 loops=1)

  • Sort Key: lima_three.quebec_three, lima_three.golf_foxtrot_india, lima_three.november_india, lima_three.whiskey_victor, lima_three.golf_papa
  • Sort Method: quicksort Memory: 7,553kB
29. 200.169 200.169 ↓ 5,535.5 11,071 1

CTE Scan on papa_alpha lima_three (cost=0.00..0.04 rows=2 width=604) (actual time=139.668..200.169 rows=11,071 loops=1)

30. 68.586 34,694.129 ↓ 11,071.0 11,071 1

Sort (cost=0.03..0.04 rows=1 width=176) (actual time=34,683.690..34,694.129 rows=11,071 loops=1)

  • Sort Key: golf_foxtrot_xray.quebec_three, golf_foxtrot_xray.golf_foxtrot_india, golf_foxtrot_xray.november_india, golf_foxtrot_xray.whiskey_victor, golf_foxtrot_xray.golf_papa
  • Sort Method: quicksort Memory: 4,173kB
31. 34,625.543 34,625.543 ↓ 11,071.0 11,071 1

CTE Scan on papa_tango golf_foxtrot_xray (cost=0.00..0.02 rows=1 width=176) (actual time=25.460..34,625.543 rows=11,071 loops=1)

32.          

SubPlan (for GroupAggregate)

33. 16.080 80.400 ↑ 1.0 1 5,360

Aggregate (cost=2,581.00..2,581.01 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=5,360)

34. 26.800 64.320 ↓ 0.0 0 5,360

HashAggregate (cost=2,525.00..2,529.00 rows=200 width=64) (actual time=0.011..0.012 rows=0 loops=5,360)

  • Group Key: "zulu_quebec"(oscar_bravo.uniform_yankee_xray, 'golf_charlie_lima'::text)
35. 26.800 37.520 ↑ 100,000.0 1 5,360

ProjectSet (cost=0.00..525.00 rows=100,000 width=40) (actual time=0.004..0.007 rows=1 loops=5,360)

36. 10.720 10.720 ↓ 0.0 0 5,360

Function Scan on whiskey_mike oscar_bravo (cost=0.00..10.00 rows=1,000 width=64) (actual time=0.002..0.002 rows=0 loops=5,360)

37. 37.520 219.760 ↑ 1.0 1 5,360

Aggregate (cost=2,581.00..2,581.01 rows=1 width=32) (actual time=0.040..0.041 rows=1 loops=5,360)

38. 64.320 182.240 ↑ 200.0 1 5,360

HashAggregate (cost=2,525.00..2,529.00 rows=200 width=64) (actual time=0.032..0.034 rows=1 loops=5,360)

  • Group Key: "zulu_quebec"(victor_hotel1.uniform_yankee_xray, 'golf_charlie_lima'::text)
39. 91.120 117.920 ↑ 33,333.3 3 5,360

ProjectSet (cost=0.00..525.00 rows=100,000 width=40) (actual time=0.010..0.022 rows=3 loops=5,360)

40. 26.800 26.800 ↑ 500.0 2 5,360

Function Scan on whiskey_mike oscar_hotel (cost=0.00..10.00 rows=1,000 width=64) (actual time=0.004..0.005 rows=2 loops=5,360)

41. 33.213 55.355 ↑ 1.0 1 11,071

Aggregate (cost=1.31..1.32 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=11,071)

42. 22.142 22.142 ↓ 0.0 0 11,071

Function Scan on lima_oscar papa_india (cost=0.06..1.06 rows=100 width=8) (actual time=0.002..0.002 rows=0 loops=11,071)

43. 33.213 88.568 ↑ 1.0 1 11,071

Aggregate (cost=29.51..29.52 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=11,071)

44. 33.213 55.355 ↓ 0.0 0 11,071

HashAggregate (cost=22.50..26.50 rows=200 width=64) (actual time=0.005..0.005 rows=0 loops=11,071)

  • Group Key: "zulu_quebec"(victor_hotel3.uniform_yankee_xray, 'golf_charlie_lima'::text)
45. 22.142 22.142 ↓ 0.0 0 11,071

Function Scan on whiskey_mike romeo_whiskey (cost=0.00..12.50 rows=1,000 width=64) (actual time=0.002..0.002 rows=0 loops=11,071)

46. 33.213 88.568 ↑ 1.0 1 11,071

Aggregate (cost=29.51..29.52 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=11,071)

47. 33.213 55.355 ↓ 0.0 0 11,071

HashAggregate (cost=22.50..26.50 rows=200 width=64) (actual time=0.004..0.005 rows=0 loops=11,071)

  • Group Key: "zulu_quebec"(victor_hotel2.uniform_yankee_xray, 'golf_charlie_lima'::text)
48. 22.142 22.142 ↓ 0.0 0 11,071

Function Scan on whiskey_mike yankee (cost=0.00..12.50 rows=1,000 width=64) (actual time=0.001..0.002 rows=0 loops=11,071)

49.          

CTE lima_zulu

50. 68.331 1,978.250 ↓ 2,680.0 5,360 1

GroupAggregate (cost=0.05..50,939.53 rows=2 width=176) (actual time=1,471.424..1,978.250 rows=5,360 loops=1)

  • Group Key: five_papa.quebec_three, five_papa.golf_foxtrot_india, five_papa.november_india
51. 13.719 1,475.759 ↓ 2,680.0 5,360 1

Sort (cost=0.05..0.06 rows=2 width=168) (actual time=1,471.240..1,475.759 rows=5,360 loops=1)

  • Sort Key: five_papa.quebec_three, five_papa.golf_foxtrot_india, five_papa.november_india
  • Sort Method: quicksort Memory: 1,995kB
52. 1,462.040 1,462.040 ↓ 2,680.0 5,360 1

CTE Scan on five_papa (cost=0.00..0.04 rows=2 width=168) (actual time=0.004..1,462.040 rows=5,360 loops=1)

53.          

SubPlan (for GroupAggregate)

54. 10.720 75.040 ↑ 1.0 1 5,360

Aggregate (cost=10,586.83..10,586.84 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=5,360)

55. 16.080 64.320 ↓ 0.0 0 5,360

GroupAggregate (cost=9,829.82..10,582.82 rows=200 width=36) (actual time=0.010..0.012 rows=0 loops=5,360)

  • Group Key: ("zulu_quebec"(victor_hotel4.uniform_yankee_xray, 'golf_charlie_lima'::text))
56. 21.440 48.240 ↑ 100,000.0 1 5,360

Sort (cost=9,829.82..10,079.82 rows=100,000 width=40) (actual time=0.008..0.009 rows=1 loops=5,360)

  • Sort Key: ("zulu_quebec"(victor_hotel4.uniform_yankee_xray, 'golf_charlie_lima'::text))
  • Sort Method: quicksort Memory: 25kB
57. 21.440 26.800 ↑ 100,000.0 1 5,360

ProjectSet (cost=0.00..525.00 rows=100,000 width=40) (actual time=0.003..0.005 rows=1 loops=5,360)

58. 5.360 5.360 ↓ 0.0 0 5,360

Function Scan on whiskey_mike sierra_tango (cost=0.00..10.00 rows=1,000 width=64) (actual time=0.001..0.001 rows=0 loops=5,360)

59. 21.440 182.240 ↑ 1.0 1 5,360

Aggregate (cost=10,586.83..10,586.84 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=5,360)

60. 37.520 160.800 ↑ 200.0 1 5,360

GroupAggregate (cost=9,829.82..10,582.82 rows=200 width=36) (actual time=0.025..0.030 rows=1 loops=5,360)

  • Group Key: ("zulu_quebec"(victor_hotel5.uniform_yankee_xray, 'golf_charlie_lima'::text))
61. 37.520 123.280 ↑ 33,333.3 3 5,360

Sort (cost=9,829.82..10,079.82 rows=100,000 width=40) (actual time=0.021..0.023 rows=3 loops=5,360)

  • Sort Key: ("zulu_quebec"(victor_hotel5.uniform_yankee_xray, 'golf_charlie_lima'::text))
  • Sort Method: quicksort Memory: 25kB
62. 64.320 85.760 ↑ 33,333.3 3 5,360

ProjectSet (cost=0.00..525.00 rows=100,000 width=40) (actual time=0.007..0.016 rows=3 loops=5,360)

63. 21.440 21.440 ↑ 1,000.0 1 5,360

Function Scan on whiskey_mike zulu_three (cost=0.00..10.00 rows=1,000 width=64) (actual time=0.003..0.004 rows=1 loops=5,360)

64. 32.160 91.120 ↑ 1.0 1 5,360

Aggregate (cost=1,772.51..1,772.52 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=5,360)

65. 42.880 58.960 ↑ 33,333.3 3 5,360

ProjectSet (cost=0.00..522.50 rows=100,000 width=8) (actual time=0.005..0.011 rows=3 loops=5,360)

66. 16.080 16.080 ↑ 1,000.0 1 5,360

Function Scan on whiskey_mike quebec_two (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.003..0.003 rows=1 loops=5,360)

67. 26.800 85.760 ↑ 1.0 1 5,360

Aggregate (cost=2,522.50..2,522.52 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=5,360)

68. 42.880 58.960 ↑ 33,333.3 3 5,360

ProjectSet (cost=0.00..522.50 rows=100,000 width=8) (actual time=0.005..0.011 rows=3 loops=5,360)

69. 16.080 16.080 ↑ 1,000.0 1 5,360

Function Scan on whiskey_mike romeo_alpha (cost=0.00..10.00 rows=1,000 width=32) (actual time=0.002..0.003 rows=1 loops=5,360)

70.          

CTE bravo_yankee

71. 98.748 37,072.016 ↓ 2,680.0 5,360 1

Hash Left Join (cost=0.08..1.16 rows=2 width=788) (actual time=36,967.550..37,072.016 rows=5,360 loops=1)

  • Hash Cond: ((foxtrot_bravo.quebec_three = romeo_foxtrot.quebec_three) AND (foxtrot_bravo.golf_foxtrot_india = romeo_foxtrot.golf_foxtrot_india) AND (foxtrot_bravo.november_india = romeo_foxtrot.november_india))
72. 34,974.746 34,974.746 ↓ 2,680.0 5,360 1

CTE Scan on five_papa foxtrot_bravo (cost=0.00..0.04 rows=2 width=604) (actual time=34,968.623..34,974.746 rows=5,360 loops=1)

73. 8.419 1,998.522 ↓ 2,680.0 5,360 1

Hash (cost=0.04..0.04 rows=2 width=176) (actual time=1,998.521..1,998.522 rows=5,360 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,199kB
74. 1,990.103 1,990.103 ↓ 2,680.0 5,360 1

CTE Scan on lima_zulu romeo_foxtrot (cost=0.00..0.04 rows=2 width=176) (actual time=1,471.429..1,990.103 rows=5,360 loops=1)

75. 37,092.436 37,092.436 ↓ 2,680.0 5,360 1

CTE Scan on bravo_yankee (cost=0.00..0.04 rows=2 width=0) (actual time=36,967.556..37,092.436 rows=5,360 loops=1)