explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LShk

Settings
# exclusive inclusive rows x rows loops node
1. 0.235 1,706.595 ↑ 8.1 468 1

GroupAggregate (cost=268,278.80..270,239.62 rows=3,788 width=56) (actual time=1,705.094..1,706.595 rows=468 loops=1)

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

CTE selected_file

3. 0.005 416.918 ↓ 5.0 5 1

Unique (cost=37,996.26..37,996.27 rows=1 width=16) (actual time=416.913..416.918 rows=5 loops=1)

4. 0.000 416.913 ↓ 5.0 5 1

Sort (cost=37,996.26..37,996.26 rows=1 width=16) (actual time=416.912..416.913 rows=5 loops=1)

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

Gather (cost=30,512.99..37,996.25 rows=1 width=16) (actual time=415.065..416.969 rows=5 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 13.334 408.185 ↓ 2.0 2 3

Hash Join (cost=29,512.99..36,996.15 rows=1 width=16) (actual time=408.024..408.185 rows=2 loops=3)

  • Hash Cond: (a."DECLARATION" = d.id)
7. 61.911 394.684 ↑ 1.3 169,425 3

Hash Join (cost=29,489.19..36,415.99 rows=211,945 width=32) (actual time=318.670..394.684 rows=169,425 loops=3)

  • Hash Cond: (a."FILE" = f_2.id)
8. 18.615 18.615 ↑ 1.3 169,425 3

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

9. 112.955 314.158 ↑ 1.0 452,308 3

Hash (cost=23,755.75..23,755.75 rows=458,675 width=16) (actual time=314.158..314.158 rows=452,308 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 25298kB
10. 201.203 201.203 ↑ 1.0 452,308 3

Seq Scan on file f_2 (cost=0.00..23,755.75 rows=458,675 width=16) (actual time=0.035..201.203 rows=452,308 loops=3)

  • Filter: (NOT "toBeDeleted")
11. 0.011 0.167 ↓ 5.0 5 3

Hash (cost=23.79..23.79 rows=1 width=16) (actual time=0.166..0.167 rows=5 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.156 0.156 ↓ 5.0 5 3

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

  • Index Cond: (("ORGANIZATION" = '669b8235-cab3-471e-a741-f029f536fe0c'::uuid) AND (date = '2018-03-01'::date))
  • Filter: ("NEXT_SERIAL" IS NULL)
13. 0.032 1,706.360 ↑ 80.9 468 1

Merge Append (cost=230,282.53..231,902.47 rows=37,875 width=56) (actual time=1,705.072..1,706.360 rows=468 loops=1)

  • Sort Key: fc."FILE", (lower(((unnest(r.tags)))::text))
14. 1.158 1,699.434 ↑ 78.0 468 1

GroupAggregate (cost=227,076.67..227,989.17 rows=36,500 width=56) (actual time=1,698.176..1,699.434 rows=468 loops=1)

  • Group Key: fc."FILE", (lower(((unnest(r.tags)))::text))
15. 2.079 1,698.276 ↑ 13.0 2,806 1

Sort (cost=227,076.67..227,167.92 rows=36,500 width=64) (actual time=1,698.158..1,698.276 rows=2,806 loops=1)

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

Result (cost=3,089.07..224,310.77 rows=36,500 width=64) (actual time=1,577.838..1,696.197 rows=2,806 loops=1)

17. 1.144 1,695.257 ↑ 13.0 2,806 1

ProjectSet (cost=3,089.07..223,763.27 rows=36,500 width=64) (actual time=1,577.830..1,695.257 rows=2,806 loops=1)

18. 0.623 1,694.113 ↓ 6.9 2,530 1

Hash Join (cost=3,089.07..223,578.03 rows=365 width=64) (actual time=1,577.823..1,694.113 rows=2,530 loops=1)

  • Hash Cond: (fc."REVISION" = r.id)
19. 612.475 1,675.939 ↓ 6.9 2,530 1

Hash Join (cost=0.03..220,488.04 rows=365 width=48) (actual time=1,560.117..1,675.939 rows=2,530 loops=1)

  • Hash Cond: (fc."FILE" = f.id)
20. 646.528 646.528 ↑ 1.0 8,019,125 1

Seq Scan on file_controls fc (cost=0.00..190,388.53 rows=8,025,553 width=48) (actual time=0.016..646.528 rows=8,019,125 loops=1)

21. 0.013 416.936 ↓ 5.0 5 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=416.936..416.936 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 416.923 416.923 ↓ 5.0 5 1

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

23. 4.432 17.551 ↑ 1.0 23,844 1

Hash (cost=2,776.24..2,776.24 rows=25,024 width=48) (actual time=17.551..17.551 rows=23,844 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2176kB
24. 13.119 13.119 ↑ 1.0 23,844 1

Seq Scan on revision r (cost=0.00..2,776.24 rows=25,024 width=48) (actual time=0.007..13.119 rows=23,844 loops=1)

25. 0.001 6.894 ↓ 0.0 0 1

GroupAggregate (cost=3,205.85..3,250.47 rows=1,375 width=56) (actual time=6.894..6.894 rows=0 loops=1)

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

Sort (cost=3,205.85..3,211.85 rows=2,400 width=64) (actual time=6.893..6.893 rows=0 loops=1)

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

Result (cost=2,848.95..3,071.10 rows=2,400 width=64) (actual time=6.889..6.889 rows=0 loops=1)

28. 0.001 6.889 ↓ 0.0 0 1

ProjectSet (cost=2,848.95..3,035.10 rows=2,400 width=64) (actual time=6.889..6.889 rows=0 loops=1)

29. 0.276 6.888 ↓ 0.0 0 1

Hash Join (cost=2,848.95..3,022.92 rows=24 width=64) (actual time=6.888..6.888 rows=0 loops=1)

  • Hash Cond: (e."REVISION" = r_1.id)
30. 0.737 1.128 ↓ 5.0 3,747 1

Hash Join (cost=0.03..172.04 rows=749 width=48) (actual time=0.026..1.128 rows=3,747 loops=1)

  • Hash Cond: (e."FILE" = f_1.id)
31. 0.381 0.381 ↑ 1.0 3,747 1

Seq Scan on "error_669b8235-cab3-471e-a741-f029f536fe0c_201803" e (cost=0.00..150.47 rows=3,747 width=48) (actual time=0.008..0.381 rows=3,747 loops=1)

32. 0.008 0.010 ↓ 5.0 5 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.010..0.010 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.002 0.002 ↓ 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.002 rows=5 loops=1)

34. 0.151 5.484 ↑ 1.1 751 1

Hash (cost=2,838.80..2,838.80 rows=809 width=48) (actual time=5.484..5.484 rows=751 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
35. 5.333 5.333 ↑ 1.1 751 1

Seq Scan on revision r_1 (cost=0.00..2,838.80 rows=809 width=48) (actual time=0.006..5.333 rows=751 loops=1)

  • Filter: ((type)::text = 'DSN-VAL'::text)
  • Rows Removed by Filter: 23093