explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r9S1

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 4.001 ↓ 0.0 0 1

Update on document_status updated_status (cost=71.82..701.90 rows=1 width=134) (actual time=4.001..4.001 rows=0 loops=1)

2. 0.008 3.982 ↑ 1.0 1 1

Nested Loop (cost=71.82..701.90 rows=1 width=134) (actual time=1.454..3.982 rows=1 loops=1)

  • Join Filter: (((tt.is_csdr_sent = 1) AND (((usr.permission & '16'::bigint) = 0) OR (((usr.permission & '16'::bigint) <> 0) AND ((tt.manual_cdate IS NULL) OR (tt.manual_cdate < tt.cdate))))) OR ((tt.is_csdr = 0) AND (((usr.permission & '256'::bigint) = 0) OR (((usr.permission & '256'::bigint) <> 0) AND ((tt.manual_cdate IS NULL) OR (tt.manual_cdate < tt.cdate))))))
  • Rows Removed by Join Filter: 1
3. 0.004 3.815 ↓ 1.5 3 1

Nested Loop (cost=10.92..579.98 rows=2 width=98) (actual time=1.375..3.815 rows=3 loops=1)

4. 1.052 3.796 ↓ 1.5 3 1

Hash Join (cost=10.63..563.37 rows=2 width=84) (actual time=1.365..3.796 rows=3 loops=1)

  • Hash Cond: (updated_status.id = ds.id)
5. 2.719 2.719 ↑ 1.0 13,361 1

Seq Scan on document_status updated_status (cost=0.00..502.61 rows=13,361 width=54) (actual time=0.013..2.719 rows=13,361 loops=1)

6. 0.003 0.025 ↓ 1.5 3 1

Hash (cost=10.61..10.61 rows=2 width=38) (actual time=0.025..0.025 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.022 0.022 ↓ 1.5 3 1

Index Scan using usr_src_d_st_u_idx on document_status ds (cost=0.41..10.61 rows=2 width=38) (actual time=0.017..0.022 rows=3 loops=1)

  • Index Cond: ((src = 0) AND (document_id = 12,576))
8. 0.015 0.015 ↑ 1.0 1 3

Index Scan using usr_ugid_idx on usr (cost=0.29..8.31 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: (id = ds.user_id)
9. 0.009 0.159 ↑ 1.0 1 3

Subquery Scan on tt (cost=60.90..60.92 rows=1 width=72) (actual time=0.053..0.053 rows=1 loops=3)

  • Filter: ((tt.is_csdr_sent = 1) OR (tt.is_csdr = 0))
10. 0.000 0.150 ↑ 1.0 1 3

Unique (cost=60.90..60.90 rows=1 width=560) (actual time=0.050..0.050 rows=1 loops=3)

11.          

Initplan (for Unique)

12. 0.006 0.006 ↑ 1.0 1 2

Index Scan using usr_ugid_idx on usr usr_1 (cost=0.29..8.31 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=2)

  • Index Cond: (id = $0)
13. 0.009 0.147 ↑ 1.0 1 3

Sort (cost=52.59..52.59 rows=1 width=560) (actual time=0.049..0.049 rows=1 loops=3)

  • Sort Key: r.cdate DESC
  • Sort Method: quicksort Memory: 25kB
14. 0.018 0.138 ↑ 1.0 1 3

Nested Loop Anti Join (cost=2.00..52.58 rows=1 width=560) (actual time=0.044..0.046 rows=1 loops=3)

  • Join Filter: (re.resolution_id = r.id)
  • Rows Removed by Join Filter: 21
15. 0.003 0.072 ↑ 1.0 1 3

Nested Loop Anti Join (cost=1.72..45.93 rows=1 width=28) (actual time=0.022..0.024 rows=1 loops=3)

  • Join Filter: (r2.cdate > r.cdate)
16. 0.002 0.063 ↑ 1.0 1 3

Nested Loop Anti Join (cost=1.43..37.61 rows=1 width=28) (actual time=0.019..0.021 rows=1 loops=3)

  • Join Filter: (project.parent_id = r.id)
17. 0.003 0.057 ↑ 1.0 1 3

Nested Loop (cost=0.86..25.89 rows=1 width=28) (actual time=0.017..0.019 rows=1 loops=3)

18. 0.003 0.030 ↓ 2.0 2 3

Nested Loop (cost=0.57..18.57 rows=1 width=28) (actual time=0.007..0.010 rows=2 loops=3)

19. 0.009 0.009 ↑ 1.0 1 3

Index Scan using usr_ugid_idx on usr u (cost=0.29..8.31 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3)

  • Index Cond: (id = ds.user_id)
  • Filter: (is_connected = 1)
20. 0.018 0.018 ↓ 2.0 2 3

Index Scan using res_docid_author_test_idx on resolution r (cost=0.29..10.25 rows=1 width=28) (actual time=0.004..0.006 rows=2 loops=3)

  • Index Cond: ((document_id = 12,576) AND (u.connection_date <= rdate))
  • Filter: (is_csdr_project = 0)
21. 0.024 0.024 ↓ 0.0 0 6

Index Only Scan using rt_userid_resid on resolution_to rt (cost=0.29..7.31 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=6)

  • Index Cond: ((user_id = ds.user_id) AND (resolution_id = r.id))
  • Heap Fetches: 2
22. 0.000 0.004 ↓ 0.0 0 2

Nested Loop Semi Join (cost=0.57..11.71 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=2)

23. 0.004 0.004 ↓ 0.0 0 2

Index Scan using res_docid_author_test_idx on resolution project (cost=0.29..8.02 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=2)

  • Index Cond: ((document_id = 12,576) AND (author = rt.user_id) AND (author = ds.user_id))
  • Filter: (is_draft = 1)
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using resolution_parent_id_idx on resolution result (cost=0.29..4.34 rows=3 width=8) (never executed)

  • Index Cond: (parent_id = project.id)
  • Heap Fetches: 0
25. 0.006 0.006 ↓ 0.0 0 2

Index Scan using resolution_document_id_author_on_not_project_idx on resolution r2 (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2)

  • Index Cond: ((document_id = 12,576) AND (author = ds.user_id))
  • Filter: (is_sp = 0)
26. 0.048 0.048 ↓ 7.8 31 2

Index Only Scan using "idx$$_61550007" on r_execution re (cost=0.28..6.60 rows=4 width=16) (actual time=0.008..0.024 rows=31 loops=2)

  • Index Cond: ((is_interim = 0) AND (author = rt.user_id) AND (author = ds.user_id))
  • Heap Fetches: 43
Planning time : 5.502 ms
Execution time : 4.181 ms