explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LlbD

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 35.795 ↑ 8.1 468 1

HashAggregate (cost=7,402.58..7,459.40 rows=3,788 width=56) (actual time=35.501..35.795 rows=468 loops=1)

  • Group Key: fc."FILE", (lower(((unnest(r.tags)))::text))
2.          

CTE selected_file

3. 0.005 0.156 ↓ 5.0 5 1

Unique (cost=32.01..32.02 rows=1 width=16) (actual time=0.149..0.156 rows=5 loops=1)

4. 0.021 0.151 ↓ 5.0 5 1

Sort (cost=32.01..32.02 rows=1 width=16) (actual time=0.148..0.151 rows=5 loops=1)

  • Sort Key: f_2.id
  • Sort Method: quicksort Memory: 25kB
5. 0.005 0.130 ↓ 5.0 5 1

Nested Loop (cost=0.42..32.00 rows=1 width=16) (actual time=0.062..0.130 rows=5 loops=1)

6. 0.005 0.085 ↓ 5.0 5 1

Nested Loop (cost=0.42..31.82 rows=1 width=16) (actual time=0.050..0.085 rows=5 loops=1)

7. 0.045 0.045 ↓ 5.0 5 1

Index Scan using declaration_unique_k on declaration d (cost=0.42..23.79 rows=1 width=16) (actual time=0.035..0.045 rows=5 loops=1)

  • Index Cond: (("ORGANIZATION" = '669b8235-cab3-471e-a741-f029f536fe0c'::uuid) AND (date = '2018-03-01'::date))
  • Filter: ("NEXT_SERIAL" IS NULL)
8. 0.035 0.035 ↑ 1.0 1 5

Index Scan using "association_DECLARATION_idx" on association a (cost=0.00..8.02 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=5)

  • Index Cond: ("DECLARATION" = d.id)
9. 0.040 0.040 ↑ 1.0 1 5

Index Scan using file_id_idx on file f_2 (cost=0.00..0.18 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=5)

  • Index Cond: (id = a."FILE")
  • Filter: (NOT "toBeDeleted")
10. 10.415 35.141 ↑ 80.9 468 1

Append (cost=3,576.12..7,086.49 rows=37,875 width=56) (actual time=21.650..35.141 rows=468 loops=1)

11. 2.810 24.726 ↑ 78.0 468 1

GroupAggregate (cost=3,576.12..4,488.62 rows=36,500 width=56) (actual time=21.649..24.726 rows=468 loops=1)

  • Group Key: fc."FILE", (lower(((unnest(r.tags)))::text))
12. 5.434 21.916 ↑ 13.0 2,806 1

Sort (cost=3,576.12..3,667.37 rows=36,500 width=64) (actual time=21.634..21.916 rows=2,806 loops=1)

  • Sort Key: fc."FILE", (lower(((unnest(r.tags)))::text))
  • Sort Method: quicksort Memory: 316kB
13. 2.709 16.482 ↑ 13.0 2,806 1

Result (cost=0.56..810.22 rows=36,500 width=64) (actual time=0.208..16.482 rows=2,806 loops=1)

14. 3.205 13.773 ↑ 13.0 2,806 1

ProjectSet (cost=0.56..262.72 rows=36,500 width=64) (actual time=0.200..13.773 rows=2,806 loops=1)

15. 10.568 10.568 ↓ 6.9 2,530 1

Nested Loop (cost=0.56..77.49 rows=365 width=64) (actual time=0.194..10.568 rows=2,530 loops=1)

16. 0.053 35.919 ↑ 80.9 468 1

Append (cost=3,576.12..7,086.49 rows=37,875 width=56) (actual time=22.659..35.919 rows=468 loops=1)

17. 2.703 25.618 ↑ 78.0 468 1

GroupAggregate (cost=3,576.12..4,488.62 rows=36,500 width=56) (actual time=22.659..25.618 rows=468 loops=1)

  • Group Key: fc."FILE", (lower(((unnest(r.tags)))::text))
18. 5.546 22.915 ↑ 13.0 2,806 1

Sort (cost=3,576.12..3,667.37 rows=36,500 width=64) (actual time=22.643..22.915 rows=2,806 loops=1)

  • Sort Key: fc."FILE", (lower(((unnest(r.tags)))::text))
  • Sort Method: quicksort Memory: 316kB
19. 3.058 17.369 ↑ 13.0 2,806 1

Result (cost=0.56..810.22 rows=36,500 width=64) (actual time=0.209..17.369 rows=2,806 loops=1)

20. 3.453 14.311 ↑ 13.0 2,806 1

ProjectSet (cost=0.56..262.72 rows=36,500 width=64) (actual time=0.200..14.311 rows=2,806 loops=1)

21. 4.079 10.858 ↓ 6.9 2,530 1

Nested Loop (cost=0.56..77.49 rows=365 width=64) (actual time=0.194..10.858 rows=2,530 loops=1)

22. 0.503 1.719 ↓ 6.9 2,530 1

Nested Loop (cost=0.56..70.61 rows=365 width=48) (actual time=0.183..1.719 rows=2,530 loops=1)

23. 0.161 0.161 ↓ 5.0 5 1

CTE Scan on selected_file f (cost=0.00..0.02 rows=1 width=16) (actual time=0.150..0.161 rows=5 loops=1)

24. 1.055 1.055 ↓ 1.4 506 5

Index Only Scan using "file_controls_FILE_CONTROL_REVISION_unique_k" on file_controls fc (cost=0.56..66.94 rows=365 width=48) (actual time=0.021..0.211 rows=506 loops=5)

  • Index Cond: ("FILE" = f.id)
  • Heap Fetches: 0
25. 5.060 5.060 ↑ 1.0 1 2,530

Index Scan using revision_id_idx on revision r (cost=0.00..0.02 rows=1 width=48) (actual time=0.002..0.002 rows=1 loops=2,530)

  • Index Cond: (id = fc."REVISION")
26. 0.001 10.248 ↓ 0.0 0 1

GroupAggregate (cost=2,174.49..2,219.12 rows=1,375 width=56) (actual time=10.247..10.248 rows=0 loops=1)

  • Group Key: e."FILE", (lower(((unnest(r_1.tags)))::text))
27. 0.004 10.247 ↓ 0.0 0 1

Sort (cost=2,174.49..2,180.49 rows=2,400 width=64) (actual time=10.247..10.247 rows=0 loops=1)

  • Sort Key: e."FILE", (lower(((unnest(r_1.tags)))::text))
  • Sort Method: quicksort Memory: 25kB
28. 0.001 10.243 ↓ 0.0 0 1

Result (cost=22.08..2,039.75 rows=2,400 width=64) (actual time=10.243..10.243 rows=0 loops=1)

29. 0.000 10.242 ↓ 0.0 0 1

ProjectSet (cost=22.08..2,003.75 rows=2,400 width=64) (actual time=10.242..10.242 rows=0 loops=1)

30. 0.000 10.242 ↓ 0.0 0 1

Nested Loop (cost=22.08..1,991.57 rows=24 width=64) (actual time=10.241..10.242 rows=0 loops=1)

31. 1.044 2.772 ↓ 5.0 3,747 1

Nested Loop (cost=22.08..151.96 rows=749 width=48) (actual time=0.041..2.772 rows=3,747 loops=1)

32. 0.003 0.003 ↓ 5.0 5 1

CTE Scan on selected_file f_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.003 rows=5 loops=1)

33. 1.330 1.725 ↑ 1.0 749 5

Bitmap Heap Scan on "error_669b8235-cab3-471e-a741-f029f536fe0c_201803" e (cost=22.08..144.45 rows=749 width=48) (actual time=0.090..0.345 rows=749 loops=5)

  • Recheck Cond: ("FILE" = f_1.id)
  • Heap Blocks: exact=123
34. 0.395 0.395 ↑ 1.0 749 5

Bitmap Index Scan on "error_669b8235-cab3-471e-a741-f029f536fe0c_201803_FILE_idx" (cost=0.00..21.90 rows=749 width=0) (actual time=0.079..0.079 rows=749 loops=5)

  • Index Cond: ("FILE" = f_1.id)
35. 7.494 7.494 ↓ 0.0 0 3,747

Index Scan using revision_id_idx on revision r_1 (cost=0.00..2.46 rows=1 width=48) (actual time=0.002..0.002 rows=0 loops=3,747)

  • Index Cond: (id = e."REVISION")
  • Filter: ((type)::text = 'DSN-VAL'::text)
  • Rows Removed by Filter: 1