explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PYdg

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 46.228 ↑ 1.0 1 1

Aggregate (cost=3,230.83..3,230.85 rows=1 width=32) (actual time=46.228..46.228 rows=1 loops=1)

  • Buffers: shared hit=6,840 read=25
2. 0.019 46.172 ↓ 29.0 29 1

Nested Loop Semi Join (cost=0.85..3,230.80 rows=1 width=3) (actual time=34.370..46.172 rows=29 loops=1)

  • Join Filter: (b.id = d.id)
  • Buffers: shared hit=6,840 read=25
3. 0.027 46.066 ↓ 29.0 29 1

Nested Loop (cost=0.43..3,230.30 rows=1 width=11) (actual time=34.362..46.066 rows=29 loops=1)

  • Buffers: shared hit=6,724 read=25
4. 44.877 44.877 ↓ 4.7 14 1

Seq Scan on assets b (cost=0.00..3,204.89 rows=3 width=4) (actual time=34.242..44.877 rows=14 loops=1)

  • Filter: ((retirement_date IS NULL) AND ((sg_slot_1_type)::text = 'CustomEntity02'::text) AND (sg_slot_1_id = 1,113))
  • Rows Removed by Filter: 172,199
  • Buffers: shared hit=6,212 read=5
5. 1.046 1.162 ↓ 2.0 2 14

Index Scan using tasks_entity on tasks a (cost=0.43..8.46 rows=1 width=7) (actual time=0.049..0.083 rows=2 loops=14)

  • Index Cond: (((entity_type)::text = 'Asset'::text) AND (entity_id = b.id))
  • Filter: ((task_template_id IS NULL) AND (retirement_date IS NULL) AND (step_id = 9) AND ((project_id = ANY ('{65,85,250,113,118,370,245,285,128,86,251,288,376,133,331,186,167,248,286,287,289,324,330,116,240,121,168,176,241,326,327,328,329,242,243,244,246,247,252,332,174,175,91,325,130,192,182,333,335,336,337,371,187,369,372,373,374,375,377,378,173,178,179,183,94,127,122,87,129,107,80,74,78,131,198,237,239,238,177,204,82,172,191,189,193,180,181,184,194,195,196,197,170,117,166,171,199,203,202,169,185,126,66,84}'::integer[])) OR (project_id IS NULL)) AND (((entity_type)::text <> 'Asset'::text) OR (entity_type IS NULL) OR (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=512 read=20
6.          

SubPlan (for Index Scan)

7. 0.116 0.116 ↓ 0.0 0 29

Index Scan using assets_pkey on assets c (cost=0.42..0.64 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=29)

  • Index Cond: (id = a.entity_id)
  • Filter: ((retirement_date IS NULL) AND (lower((sg_status_list)::text) = 'hld'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=116
8. 0.000 0.000 ↓ 0.0 0

Index Scan using sgs_assets on assets c_1 (cost=0.42..236.37 rows=2,716 width=4) (never executed)

  • Index Cond: (lower((sg_status_list)::text) = 'hld'::text)
  • Filter: (retirement_date IS NULL)
9. 0.087 0.087 ↑ 1.0 1 29

Index Scan using assets_pkey on assets d (cost=0.42..0.46 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=29)

  • Index Cond: (id = a.entity_id)
  • Filter: ((retirement_date IS NULL) AND (lower(sg_asset_type) = ANY ('{character,prop,set,transit}'::text[])))
  • Buffers: shared hit=116
Planning time : 1.112 ms
Execution time : 46.343 ms