explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PGFG

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 22.545 ↑ 1.0 1 1

Result (cost=21.81..21.82 rows=1 width=1) (actual time=22.526..22.545 rows=1 loops=1)

2.          

CTE source_filter_items

3. 0.253 0.253 ↓ 1.3 27 1

Index Scan using idx_filter_item_filter_id on filter_item fi (cost=0.29..9.41 rows=21 width=249) (actual time=0.023..0.253 rows=27 loops=1)

  • Index Cond: (filter_id = '1935054750793860693'::bigint)
4.          

CTE template_filter_items

5. 20.729 20.729 ↑ 62.5 16 1

Function Scan on isp_get_filter_templates (cost=0.25..10.25 rows=1,000 width=1,277) (actual time=20.608..20.729 rows=16 loops=1)

6.          

Initplan (forResult)

7. 0.342 22.511 ↑ 50.0 1 1

Hash Join (cost=1.35..40.73 rows=50 width=0) (actual time=22.502..22.511 rows=1 loops=1)

  • Hash Cond: (tfi.type = sfi.type)
  • Join Filter: ((sfi.is_active <> tfi.isactive) OR (COALESCE(sfi.i01, '-2'::integer) <> COALESCE(tfi.i01, '-2'::integer)) OR (COALESCE(sfi.i02, '-2'::integer) <> COALESCE(tfi.i02, '-2'::integer)) OR ((COALESCE(sfi.s02, ''::character varying))::text <> (COALESCE(tfi.s02, ''::character varying))::text) OR (COALESCE(sfi.d01) <> COALESCE(tfi.d01)) OR (COALESCE(sfi.d02) <> COALESCE(tfi.d02)) OR (COALESCE(sfi.f01, '-2'::double precision) <> COALESCE(tfi.f01, '-2'::double precision)) OR (COALESCE(sfi.f02, '-2'::double precision) <> COALESCE(tfi.f02, '-2'::double precision)) OR (COALESCE(sfi.ia1, '{}'::integer[]) <> COALESCE(tfi.ia1, '{}'::integer[])) OR CASE WHEN ((sfi.type = 3904) OR (sfi.type = 4015)) THEN (((sfi.s01)::text <> (tz_business_user.isp_get_display_name('1693876181150991416'::bigint))::text) OR (sfi.b01 <> '1669208993794360576'::bigint) OR (sfi.b02 <> '1693876181150991416'::bigint)) ELSE (((COALESCE(sfi.s01, ''::character varying))::text <> (COALESCE(tfi.s01, ''::character varying))::text) OR (COALESCE(sfi.b01, '-2'::bigint) <> COALESCE((tfi.b01)::bigint, '-2'::bigint)) OR (COALESCE(sfi.b02, '-2'::bigint) <> COALESCE((tfi.b02)::bigint, '-2'::bigint))) END)
  • Rows Removed by Join Filter: 15
8. 21.018 21.018 ↑ 62.5 16 1

CTE Scan on template_filter_items tfi (cost=0.00..20.00 rows=1,000 width=1,173) (actual time=20.640..21.018 rows=16 loops=1)

9. 0.202 1.151 ↓ 2.4 24 1

Hash (cost=1.23..1.23 rows=10 width=1,125) (actual time=1.144..1.151 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.855 0.949 ↓ 2.4 24 1

CTE Scan on source_filter_items sfi (cost=0.76..1.23 rows=10 width=1,125) (actual time=0.295..0.949 rows=24 loops=1)

  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 3
11.          

SubPlan (forCTE Scan)

12. 0.094 0.094 ↑ 11.1 9 1

Result (cost=0.00..0.51 rows=100 width=4) (actual time=0.015..0.094 rows=9 loops=1)