explain.depesz.com

PostgreSQL's explain analyze made readable

Result: omNg

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 8,191.702 ↑ 2,568.0 1 1

Hash Left Join (cost=173,336.01..192,548.16 rows=2,568 width=385) (actual time=8,191.695..8,191.702 rows=1 loops=1)

  • Hash Cond: (((b.client_id)::text = (d.client_id)::text) AND (b.member_kb_no = d.member_kb_no) AND (b.member_type_no = d.member_type_no))
2. 0.018 8,191.687 ↑ 2,568.0 1 1

Hash Left Join (cost=173,334.84..192,505.34 rows=2,568 width=388) (actual time=8,191.681..8,191.687 rows=1 loops=1)

  • Hash Cond: (((b.client_id)::text = (c.client_id)::text) AND (b.member_kb_no = c.member_kb_no))
3. 72.986 8,191.652 ↑ 2,568.0 1 1

Hash Right Join (cost=173,333.62..192,485.39 rows=2,568 width=373) (actual time=8,191.646..8,191.652 rows=1 loops=1)

  • Hash Cond: (((h_member.client_id)::text = (b.client_id)::text) AND ((h_member.member_id)::text = (b.member_id)::text))
4. 226.397 6,393.855 ↑ 1.0 510,019 1

GroupAggregate (cost=94,011.46..104,212.04 rows=510,029 width=47) (actual time=6,086.685..6,393.855 rows=510,019 loops=1)

  • Group Key: h_member.client_id, h_member.member_id
5. 5,457.169 6,167.458 ↑ 1.0 510,021 1

Sort (cost=94,011.46..95,286.53 rows=510,029 width=24) (actual time=6,086.674..6,167.458 rows=510,021 loops=1)

  • Sort Key: h_member.member_id
  • Sort Method: external merge Disk: 16976kB
6. 710.289 710.289 ↑ 1.0 510,021 1

Seq Scan on h_member (cost=0.00..35,198.65 rows=510,029 width=24) (actual time=0.012..710.289 rows=510,021 loops=1)

  • Filter: (((client_id)::text = 'hip'::text) AND ((nyukin_flg)::text = '1'::text) AND ((member_end_date)::text >= to_char(now(), 'yyyymmdd'::text)))
  • Rows Removed by Filter: 12
7. 0.014 1,724.811 ↑ 2,568.0 1 1

Hash (cost=79,283.64..79,283.64 rows=2,568 width=352) (actual time=1,724.811..1,724.811 rows=1 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
8. 71.097 1,724.797 ↑ 2,568.0 1 1

Hash Right Join (cost=37,582.10..79,283.64 rows=2,568 width=352) (actual time=1,724.796..1,724.797 rows=1 loops=1)

  • Hash Cond: (((b.client_id)::text = (a.client_id)::text) AND ((b.member_id)::text = (a.member_id)::text))
9. 1,120.180 1,120.180 ↑ 1.0 510,019 1

Seq Scan on h_member b (cost=0.00..39,023.87 rows=510,029 width=53) (actual time=0.024..1,120.180 rows=510,019 loops=1)

  • Filter: (((client_id)::text = 'hip'::text) AND ((nyukin_flg)::text = '1'::text) AND (to_char(now(), 'yyyymmdd'::text) >= (member_start_date)::text) AND (to_char(now(), 'yyyymmdd'::text) <= (member_end_date)::text))
  • Rows Removed by Filter: 14
10. 0.000 533.520 ↑ 2,568.0 1 1

Hash (cost=37,543.58..37,543.58 rows=2,568 width=303) (actual time=533.520..533.520 rows=1 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
11. 5.437 533.584 ↑ 2,568.0 1 1

Gather (cost=1,000.00..37,543.58 rows=2,568 width=303) (actual time=533.369..533.584 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 528.147 528.147 ↓ 0.0 0 3

Parallel Seq Scan on m_member a (cost=0.00..36,286.78 rows=1,070 width=303) (actual time=528.146..528.147 rows=0 loops=3)

  • Filter: (((client_id)::text = 'hip'::text) AND ((admission_kb)::text = '1'::text) AND ((lower((mail_address)::text) = 'thiennb@rikkeisoft.com'::text) OR ((login_id)::text = 'thiennb@rikkeisoft.com'::text) OR ((lower((mail_address_1)::text) = 'thiennb@rikkeisoft.com'::text) AND ((login_id_2_flg)::text = '1'::text)) OR ((lower((login_id_3)::text) = 'thiennb@rikkeisoft.com'::text) AND ((login_id_3_flg)::text = '1'::text))))
  • Rows Removed by Filter: 170060
13. 0.004 0.017 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=31) (actual time=0.017..0.017 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.013 0.013 ↑ 1.0 5 1

Seq Scan on m_member_kb c (cost=0.00..1.15 rows=5 width=31) (actual time=0.011..0.013 rows=5 loops=1)

  • Filter: ((client_id)::text = 'hip'::text)
  • Rows Removed by Filter: 7
15. 0.003 0.008 ↑ 1.0 4 1

Hash (cost=1.10..1.10 rows=4 width=34) (actual time=0.008..0.008 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on m_member_type d (cost=0.00..1.10 rows=4 width=34) (actual time=0.004..0.005 rows=4 loops=1)

  • Filter: ((client_id)::text = 'hip'::text)
  • Rows Removed by Filter: 4
Planning time : 0.923 ms
Execution time : 8,194.611 ms