explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zCkK : BI

Settings
# exclusive inclusive rows x rows loops node
1. 968.935 30,240.580 ↓ 32.3 1,002,296 1

WindowAgg (cost=1,729,778.00..1,729,933.01 rows=31,002 width=320) (actual time=29,065.240..30,240.580 rows=1,002,296 loops=1)

  • Execution Time: 30,533.842 ms +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.          

CTE romeo_foxtrot

3. 8.514 321.353 ↓ 17.9 1,074,538 1

Gather (cost=1,007.64..233,866.69 rows=60,055 width=62) (actual time=0.861..321.353 rows=1,074,538 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 85.181 312.839 ↑ 65.8 537,269 2 / 2

Result (cost=7.64..226,861.19 rows=35,326,000 width=62) (actual time=0.142..312.839 rows=537,269 loops=2)

5. 139.760 227.658 ↑ 65.8 537,269 2 / 2

ProjectSet (cost=7.64..67,894.19 rows=35,326,000 width=66) (actual time=0.141..227.658 rows=537,269 loops=2)

6. 9.030 87.898 ↓ 1.0 36,122 2 / 2

Hash Left Join (cost=7.64..14,816.88 rows=35,326 width=58) (actual time=0.132..87.898 rows=36,122 loops=2)

  • Hash Cond: (whiskey_oscar.kilo_sierra = five_four.quebec_seven_victor_foxtrot)
7. 78.784 78.784 ↓ 1.0 36,122 2 / 2

Parallel Seq Scan on whiskey_oscar (cost=0.00..14,789.53 rows=35,326 width=36) (actual time=0.014..78.784 rows=36,122 loops=2)

  • Filter: ((NOT zulu_golf) AND (zulu_echo five_romeo NOT NULL) AND (quebec_five = 2) AND ((whiskey_alpha = 'golf'::whiskey_alpha) OR (whiskey_alpha = 'alpha_mike'::whiskey_alpha)))
  • Rows Removed by Filter: 112,282
8. 0.031 0.084 ↓ 1.2 119 2 / 2

Hash (cost=7.29..7.29 rows=98 width=30) (actual time=0.083..0.084 rows=119 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
9. 0.053 0.053 ↓ 1.2 119 2 / 2

Seq Scan on five_four (cost=0.00..7.29 rows=98 width=30) (actual time=0.018..0.053 rows=119 loops=2)

10.          

CTE seven

11. 1,386.351 12,917.207 ↑ 4.4 730,266 1

GroupAggregate (cost=418,616.64..483,534.08 rows=3,240,875 width=56) (actual time=10,750.085..12,917.207 rows=730,266 loops=1)

  • Group Key: xray_tango.uniform, ((papa('kilo_lima'::text, xray_tango.charlie))::date)
12. 3,794.725 11,530.856 ↑ 1.0 3,819,192 1

Sort (cost=418,616.64..420,565.17 rows=3,897,045 width=40) (actual time=10,750.041..11,530.856 rows=3,819,192 loops=1)

  • Sort Key: xray_tango.uniform, ((papa('kilo_lima'::text, xray_tango.charlie))::date)
  • Sort Method: external merge Disk: 186,888kB
13. 1,575.542 7,736.131 ↑ 1.0 3,819,192 1

Hash Left Join (cost=10,189.65..257,179.94 rows=3,897,045 width=40) (actual time=82.272..7,736.131 rows=3,819,192 loops=1)

  • Hash Cond: (xray_tango.quebec_seven_victor_foxtrot = tango.juliet)
14. 6,079.136 6,079.136 ↑ 1.0 3,818,899 1

Seq Scan on xray_tango (cost=0.00..221,623.60 rows=3,897,045 width=44) (actual time=0.064..6,079.136 rows=3,818,899 loops=1)

  • Filter: ((NOT zulu_golf) AND ((victor_two AND bravo_sierra) OR (five_uniform > 0)) AND (whiskey_romeo < 500))
  • Rows Removed by Filter: 1,497,636
15. 17.141 81.453 ↑ 17.5 15,705 1

Hash (cost=9,229.76..9,229.76 rows=274,254 width=16) (actual time=81.453..81.453 rows=15,705 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 4,833kB
16. 64.312 64.312 ↓ 1.1 288,171 1

Seq Scan on tango (cost=0.00..9,229.76 rows=274,254 width=16) (actual time=0.007..64.312 rows=288,171 loops=1)

17.          

CTE india_tango

18. 28.704 105.505 ↑ 1.2 41,091 1

HashAggregate (cost=9,682.52..9,962.86 rows=50,972 width=28) (actual time=93.789..105.505 rows=41,091 loops=1)

  • Group Key: bravo_papa1.uniform, (papa('kilo_lima'::text, bravo_papa1.quebec_seven_victor_alpha))::date
19. 14.934 76.801 ↑ 1.2 41,753 1

Hash Join (cost=1.17..9,453.14 rows=50,972 width=20) (actual time=0.072..76.801 rows=41,753 loops=1)

  • Hash Cond: (bravo_papa1.kilo_romeo = xray_bravo.quebec_seven_victor_foxtrot)
20. 61.843 61.843 ↑ 1.3 41,823 1

Seq Scan on tango three (cost=0.00..9,366.89 rows=52,932 width=28) (actual time=0.033..61.843 rows=41,823 loops=1)

  • Filter: ((juliet five_romeo NULL) AND (six <> 0))
  • Rows Removed by Filter: 246,348
21. 0.007 0.024 ↑ 1.0 26 1

Hash (cost=1.08..1.08 rows=26 width=8) (actual time=0.024..0.024 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
22. 0.017 0.017 ↑ 1.0 26 1

Seq Scan on bravo_whiskey xray_bravo (cost=0.00..1.08 rows=26 width=8) (actual time=0.012..0.017 rows=26 loops=1)

  • Filter: (NOT romeo_tango)
  • Rows Removed by Filter: 1
23.          

CTE oscar

24. 2,213.207 17,926.239 ↑ 5.8 1,074,538 1

Merge Left Join (cost=171,080.28..275,519.09 rows=6,200,449 width=164) (actual time=15,019.593..17,926.239 rows=1,074,538 loops=1)

  • Merge Cond: ((romeo_foxtrot.quebec_seven_victor_foxtrot = seven.uniform) AND (romeo_foxtrot.alpha_india = seven.mike))
25. 284.884 1,949.159 ↓ 14.0 1,074,538 1

Merge Left Join (cost=2,416.55..2,851.00 rows=76,528 width=80) (actual time=1,463.078..1,949.159 rows=1,074,538 loops=1)

  • Merge Cond: ((romeo_foxtrot.quebec_seven_victor_foxtrot = india_tango.uniform) AND (romeo_foxtrot.alpha_india = india_tango.victor_papa))
26. 853.142 1,522.306 ↓ 17.9 1,074,538 1

Sort (cost=1,313.64..1,343.67 rows=60,055 width=72) (actual time=1,328.101..1,522.306 rows=1,074,538 loops=1)

  • Sort Key: romeo_foxtrot.quebec_seven_victor_foxtrot, romeo_foxtrot.alpha_india
  • Sort Method: external merge Disk: 79,888kB
27. 669.164 669.164 ↓ 17.9 1,074,538 1

CTE Scan on romeo_foxtrot (cost=0.00..360.33 rows=60,055 width=72) (actual time=0.863..669.164 rows=1,074,538 loops=1)

28. 21.517 141.969 ↑ 1.2 41,084 1

Sort (cost=1,102.90..1,128.39 rows=50,972 width=20) (actual time=134.931..141.969 rows=41,084 loops=1)

  • Sort Key: india_tango.uniform, india_tango.victor_papa
  • Sort Method: quicksort Memory: 4,747kB
29. 120.452 120.452 ↑ 1.2 41,091 1

CTE Scan on india_tango (cost=0.00..305.83 rows=50,972 width=20) (actual time=93.793..120.452 rows=41,091 loops=1)

30. 95.162 13,763.873 ↑ 4.4 729,516 1

Materialize (cost=168,663.74..171,904.61 rows=3,240,875 width=56) (actual time=13,555.853..13,763.873 rows=729,516 loops=1)

31. 428.263 13,668.711 ↑ 4.4 729,516 1

Sort (cost=168,663.74..170,284.18 rows=3,240,875 width=56) (actual time=13,555.849..13,668.711 rows=729,516 loops=1)

  • Sort Key: seven.uniform, seven.mike
  • Sort Method: external merge Disk: 41,552kB
32. 13,240.448 13,240.448 ↑ 4.4 730,266 1

CTE Scan on seven (cost=0.00..19,445.25 rows=3,240,875 width=56) (actual time=10,750.090..13,240.448 rows=730,266 loops=1)

33.          

CTE quebec_mike

34. 4,332.968 24,555.266 ↑ 5.8 1,074,538 1

WindowAgg (cost=540,419.26..608,624.20 rows=6,200,449 width=292) (actual time=19,982.505..24,555.266 rows=1,074,538 loops=1)

35. 1,003.742 20,222.298 ↑ 5.8 1,074,538 1

Sort (cost=540,419.26..543,519.49 rows=6,200,449 width=164) (actual time=19,982.475..20,222.298 rows=1,074,538 loops=1)

  • Sort Key: oscar.quebec_seven_victor_foxtrot, oscar.alpha_india
  • Sort Method: external merge Disk: 178,072kB
36. 19,218.556 19,218.556 ↑ 5.8 1,074,538 1

CTE Scan on oscar (cost=0.00..37,202.69 rows=6,200,449 width=164) (actual time=15,019.598..19,218.556 rows=1,074,538 loops=1)

37.          

CTE india_foxtrot

38. 26,500.913 26,500.913 ↑ 5.8 1,074,538 1

CTE Scan on quebec_mike (cost=0.00..74,405.39 rows=6,200,449 width=332) (actual time=19,982.514..26,500.913 rows=1,074,538 loops=1)

39. 1,005.293 29,271.645 ↓ 32.3 1,002,296 1

Sort (cost=43,865.70..43,881.20 rows=31,002 width=288) (actual time=29,065.222..29,271.645 rows=1,002,296 loops=1)

  • Sort Key: india_foxtrot.quebec_seven_victor_foxtrot, india_foxtrot.alpha_india
  • Sort Method: external merge Disk: 222,456kB
40. 28,266.352 28,266.352 ↓ 32.3 1,002,296 1

CTE Scan on india_foxtrot (cost=0.00..43,403.14 rows=31,002 width=288) (actual time=19,982.519..28,266.352 rows=1,002,296 loops=1)

  • Filter: ((alpha_india >= 'bravo_yankee'::date) AND (alpha_india <= 'november'::date))
  • Rows Removed by Filter: 72,242
Planning time : 4.740 ms