explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2v8b

Settings
# exclusive inclusive rows x rows loops node
1. 77.105 2,834.418 ↓ 0.0 0 1

Seq Scan on search_debug3 (cost=0.00..14,704,802.31 rows=115,260 width=20) (actual time=2,834.418..2,834.418 rows=0 loops=1)

  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 212101
2.          

SubPlan (forSeq Scan)

3. 0.000 2,757.313 ↓ 0.0 0 212,101

GroupAggregate (cost=46.63..63.77 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=212,101)

  • Group Key: search_debug3.object_id
  • Filter: ((count(1) >= 2) AND (array_agg(lower((qbank_propertyvalue_1.stringvalue)::text)) @> '{"equipment automation"," asc"}'::text[]))
  • Rows Removed by Filter: 0
4.          

Initplan (forGroupAggregate)

5. 7.736 7.736 ↓ 36.1 1,408 1

Index Scan using idx_qbank_propertyvalue_propertytypeid_languageid_stringvalue on qbank_propertyvalue (cost=0.43..41.27 rows=39 width=4) (actual time=0.085..7.736 rows=1,408 loops=1)

  • Index Cond: ((propertytype_id = 22) AND (language_id = 1) AND (lower((stringvalue)::text) = ANY ('{"equipment automation"," asc"}'::text[])))
6. 1,705.474 2,757.313 ↓ 0.0 0 212,101

Nested Loop (cost=5.36..22.48 rows=1 width=13) (actual time=0.013..0.013 rows=0 loops=212,101)

7. 0.000 636.303 ↓ 0.0 0 212,101

Nested Loop (cost=4.94..7.04 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=212,101)

  • Join Filter: (qo.type_id = qotp.type_id)
  • Rows Removed by Join Filter: 4
8. 424.202 424.202 ↑ 1.0 1 212,101

Index Scan using pk_qbank_object_id on qbank_object qo (cost=0.42..2.44 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=212,101)

  • Index Cond: (id = search_debug3.object_id)
9. 212.101 212.101 ↓ 1.3 4 212,101

Unique (cost=4.52..4.53 rows=3 width=4) (actual time=0.000..0.001 rows=4 loops=212,101)

10. 0.000 0.000 ↓ 1.3 4 212,101

Sort (cost=4.52..4.52 rows=3 width=4) (actual time=0.000..0.000 rows=4 loops=212,101)

  • Sort Key: qotp.type_id
  • Sort Method: quicksort Memory: 25kB
11. 0.006 0.039 ↓ 1.3 4 1

Hash Join (cost=2.37..4.49 rows=3 width=4) (actual time=0.032..0.039 rows=4 loops=1)

  • Hash Cond: (qotp.propertyset_id = qp.id)
12. 0.007 0.026 ↑ 1.0 4 1

Hash Join (cost=1.18..3.25 rows=4 width=12) (actual time=0.020..0.026 rows=4 loops=1)

  • Hash Cond: (qpp.propertyset_id = qotp.propertyset_id)
13. 0.014 0.014 ↑ 1.0 4 1

Seq Scan on qbank_propertyset_propertytype qpp (cost=0.00..2.01 rows=4 width=4) (actual time=0.010..0.014 rows=4 loops=1)

  • Filter: (propertytype_id = 22)
  • Rows Removed by Filter: 77
14. 0.003 0.005 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=8) (actual time=0.005..0.005 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.002 0.002 ↑ 1.0 8 1

Seq Scan on qbank_object_type_propertyset qotp (cost=0.00..1.08 rows=8 width=8) (actual time=0.001..0.002 rows=8 loops=1)

16. 0.003 0.007 ↑ 1.0 8 1

Hash (cost=1.09..1.09 rows=8 width=4) (actual time=0.007..0.007 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.004 0.004 ↑ 1.0 8 1

Seq Scan on qbank_propertyset qp (cost=0.00..1.09 rows=8 width=4) (actual time=0.002..0.004 rows=8 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
18. 415.536 415.536 ↑ 1.0 1 9,444

Index Scan using pk_qbank_propertyvalue on qbank_propertyvalue qbank_propertyvalue_1 (cost=0.43..15.44 rows=1 width=13) (actual time=0.041..0.044 rows=1 loops=9,444)

  • Index Cond: ((object_id = ANY ($1)) AND (object_id = search_debug3.object_id) AND (propertytype_id = 22) AND (language_id = 1))