explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p3X6 : docTypeGroup with index on docTypeId

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 14,975.722 ↑ 1.2 17 1

Limit (cost=0.43..41,699.26 rows=20 width=12) (actual time=1.761..14,975.722 rows=17 loops=1)

2. 3,190.064 14,975.703 ↑ 3,165.2 17 1

Nested Loop (cost=0.43..112,186,535.71 rows=53,808 width=12) (actual time=1.759..14,975.703 rows=17 loops=1)

  • Join Filter: (e.doctypeid = "e.DocType".id)
  • Rows Removed by Join Filter: 8035187
3. 3,749.733 9,776.826 ↓ 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.627..9,776.826 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.013 0.579 ↑ 13,938.0 2 1

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

16. 0.015 0.518 ↑ 4,646.0 6 1

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

17. 0.013 0.455 ↑ 4,646.0 6 1

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

18. 0.012 0.322 ↑ 1,042.0 3 1

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

19. 0.053 0.157 ↑ 1,039.3 3 1

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

  • Group Key: f_1.docid
20. 0.033 0.104 ↑ 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.094..0.104 rows=7 loops=1)

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

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

  • Index Cond: (unitid = 1438)
22. 0.153 0.153 ↑ 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.051..0.051 rows=1 loops=3)

  • Index Cond: (id = f_1.docid)
  • Heap Fetches: 3
23. 0.120 0.120 ↑ 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.037..0.040 rows=2 loops=3)

  • Index Cond: (parentid = "t.Parent_1".id)
  • Heap Fetches: 6
24. 0.048 0.048 ↑ 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.008..0.008 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.753 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.060 0.060 ↑ 1.0 4 1

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

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