explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8JrC

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 65,743.194 ↓ 0.0 0 1

Limit (cost=0.43..4,465.16 rows=20 width=12) (actual time=65,743.193..65,743.194 rows=0 loops=1)

2. 65,743.191 65,743.191 ↓ 0.0 0 1

Index Scan Backward using docs_regdate_idx on docs e (cost=0.43..112,111,685.18 rows=502,210 width=12) (actual time=65,743.191..65,743.191 rows=0 loops=1)

  • Filter: ((strpos(lower((docsubject)::text), 'test123'::text) > 0) AND ((SubPlan 1) OR (alternatives: SubPlan 2 or hashed SubPlan 3)))
  • Rows Removed by Filter: 2008838
3.          

SubPlan (forIndex Scan Backward)

4. 0.000 0.000 ↓ 0.0 0

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

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

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

6. 0.000 0.000 ↓ 0.0 0

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

7. 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)
8. 0.000 0.000 ↓ 0.0 0

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

9. 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)
10. 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
11. 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)
12. 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
13. 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[]))
14. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=10,737.80..64,892.18 rows=27,876 width=4) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=10,737.37..50,997.05 rows=27,876 width=8) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=10,736.94..36,218.52 rows=27,876 width=4) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=10,736.51..34,063.61 rows=3,126 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=10,736.08..10,767.26 rows=3,118 width=4) (never executed)

  • Group Key: f_1.docid
19. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on docunits f_1 (cost=60.66..10,728.27 rows=3,126 width=4) (never executed)

  • Recheck Cond: (unitid = 1438)
20. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_docunits_unitid (cost=0.00..59.88 rows=3,126 width=0) (never executed)

  • Index Cond: (unitid = 1438)
21. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = f_1.docid)
  • Heap Fetches: 0
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_docclosures on docclosures t0_1 (cost=0.43..0.61 rows=8 width=8) (never executed)

  • Index Cond: (parentid = "t.Parent_1".id)
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = t0_1.childid)
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (docid = t0_1.childid)
  • Filter: (classificationid = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[]))
Planning time : 8.495 ms