explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K92H : docTypeGroup

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 15,096.053 ↑ 1.2 17 1

Limit (cost=0.43..41,699.26 rows=20 width=12) (actual time=1.721..15,096.053 rows=17 loops=1)

2. 3,461.249 15,096.036 ↑ 3,165.2 17 1

Nested Loop (cost=0.43..112,186,535.71 rows=53,808 width=12) (actual time=1.718..15,096.036 rows=17 loops=1)

  • Join Filter: (e.doctypeid = "e.DocType".id)
  • Rows Removed by Join Filter: 8035187
3. 3,598.986 9,625.974 ↓ 1.3 2,008,813 1

Index Scan Backward using docs_regdate_idx on docs e (cost=0.43..112,096,618.89 rows=1,506,628 width=16) (actual time=1.597..9,625.974 rows=2,008,813 loops=1)

  • Filter: ((SubPlan 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3))
  • Rows Removed by Filter: 25
4.          

SubPlan (forIndex Scan Backward)

5. 6,026.514 6,026.514 ↑ 1.0 1 2,008,838

Index Scan using idx_docclassifications on docclassifications t (cost=0.43..8.46 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=2,008,838)

  • Index Cond: (e.id = docid)
  • Filter: (classificationid = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[]))
6. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=2.15..47.19 rows=1 width=0) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.72..38.72 rows=1 width=4) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=1.29..30.27 rows=1 width=4) (never executed)

  • Join Filter: (t0.parentid = f.docid)
9. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..28.21 rows=2 width=12) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_docclosures_childid on docclosures t0 (cost=0.43..11.32 rows=2 width=8) (never executed)

  • Index Cond: (childid = e.id)
11. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_docs on docs "t.Parent" (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (id = t0.parentid)
  • Heap Fetches: 0
12. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_docunits_docidid on docunits f (cost=0.44..1.02 rows=1 width=4) (never executed)

  • Index Cond: (docid = "t.Parent".id)
  • Filter: (unitid = 1438)
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_docs on docs "t.Child" (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (id = e.id)
  • Heap Fetches: 0
14. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_docclassifications on docclassifications j (cost=0.43..8.46 rows=1 width=4) (never executed)

  • Index Cond: (docid = e.id)
  • Filter: (classificationid = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[]))
15. 0.014 0.474 ↑ 13,938.0 2 1

Nested Loop Semi Join (cost=10,737.80..64,892.18 rows=27,876 width=4) (actual time=0.373..0.474 rows=2 loops=1)

16. 0.017 0.412 ↑ 4,646.0 6 1

Nested Loop (cost=10,737.37..50,997.05 rows=27,876 width=8) (actual time=0.291..0.412 rows=6 loops=1)

17. 0.012 0.353 ↑ 4,646.0 6 1

Nested Loop (cost=10,736.94..36,218.52 rows=27,876 width=4) (actual time=0.271..0.353 rows=6 loops=1)

18. 0.009 0.212 ↑ 1,042.0 3 1

Nested Loop (cost=10,736.51..34,063.61 rows=3,126 width=8) (actual time=0.172..0.212 rows=3 loops=1)

19. 0.054 0.167 ↑ 1,039.3 3 1

HashAggregate (cost=10,736.08..10,767.26 rows=3,118 width=4) (actual time=0.147..0.167 rows=3 loops=1)

  • Group Key: f_1.docid
20. 0.036 0.113 ↑ 446.6 7 1

Bitmap Heap Scan on docunits f_1 (cost=60.66..10,728.27 rows=3,126 width=4) (actual time=0.102..0.113 rows=7 loops=1)

  • Recheck Cond: (unitid = 1438)
  • Heap Blocks: exact=2
21. 0.077 0.077 ↑ 173.7 18 1

Bitmap Index Scan on ix_docunits_unitid (cost=0.00..59.88 rows=3,126 width=0) (actual time=0.077..0.077 rows=18 loops=1)

  • Index Cond: (unitid = 1438)
22. 0.036 0.036 ↑ 1.0 1 3

Index Only Scan using pk_docs on docs "t.Parent_1" (cost=0.43..7.48 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=3)

  • Index Cond: (id = f_1.docid)
  • Heap Fetches: 3
23. 0.129 0.129 ↑ 4.0 2 3

Index Only Scan using pk_docclosures on docclosures t0_1 (cost=0.43..0.61 rows=8 width=8) (actual time=0.040..0.043 rows=2 loops=3)

  • Index Cond: (parentid = "t.Parent_1".id)
  • Heap Fetches: 6
24. 0.042 0.042 ↑ 1.0 1 6

Index Only Scan using pk_docs on docs "t.Child_1" (cost=0.43..0.53 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=6)

  • Index Cond: (id = t0_1.childid)
  • Heap Fetches: 6
25. 0.048 0.048 ↓ 0.0 0 6

Index Scan using idx_docclassifications on docclassifications j_1 (cost=0.43..0.49 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=6)

  • Index Cond: (docid = t0_1.childid)
  • Filter: (classificationid = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[]))
26. 2,008.757 2,008.813 ↑ 1.0 4 2,008,813

Materialize (cost=0.00..3.42 rows=4 width=4) (actual time=0.000..0.001 rows=4 loops=2,008,813)

27. 0.056 0.056 ↑ 1.0 4 1

Seq Scan on doctypes "e.DocType" (cost=0.00..3.40 rows=4 width=4) (actual time=0.052..0.056 rows=4 loops=1)

  • Filter: (doctypegroupid = 401)
  • Rows Removed by Filter: 108