explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7M7E

Settings
# exclusive inclusive rows x rows loops node
1. 51.621 155.594 ↑ 1.0 57,916 1

Hash Join (cost=6,062.02..8,932.36 rows=60,690 width=534) (actual time=99.481..155.594 rows=57,916 loops=1)

  • Hash Cond: (((pd.provider_id)::character varying(40))::text = (a.provider_id)::text)
2. 4.600 13.516 ↑ 1.0 5,120 1

Hash Right Join (cost=310.91..384.30 rows=5,120 width=17) (actual time=8.922..13.516 rows=5,120 loops=1)

  • Hash Cond: ((system_code_detail.system_code_value)::text = COALESCE((pd.gender)::text, 'U'::text))
3. 0.018 0.038 ↑ 12.0 2 1

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=4) (actual time=0.027..0.038 rows=2 loops=1)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
  • Filter: (is_active = 'Y'::bpchar)
  • Heap Blocks: exact=2
4. 0.020 0.020 ↑ 15.0 2 1

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.71 rows=30 width=0) (actual time=0.020..0.020 rows=2 loops=1)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
5. 4.551 8.878 ↑ 1.0 5,120 1

Hash (cost=242.20..242.20 rows=5,120 width=19) (actual time=8.877..8.878 rows=5,120 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 329kB
6. 4.327 4.327 ↑ 1.0 5,120 1

Seq Scan on provider pd (cost=0.00..242.20 rows=5,120 width=19) (actual time=0.009..4.327 rows=5,120 loops=1)

7. 43.385 90.457 ↑ 1.0 57,916 1

Hash (cost=4,122.16..4,122.16 rows=57,916 width=102) (actual time=90.456..90.457 rows=57,916 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 775kB
8. 47.072 47.072 ↑ 1.0 57,916 1

Seq Scan on provider_preferences a (cost=0.00..4,122.16 rows=57,916 width=102) (actual time=0.007..47.072 rows=57,916 loops=1)