explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t2rf

Settings
# exclusive inclusive rows x rows loops node
1. 44.340 377,450.330 ↓ 0.0 0 1

Delete on search_debug (cost=6,609.46..10,846.96 rows=115,260 width=6) (actual time=377,450.330..377,450.330 rows=0 loops=1)

2. 77.651 377,405.990 ↓ 1.8 212,101 1

Seq Scan on search_debug (cost=6,609.46..10,846.96 rows=115,260 width=6) (actual time=377,378.260..377,405.990 rows=212,101 loops=1)

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

SubPlan (forSeq Scan)

4. 58.634 377,328.339 ↓ 0.0 0 1

GroupAggregate (cost=46.63..6,609.46 rows=1 width=4) (actual time=377,328.339..377,328.339 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
5.          

Initplan (forGroupAggregate)

6. 49.372 49.372 ↓ 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=3.133..49.372 rows=1,408 loops=1)

  • Index Cond: ((propertytype_id = 22) AND (language_id = 1) AND (lower((stringvalue)::text) = ANY ('{"equipment automation"," asc"}'::text[])))
7. 175,282.375 377,220.333 ↓ 9,751.0 9,751 1

Nested Loop (cost=5.36..6,568.16 rows=1 width=13) (actual time=53.832..377,220.333 rows=9,751 loops=1)

  • Join Filter: (qo.id = search_debug_1.object_id)
  • Rows Removed by Join Filter: 2216869901
8. 20.838 5,586.686 ↓ 10,452.0 10,452 1

Nested Loop (cost=5.36..25.46 rows=1 width=17) (actual time=1.102..5,586.686 rows=10,452 loops=1)

  • Join Filter: (qo.type_id = qotp.type_id)
  • Rows Removed by Join Filter: 31356
9. 40.429 5,544.944 ↓ 5,226.0 10,452 1

Nested Loop (cost=0.85..20.80 rows=2 width=21) (actual time=0.031..5,544.944 rows=10,452 loops=1)

10. 4,793.779 4,793.779 ↓ 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.016..4,793.779 rows=10,452 loops=1)

  • Index Cond: ((object_id = ANY ($0)) AND (propertytype_id = 22) AND (language_id = 1))
11. 710.736 710.736 ↑ 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.066..0.068 rows=1 loops=10,452)

  • Index Cond: (id = qbank_propertyvalue_1.object_id)
12. 19.835 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)

13. 0.004 1.069 ↓ 1.3 4 1

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

14. 0.011 1.065 ↓ 1.3 4 1

Sort (cost=4.52..4.52 rows=3 width=4) (actual time=1.064..1.065 rows=4 loops=1)

  • Sort Key: qotp.type_id
  • Sort Method: quicksort Memory: 25kB
15. 0.011 1.054 ↓ 1.3 4 1

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

  • Hash Cond: (qotp.propertyset_id = qp.id)
16. 0.013 1.030 ↑ 1.0 4 1

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

  • Hash Cond: (qpp.propertyset_id = qotp.propertyset_id)
17. 1.007 1.007 ↑ 1.0 4 1

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

  • Filter: (propertytype_id = 22)
  • Rows Removed by Filter: 77
18. 0.004 0.010 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.006 0.006 ↑ 1.0 8 1

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

20. 0.002 0.013 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.011 0.011 ↑ 1.0 8 1

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

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
22. 196,351.272 196,351.272 ↑ 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.004..18.786 rows=212,101 loops=10,452)

Planning time : 22.726 ms
Execution time : 377,461.363 ms