explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0WgC

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 6,034.453 ↑ 6,299,786,892.0 1 1

Subquery Scan on temptable (cost=21,607,801.41..19,015,652,370.80 rows=6,299,786,892 width=426) (actual time=6,034.440..6,034.453 rows=1 loops=1)

2. 79.808 6,034.441 ↑ 6,299,786,892.0 1 1

Merge Left Join (cost=21,607,801.41..18,921,155,567.42 rows=6,299,786,892 width=418) (actual time=6,034.435..6,034.441 rows=1 loops=1)

  • Merge Cond: ((t1.prodbeg_src)::text = (t2.prodbeg_src)::text)
  • Filter: (t2.c_documentid IS NULL)
  • Rows Removed by Filter: 53745
3.          

CTE t1

4. 24.511 852.050 ↑ 69.6 53,746 1

WindowAgg (cost=978,618.19..1,044,091.61 rows=3,741,338 width=120) (actual time=818.960..852.050 rows=53,746 loops=1)

5. 28.748 827.539 ↑ 69.6 53,746 1

Sort (cost=978,618.19..987,971.54 rows=3,741,338 width=104) (actual time=818.945..827.539 rows=53,746 loops=1)

  • Sort Key: a.rno
  • Sort Method: external merge Disk: 3008kB
6. 54.210 798.791 ↑ 69.6 53,746 1

Merge Join (cost=15,687.80..90,791.52 rows=3,741,338 width=104) (actual time=643.067..798.791 rows=53,746 loops=1)

  • Merge Cond: ((lower((a.prodbeg)::text)) = (lower((b.c_pagealias)::text)))
7. 319.193 391.022 ↓ 4.6 53,746 1

Sort (cost=6,745.89..6,775.09 rows=11,680 width=92) (actual time=356.858..391.022 rows=53,746 loops=1)

  • Sort Key: (lower((a.prodbeg)::text))
  • Sort Method: external merge Disk: 3008kB
8. 71.829 71.829 ↓ 4.6 53,746 1

Seq Scan on temp_dat_02112020092448_72074 a (cost=0.00..5,956.80 rows=11,680 width=92) (actual time=0.304..71.829 rows=53,746 loops=1)

9. 7.443 353.559 ↑ 1.0 64,044 1

Materialize (cost=8,941.91..9,262.23 rows=64,064 width=12) (actual time=284.756..353.559 rows=64,044 loops=1)

10. 303.296 346.116 ↑ 1.0 64,044 1

Sort (cost=8,941.91..9,102.07 rows=64,064 width=12) (actual time=284.750..346.116 rows=64,044 loops=1)

  • Sort Key: (lower((b.c_pagealias)::text))
  • Sort Method: external merge Disk: 2248kB
11. 42.820 42.820 ↑ 1.0 64,064 1

Seq Scan on t_e20so1_pages b (cost=0.00..3,185.64 rows=64,064 width=12) (actual time=0.033..42.820 rows=64,064 loops=1)

12.          

CTE t2

13. 354.007 4,165.964 ↑ 58.5 1,152,293 1

Merge Full Join (cost=205,913.94..1,555,980.10 rows=67,353,304 width=50) (actual time=2,965.988..4,165.964 rows=1,152,293 loops=1)

  • Merge Cond: ((lower((a_1.prodbeg)::text)) = (lower((c.c_docdisplayid)::text)))
14. 246.292 282.663 ↓ 4.6 53,746 1

Sort (cost=6,745.89..6,775.09 rows=11,680 width=42) (actual time=249.052..282.663 rows=53,746 loops=1)

  • Sort Key: (lower((a_1.prodbeg)::text))
  • Sort Method: external merge Disk: 1904kB
15. 36.371 36.371 ↓ 4.6 53,746 1

Seq Scan on temp_dat_02112020092448_72074 a_1 (cost=0.00..5,956.80 rows=11,680 width=42) (actual time=0.073..36.371 rows=53,746 loops=1)

16. 126.763 3,529.294 ↑ 1.0 1,152,292 1

Materialize (cost=199,168.05..204,934.60 rows=1,153,310 width=20) (actual time=2,716.875..3,529.294 rows=1,152,292 loops=1)

17. 2,542.865 3,402.531 ↑ 1.0 1,152,292 1

Sort (cost=199,168.05..202,051.33 rows=1,153,310 width=20) (actual time=2,716.868..3,402.531 rows=1,152,292 loops=1)

  • Sort Key: (lower((c.c_docdisplayid)::text))
  • Sort Method: external merge Disk: 49760kB
18. 510.146 859.666 ↑ 1.0 1,152,292 1

Result (cost=0.00..41,483.33 rows=1,153,310 width=20) (actual time=0.082..859.666 rows=1,152,292 loops=1)

19. 91.502 349.520 ↑ 1.0 1,152,292 1

Append (cost=0.00..41,483.33 rows=1,153,310 width=20) (actual time=0.063..349.520 rows=1,152,292 loops=1)

20. 0.060 0.060 ↑ 1.0 8 1

Seq Scan on t_e20so1_document_datastore_1 c (cost=0.00..1.08 rows=8 width=24) (actual time=0.057..0.060 rows=8 loops=1)

21. 6.501 6.501 ↑ 1.0 28,869 1

Index Only Scan using t_e20so1_document_datastore_11_c_docdisplayid_c_documentid_idx on t_e20so1_document_datastore_11 c_1 (cost=0.41..673.25 rows=28,869 width=32) (actual time=0.117..6.501 rows=28,869 loops=1)

  • Heap Fetches: 0
22. 249.959 249.959 ↑ 1.0 1,117,001 1

Index Only Scan using t_e20so1_document_datastore_12_c_docdisplayid_c_documentid_idx on t_e20so1_document_datastore_12 c_2 (cost=0.43..34,878.46 rows=1,117,989 width=20) (actual time=0.154..249.959 rows=1,117,001 loops=1)

  • Heap Fetches: 181
23. 1.261 1.261 ↑ 1.0 5,948 1

Index Only Scan using t_e20so1_document_datastore_13_c_docdisplayid_c_documentid_idx on t_e20so1_document_datastore_13 c_3 (cost=0.28..140.10 rows=5,948 width=32) (actual time=0.076..1.261 rows=5,948 loops=1)

  • Heap Fetches: 0
24. 0.168 0.168 ↑ 1.0 430 1

Index Only Scan using t_e20so1_document_datastore_14_c_docdisplayid_c_documentid_idx on t_e20so1_document_datastore_14 c_4 (cost=0.27..12.22 rows=430 width=33) (actual time=0.083..0.168 rows=430 loops=1)

  • Heap Fetches: 0
25. 0.051 0.051 ↑ 1.0 36 1

Seq Scan on t_e20so1_document_datastore_15 c_5 (cost=0.00..1.36 rows=36 width=24) (actual time=0.042..0.051 rows=36 loops=1)

26. 0.018 0.018 ↓ 0.0 0 1

Seq Scan on t_e20so1_document_datastore_default c_6 (cost=0.00..10.30 rows=30 width=226) (actual time=0.018..0.018 rows=0 loops=1)

27. 124.866 1,012.083 ↑ 69.6 53,746 1

Sort (cost=2,161,050.63..2,170,403.98 rows=3,741,338 width=418) (actual time=965.891..1,012.083 rows=53,746 loops=1)

  • Sort Key: t1.prodbeg_src
  • Sort Method: external merge Disk: 3448kB
28. 887.217 887.217 ↑ 69.6 53,746 1

CTE Scan on t1 (cost=0.00..74,826.76 rows=3,741,338 width=418) (actual time=818.964..887.217 rows=53,746 loops=1)

29. 8.787 4,942.550 ↑ 1,253.2 53,747 1

Materialize (cost=16,846,679.07..17,183,445.59 rows=67,353,304 width=50) (actual time=4,890.983..4,942.550 rows=53,747 loops=1)

30. 426.947 4,933.763 ↑ 1,253.2 53,747 1

Sort (cost=16,846,679.07..17,015,062.33 rows=67,353,304 width=50) (actual time=4,890.978..4,933.763 rows=53,747 loops=1)

  • Sort Key: t2.prodbeg_src
  • Sort Method: external merge Disk: 21160kB
31. 4,506.816 4,506.816 ↑ 58.5 1,152,293 1

CTE Scan on t2 (cost=0.00..1,347,066.08 rows=67,353,304 width=50) (actual time=2,965.997..4,506.816 rows=1,152,293 loops=1)

Planning time : 0.903 ms