explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TBFQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 64.971 ↑ 66.7 3 1

Sort (cost=14,841.71..14,842.21 rows=200 width=24) (actual time=64.971..64.971 rows=3 loops=1)

  • Sort Key: range.date_from NULLS FIRST
  • Sort Method: quicksort Memory: 25kB
2.          

CTE range

3. 0.004 0.047 ↑ 200.2 5 1

Append (cost=0.03..10.06 rows=1,001 width=16) (actual time=0.027..0.047 rows=5 loops=1)

4. 0.029 0.029 ↑ 250.0 4 1

Function Scan on generate_series days (cost=0.03..10.03 rows=1,000 width=16) (actual time=0.027..0.029 rows=4 loops=1)

5. 0.014 0.014 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)

6. 0.107 64.964 ↑ 66.7 3 1

HashAggregate (cost=14,815.01..14,824.01 rows=200 width=24) (actual time=64.959..64.964 rows=3 loops=1)

  • Group Key: range.date_from, range.date_to
7. 0.207 64.857 ↑ 74.8 114 1

Nested Loop (cost=12,716.63..14,644.55 rows=8,523 width=24) (actual time=64.115..64.857 rows=114 loops=1)

  • Join Filter: (((range.date_from IS NOT NULL) AND (contract.status_changed > range.date_from) AND (contract.status_changed < (range.date_to + '1 day'::interval))) OR ((range.date_from IS NULL) AND (contract.status_changed <= (range.date_to + '1 day'::interval))))
  • Rows Removed by Join Filter: 451
8. 0.055 0.055 ↑ 200.2 5 1

CTE Scan on range (cost=0.00..20.02 rows=1,001 width=16) (actual time=0.030..0.055 rows=5 loops=1)

9. 0.149 64.595 ↓ 1.5 113 5

Materialize (cost=12,716.63..12,722.82 rows=76 width=8) (actual time=12.736..12.919 rows=113 loops=5)

10. 0.051 64.446 ↓ 1.5 113 1

Subquery Scan on contract (cost=12,716.63..12,722.44 rows=76 width=8) (actual time=63.678..64.446 rows=113 loops=1)

  • Filter: ((NOT contract.has_posts_by_reviwer) OR contract.has_not_registred_reviewers)
  • Rows Removed by Filter: 14
11. 0.110 64.395 ↓ 1.3 127 1

Unique (cost=12,716.63..12,721.43 rows=101 width=57) (actual time=63.675..64.395 rows=127 loops=1)

12. 0.345 64.285 ↓ 2.8 280 1

WindowAgg (cost=12,716.63..12,721.18 rows=101 width=57) (actual time=63.674..64.285 rows=280 loops=1)

13. 0.206 63.940 ↓ 2.8 280 1

Group (cost=12,716.63..12,718.65 rows=101 width=57) (actual time=63.647..63.940 rows=280 loops=1)

  • Group Key: contract_1.id, p.id, d.statuschanged, a.userid
14. 0.447 63.734 ↓ 4.3 430 1

Sort (cost=12,716.63..12,716.88 rows=101 width=57) (actual time=63.645..63.734 rows=430 loops=1)

  • Sort Key: contract_1.id, p.id, d.statuschanged, a.userid
  • Sort Method: quicksort Memory: 63kB
15. 0.349 63.287 ↓ 4.3 430 1

Hash Left Join (cost=1,681.86..12,713.27 rows=101 width=57) (actual time=12.706..63.287 rows=430 loops=1)

  • Hash Cond: ((dis.id = p.discussionid) AND (a.userid = p.userid))
16. 0.255 52.777 ↓ 4.0 405 1

Nested Loop Left Join (cost=911.56..11,934.37 rows=101 width=57) (actual time=2.519..52.777 rows=405 loops=1)

17. 0.305 50.996 ↓ 3.4 218 1

Nested Loop (cost=911.56..11,803.17 rows=65 width=57) (actual time=2.504..50.996 rows=218 loops=1)

18. 0.279 50.255 ↓ 3.4 218 1

Nested Loop (cost=911.28..11,771.94 rows=65 width=56) (actual time=2.498..50.255 rows=218 loops=1)

19. 0.440 48.548 ↓ 3.6 238 1

Nested Loop (cost=910.86..11,640.13 rows=67 width=72) (actual time=2.458..48.548 rows=238 loops=1)

20. 0.418 47.394 ↓ 3.3 238 1

Hash Join (cost=910.86..11,589.79 rows=72 width=56) (actual time=2.438..47.394 rows=238 loops=1)

  • Hash Cond: (dr.documentid = d.id)
21. 44.555 44.555 ↓ 1.5 751 1

Seq Scan on documentroles dr (cost=0.00..10,676.27 rows=517 width=32) (actual time=0.008..44.555 rows=751 loops=1)

  • Filter: ((role = 'Reviewer'::text) AND (party = 'PARTY'::text))
  • Rows Removed by Filter: 348,945
22. 0.331 2.421 ↓ 1.0 797 1

Hash (cost=900.96..900.96 rows=792 width=40) (actual time=2.421..2.421 rows=797 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 65kB
23. 2.090 2.090 ↓ 1.0 797 1

Seq Scan on documents d (cost=0.00..900.96 rows=792 width=40) (actual time=0.004..2.090 rows=797 loops=1)

  • Filter: (status = 'REVIEWING'::text)
  • Rows Removed by Filter: 4,892
24. 0.714 0.714 ↑ 1.0 1 238

Index Scan using cp_id on contract contract_1 (cost=0.00..0.69 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=238)

  • Index Cond: (id = d.contractid)
  • Filter: ((NOT createdasexecuted) AND (NOT cancelled) AND (NOT terminated))
25. 1.428 1.428 ↑ 1.0 1 238

Index Only Scan using idx_contractsview_contractid_userid on contractsview cv (cost=0.42..1.96 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=238)

  • Index Cond: ((contractid = contract_1.id) AND (userid = 'cd9a210b-1779-480e-9990-65e846e4d4e9'::uuid))
  • Heap Fetches: 0
26. 0.436 0.436 ↑ 1.0 1 218

Index Scan using ac_userid on account a (cost=0.28..0.47 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=218)

  • Index Cond: (userid = dr.userid)
27. 1.526 1.526 ↑ 6.0 1 218

Index Scan using di_documentid on discussion dis (cost=0.00..1.96 rows=6 width=32) (actual time=0.003..0.007 rows=1 loops=218)

  • Index Cond: (d.id = documentid)
28. 5.004 10.161 ↓ 1.0 13,823 1

Hash (cost=563.72..563.72 rows=13,772 width=48) (actual time=10.161..10.161 rows=13,823 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,208kB
29. 5.157 5.157 ↓ 1.0 13,823 1

Seq Scan on post p (cost=0.00..563.72 rows=13,772 width=48) (actual time=0.006..5.157 rows=13,823 loops=1)

Planning time : 3.045 ms
Execution time : 65.150 ms