explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tk94

Settings
# exclusive inclusive rows x rows loops node
1. 0.380 2,475.882 ↓ 1.7 5,828 1

Append (cost=6,957,011,165.620..6,957,013,553.250 rows=3,451 width=8) (actual time=2,471.994..2,475.882 rows=5,828 loops=1)

2.          

CTE india_alpha

3. 249.789 2,050.395 ↓ 5.1 5,610 1

Gather (cost=34,394.920..6,953,216,129.840 rows=1,110 width=8) (actual time=84.244..2,050.395 rows=5,610 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 2.921 1,800.606 ↓ 4.0 1,870 3 / 3

Nested Loop Semi Join (cost=33,394.920..6,953,215,018.840 rows=462 width=8) (actual time=68.350..1,800.606 rows=1,870 loops=3)

  • Join Filter: (tango.alpha = xray_xray.alpha)
5. 1.796 1,784.765 ↓ 5.6 2,584 3 / 3

Nested Loop Semi Join (cost=33,394.500..6,953,214,753.370 rows=462 width=12) (actual time=68.325..1,784.765 rows=2,584 loops=3)

6. 11.731 88.824 ↑ 1.3 10,655 3 / 3

Merge Join (cost=33,391.350..33,889.530 rows=14,007 width=12) (actual time=67.401..88.824 rows=10,655 loops=3)

  • Merge Cond: (whiskey.quebec = tango.alpha)
7. 13.592 30.046 ↑ 1.3 26,719 3 / 3

Sort (cost=6,699.300..6,783.030 rows=33,492 width=4) (actual time=26.428..30.046 rows=26,719 loops=3)

  • Sort Key: whiskey.quebec
  • Sort Method: quicksort Memory: 2,303kB
8. 16.454 16.454 ↑ 1.3 26,759 3 / 3

Parallel Seq Scan on oscar whiskey (cost=0.000..4,182.120 rows=33,492 width=4) (actual time=0.005..16.454 rows=26,759 loops=3)

  • Filter: (NOT bravo_two)
  • Rows Removed by Filter: 3,756
9. 20.593 47.047 ↓ 1.1 42,447 3 / 3

Sort (cost=26,692.050..26,787.940 rows=38,355 width=8) (actual time=40.806..47.047 rows=42,447 loops=3)

  • Sort Key: tango.alpha
  • Sort Method: quicksort Memory: 3,526kB
10. 26.454 26.454 ↓ 1.1 42,448 3 / 3

Index Scan using foxtrot_yankee on mike_delta tango (cost=0.430..23,771.870 rows=38,355 width=8) (actual time=0.051..26.454 rows=42,448 loops=3)

  • Index Cond: (victor_juliet = 3)
11. 10.974 1,694.145 ↓ 0.0 0 31,965 / 3

Nested Loop (cost=3.150..951.920 rows=51,242,518 width=4) (actual time=0.159..0.159 rows=0 loops=31,965)

12. 53.650 1,672.835 ↓ 0.0 0 31,965 / 3

Nested Loop (cost=2.720..593.520 rows=222 width=36) (actual time=0.157..0.157 rows=0 loops=31,965)

13. 37.174 1,459.735 ↑ 28.6 5 31,965 / 3

Nested Loop (cost=2.290..481.620 rows=143 width=20) (actual time=0.060..0.137 rows=5 loops=31,965)

14. 90.449 1,214.670 ↑ 20.4 7 31,965 / 3

Nested Loop (cost=1.860..408.690 rows=143 width=8) (actual time=0.036..0.114 rows=7 loops=31,965)

15. 101.104 916.330 ↑ 20.4 7 31,965 / 3

Nested Loop (cost=1.440..343.290 rows=143 width=8) (actual time=0.031..0.086 rows=7 loops=31,965)

16. 124.531 607.335 ↑ 20.4 7 31,965 / 3

Nested Loop (cost=1.000..275.130 rows=143 width=8) (actual time=0.027..0.057 rows=7 loops=31,965)

17. 127.860 127.860 ↑ 37.4 11 31,965 / 3

Index Only Scan using mike_juliet on mike_india juliet_romeo (cost=0.560..78.810 rows=411 width=8) (actual time=0.008..0.012 rows=11 loops=31,965)

  • Index Cond: (charlie_two = tango.quebec)
  • Heap Fetches: 121,221
18. 354.944 354.944 ↑ 1.0 1 354,944 / 3

Index Scan using kilo_romeo on foxtrot_victor papa (cost=0.440..0.470 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=354,944)

  • Index Cond: (zulu_juliet = juliet_romeo.quebec)
19. 207.891 207.891 ↑ 1.0 1 207,891 / 3

Index Scan using six on kilo_zulu sierra (cost=0.430..0.480 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=207,891)

  • Index Cond: (quebec = papa.charlie_echo_whiskey)
20. 207.891 207.891 ↑ 1.0 1 207,891 / 3

Index Scan using zulu_papa on xray_lima uniform_india (cost=0.430..0.460 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=207,891)

  • Index Cond: (quebec = sierra.charlie_xray)
21. 207.891 207.891 ↑ 1.0 1 207,891 / 3

Index Scan using foxtrot_oscar on five_bravo_romeo xray_seven (cost=0.430..0.510 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=207,891)

  • Index Cond: (quebec = uniform_india.seven_sierra)
22. 159.450 159.450 ↓ 0.0 0 159,450 / 3

Index Only Scan using foxtrot_xray on bravo_charlie four_xray (cost=0.430..0.760 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=159,450)

  • Index Cond: ((two_victor = xray_seven.victor_november) AND (five_bravo_alpha = 4))
  • Heap Fetches: 2,624
23. 10.336 10.336 ↑ 40.0 1 7,752 / 3

Index Only Scan using mike_hotel on five_bravo_romeo delta (cost=0.430..1.210 rows=40 width=16) (actual time=0.004..0.004 rows=1 loops=7,752)

  • Index Cond: (victor_november = xray_seven.victor_november)
  • Heap Fetches: 2,624
24. 12.920 12.920 ↑ 1.0 1 7,752 / 3

Index Scan using zulu_zulu on golf xray_xray (cost=0.420..0.560 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=7,752)

  • Index Cond: ((alpha = whiskey.quebec) AND (hotel_bravo = 2))
  • Filter: ((kilo_oscar <= CURRENT_TIMESTAMP) AND ((four_india_whiskey five_romeo NULL) OR (four_india_whiskey >= CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 1
25.          

CTE four_india_delta

26. 2.042 412.579 ↑ 149.1 218 1

Merge Semi Join (cost=34,392.800..3,795,035.780 rows=32,506 width=8) (actual time=218.600..412.579 rows=218 loops=1)

  • Merge Cond: (november1.alpha = seven_three1.alpha)
27. 0.000 338.591 ↑ 7.4 4,376 1

Gather Merge (cost=34,392.380..3,786,582.310 rows=32,506 width=12) (actual time=96.139..338.591 rows=4,376 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
28. 3.622 342.182 ↑ 9.3 1,459 3 / 3

Nested Loop Anti Join (cost=33,392.360..3,781,830.290 rows=13,544 width=12) (actual time=72.544..342.182 rows=1,459 loops=3)

29. 10.350 82.840 ↑ 1.3 10,655 3 / 3

Merge Join (cost=33,391.350..33,889.530 rows=14,007 width=12) (actual time=64.083..82.840 rows=10,655 loops=3)

  • Merge Cond: (hotel_foxtrot1.quebec = november1.alpha)
30. 12.514 28.295 ↑ 1.3 26,720 3 / 3

Sort (cost=6,699.300..6,783.030 rows=33,492 width=4) (actual time=25.013..28.295 rows=26,720 loops=3)

  • Sort Key: hotel_foxtrot1.quebec
  • Sort Method: quicksort Memory: 2,304kB
31. 15.781 15.781 ↑ 1.3 26,759 3 / 3

Parallel Seq Scan on oscar juliet_six (cost=0.000..4,182.120 rows=33,492 width=4) (actual time=0.005..15.781 rows=26,759 loops=3)

  • Filter: (NOT bravo_two)
  • Rows Removed by Filter: 3,756
32. 18.847 44.195 ↓ 1.1 42,448 3 / 3

Sort (cost=26,692.050..26,787.940 rows=38,355 width=8) (actual time=38.896..44.195 rows=42,448 loops=3)

  • Sort Key: november1.alpha
  • Sort Method: quicksort Memory: 3,526kB
33. 25.348 25.348 ↓ 1.1 42,448 3 / 3

Index Scan using foxtrot_yankee on mike_delta india_zulu (cost=0.430..23,771.870 rows=38,355 width=8) (actual time=0.047..25.348 rows=42,448 loops=3)

  • Index Cond: (victor_juliet = 3)
34. 13.373 255.720 ↑ 143.0 1 31,965 / 3

Nested Loop (cost=1.000..275.130 rows=143 width=4) (actual time=0.024..0.024 rows=1 loops=31,965)

35. 85.240 85.240 ↑ 82.2 5 31,965 / 3

Index Only Scan using mike_juliet on mike_india five_papa (cost=0.560..78.810 rows=411 width=8) (actual time=0.007..0.008 rows=5 loops=31,965)

  • Index Cond: (charlie_two = november1.quebec)
  • Heap Fetches: 48,122
36. 157.107 157.107 ↓ 0.0 0 157,107 / 3

Index Only Scan using kilo_romeo on foxtrot_victor uniform_zulu (cost=0.440..0.470 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=157,107)

  • Index Cond: (zulu_juliet = juliet_romeo2.quebec)
  • Heap Fetches: 10,098
37. 71.946 71.946 ↓ 1.6 22,084 1

Index Scan using zulu_zulu on golf two_xray (cost=0.420..8,340.350 rows=13,978 width=4) (actual time=0.041..71.946 rows=22,084 loops=1)

  • Index Cond: (hotel_bravo = 2)
  • Filter: ((kilo_oscar <= CURRENT_TIMESTAMP) AND ((four_india_whiskey five_romeo NULL) OR (four_india_whiskey >= CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 45,271
38. 0.553 2,473.338 ↓ 28.1 5,610 1

Result (cost=0.000..1,178.560 rows=200 width=8) (actual time=2,471.994..2,473.338 rows=5,610 loops=1)

39. 4.635 2,472.785 ↓ 28.1 5,610 1

HashSetOp Except (cost=0.000..1,176.560 rows=200 width=12) (actual time=2,471.992..2,472.785 rows=5,610 loops=1)

40. 0.846 2,468.150 ↑ 5.8 5,828 1

Append (cost=0.000..1,008.480 rows=33,616 width=12) (actual time=84.248..2,468.150 rows=5,828 loops=1)

41. 1.319 2,054.624 ↓ 5.1 5,610 1

Subquery Scan on victor_victor (cost=0.000..33.300 rows=1,110 width=12) (actual time=84.248..2,054.624 rows=5,610 loops=1)

42. 2,053.305 2,053.305 ↓ 5.1 5,610 1

CTE Scan on india_alpha (cost=0.000..22.200 rows=1,110 width=8) (actual time=84.247..2,053.305 rows=5,610 loops=1)

43. 0.034 412.680 ↑ 149.1 218 1

Subquery Scan on echo (cost=0.000..975.180 rows=32,506 width=12) (actual time=218.603..412.680 rows=218 loops=1)

44. 412.646 412.646 ↑ 149.1 218 1

CTE Scan on four_india_delta (cost=0.000..650.120 rows=32,506 width=8) (actual time=218.602..412.646 rows=218 loops=1)

45. 0.020 2.164 ↑ 14.9 218 1

Result (cost=0.000..1,209.070 rows=3,251 width=8) (actual time=2.120..2.164 rows=218 loops=1)

46. 0.665 2.144 ↑ 14.9 218 1

HashSetOp Except (cost=0.000..1,176.560 rows=3,251 width=12) (actual time=2.119..2.144 rows=218 loops=1)

47. 0.385 1.479 ↑ 5.8 5,828 1

Append (cost=0.000..1,008.480 rows=33,616 width=12) (actual time=0.002..1.479 rows=5,828 loops=1)

48. 0.025 0.048 ↑ 149.1 218 1

Subquery Scan on bravo_seven (cost=0.000..975.180 rows=32,506 width=12) (actual time=0.002..0.048 rows=218 loops=1)

49. 0.023 0.023 ↑ 149.1 218 1

CTE Scan on four_india_delta charlie_echo_seven (cost=0.000..650.120 rows=32,506 width=8) (actual time=0.001..0.023 rows=218 loops=1)

50. 0.579 1.046 ↓ 5.1 5,610 1

Subquery Scan on four_golf (cost=0.000..33.300 rows=1,110 width=12) (actual time=0.002..1.046 rows=5,610 loops=1)

51. 0.467 0.467 ↓ 5.1 5,610 1

CTE Scan on india_alpha two_six (cost=0.000..22.200 rows=1,110 width=8) (actual time=0.001..0.467 rows=5,610 loops=1)

Planning time : 6.009 ms
Execution time : 2,538.803 ms