explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oCVp

Settings
# exclusive inclusive rows x rows loops node
1. 123.081 428,088.814 ↓ 1.8 212,101 1

Seq Scan on search_debug (cost=6,609.46..10,846.96 rows=115,260 width=20) (actual time=428,043.096..428,088.814 rows=212,101 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
2.          

SubPlan (forSeq Scan)

3. 46.783 427,965.733 ↓ 0.0 0 1

GroupAggregate (cost=46.63..6,609.46 rows=1 width=4) (actual time=427,965.733..427,965.733 rows=0 loops=1)

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

Initplan (forGroupAggregate)

5. 77.018 77.018 ↓ 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=32.853..77.018 rows=1,408 loops=1)

  • Index Cond: ((propertytype_id = 22) AND (language_id = 1) AND (lower((stringvalue)::text) = ANY ('{"equipment automation"," asc"}'::text[])))
6. 195,680.318 427,841.932 ↓ 9,751.0 9,751 1

Nested Loop (cost=5.36..6,568.16 rows=1 width=13) (actual time=75.787..427,841.932 rows=9,751 loops=1)

  • Join Filter: (qo.id = search_debug_1.object_id)
  • Rows Removed by Join Filter: 2216869901
7. 24.736 4,757.450 ↓ 10,452.0 10,452 1

Nested Loop (cost=5.36..25.46 rows=1 width=17) (actual time=0.088..4,757.450 rows=10,452 loops=1)

  • Join Filter: (qo.type_id = qotp.type_id)
  • Rows Removed by Join Filter: 31356
8. 44.600 4,711.810 ↓ 5,226.0 10,452 1

Nested Loop (cost=0.85..20.80 rows=2 width=21) (actual time=0.023..4,711.810 rows=10,452 loops=1)

9. 4,353.650 4,353.650 ↓ 5,226.0 10,452 1

Index Scan using pk_qbank_propertyvalue on qbank_propertyvalue qbank_propertyvalue_1 (cost=0.43..15.91 rows=2 width=13) (actual time=0.012..4,353.650 rows=10,452 loops=1)

  • Index Cond: ((object_id = ANY ($0)) AND (propertytype_id = 22) AND (language_id = 1))
10. 313.560 313.560 ↑ 1.0 1 10,452

Index Scan using pk_qbank_object_id on qbank_object qo (cost=0.42..2.44 rows=1 width=8) (actual time=0.028..0.030 rows=1 loops=10,452)

  • Index Cond: (id = qbank_propertyvalue_1.object_id)
11. 20.843 20.904 ↓ 1.3 4 10,452

Materialize (cost=4.52..4.58 rows=3 width=4) (actual time=0.000..0.002 rows=4 loops=10,452)

12. 0.002 0.061 ↓ 1.3 4 1

Unique (cost=4.52..4.53 rows=3 width=4) (actual time=0.058..0.061 rows=4 loops=1)

13. 0.009 0.059 ↓ 1.3 4 1

Sort (cost=4.52..4.52 rows=3 width=4) (actual time=0.058..0.059 rows=4 loops=1)

  • Sort Key: qotp.type_id
  • Sort Method: quicksort Memory: 25kB
14. 0.008 0.050 ↓ 1.3 4 1

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

  • Hash Cond: (qotp.propertyset_id = qp.id)
15. 0.006 0.031 ↑ 1.0 4 1

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

  • Hash Cond: (qpp.propertyset_id = qotp.propertyset_id)
16. 0.018 0.018 ↑ 1.0 4 1

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

  • Filter: (propertytype_id = 22)
  • Rows Removed by Filter: 77
17. 0.002 0.007 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.005 0.005 ↑ 1.0 8 1

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

19. 0.003 0.011 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.008 0.008 ↑ 1.0 8 1

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

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
21. 227,404.164 227,404.164 ↑ 1.1 212,101 10,452

Seq Scan on search_debug search_debug_1 (cost=0.00..3,661.20 rows=230,520 width=4) (actual time=0.005..21.757 rows=212,101 loops=10,452)