explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wuwy

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 430.029 ↑ 1.0 1 1

Aggregate (cost=100,000,038,011.89..100,000,038,011.90 rows=1 width=8) (actual time=430.029..430.029 rows=1 loops=1)

2.          

CTE duplicate_selected_file

3. 27.481 444.518 ↓ 29.0 29 1

Gather (cost=30,514.53..37,997.79 rows=1 width=16) (actual time=427.438..444.518 rows=29 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 13.708 417.037 ↓ 10.0 10 3

Hash Join (cost=29,514.53..36,997.69 rows=1 width=16) (actual time=416.669..417.037 rows=10 loops=3)

  • Hash Cond: (a."DECLARATION" = d.id)
5. 60.572 402.974 ↑ 1.3 169,428 3

Hash Join (cost=29,490.73..36,417.53 rows=211,945 width=32) (actual time=328.639..402.974 rows=169,428 loops=3)

  • Hash Cond: (a."FILE" = f.id)
6. 17.006 17.006 ↑ 1.3 169,428 3

Parallel Seq Scan on association a (cost=0.00..6,370.45 rows=211,945 width=32) (actual time=0.024..17.006 rows=169,428 loops=3)

7. 127.821 325.396 ↑ 1.0 452,316 3

Hash (cost=23,756.99..23,756.99 rows=458,699 width=16) (actual time=325.396..325.396 rows=452,316 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 25299kB
8. 197.575 197.575 ↑ 1.0 452,316 3

Seq Scan on file f (cost=0.00..23,756.99 rows=458,699 width=16) (actual time=0.027..197.575 rows=452,316 loops=3)

  • Filter: (NOT "toBeDeleted")
9. 0.018 0.355 ↓ 29.0 29 3

Hash (cost=23.79..23.79 rows=1 width=16) (actual time=0.355..0.355 rows=29 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.337 0.337 ↓ 29.0 29 3

Index Scan using declaration_unique_k on declaration d (cost=0.42..23.79 rows=1 width=16) (actual time=0.177..0.337 rows=29 loops=3)

  • Index Cond: (("ORGANIZATION" = '904eb59b-5610-4472-8254-72c487c1305b'::uuid) AND (date = '2018-09-01'::date))
  • Filter: ("NEXT_SERIAL" IS NULL)
11.          

CTE selected_file

12. 430.012 430.012 ↑ 3.5 29 1

CTE Scan on temp (cost=100,000,000,009.80..100,000,000,011.82 rows=101 width=16) (actual time=429.783..430.012 rows=29 loops=1)

13.          

CTE temp

14. 0.020 430.004 ↑ 3.5 29 1

Recursive Union (cost=0.03..100,000,000,009.80 rows=101 width=16) (actual time=429.782..430.004 rows=29 loops=1)

15. 0.002 429.781 ↑ 1.0 1 1

Limit (cost=0.03..0.04 rows=1 width=16) (actual time=429.780..429.781 rows=1 loops=1)

16. 0.016 429.779 ↑ 1.0 1 1

Sort (cost=0.03..0.04 rows=1 width=16) (actual time=429.779..429.779 rows=1 loops=1)

  • Sort Key: duplicate_selected_file.id
  • Sort Method: top-N heapsort Memory: 25kB
17. 429.763 429.763 ↓ 29.0 29 1

CTE Scan on duplicate_selected_file (cost=0.00..0.02 rows=1 width=16) (actual time=427.442..429.763 rows=29 loops=1)

18. 0.029 0.203 ↑ 10.0 1 29

Nested Loop (cost=10,000,000,000.03..10,000,000,000.77 rows=10 width=16) (actual time=0.007..0.007 rows=1 loops=29)

19. 0.000 0.000 ↑ 10.0 1 29

WorkTable Scan on temp f_1 (cost=0.00..0.20 rows=10 width=16) (actual time=0.000..0.000 rows=1 loops=29)

20. 0.000 0.174 ↑ 1.0 1 29

Limit (cost=0.03..0.04 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=29)

21. 0.087 0.174 ↑ 1.0 1 29

Sort (cost=0.03..0.04 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=29)

  • Sort Key: duplicate_selected_file_1.id
  • Sort Method: quicksort Memory: 25kB
22. 0.087 0.087 ↓ 14.0 14 29

CTE Scan on duplicate_selected_file duplicate_selected_file_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.003 rows=14 loops=29)

  • Filter: (id > f_1.id)
  • Rows Removed by Filter: 15
23. 430.021 430.021 ↑ 3.5 29 1

CTE Scan on selected_file (cost=0.00..2.02 rows=101 width=0) (actual time=429.784..430.021 rows=29 loops=1)