explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wdzp : Optimization for: plan #mpj7

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.011 3,819.739 ↑ 2,568.0 1 1

Hash Left Join (cost=115,252.32..157,027.71 rows=2,568 width=385) (actual time=3,819.736..3,819.739 rows=1 loops=1)

  • Hash Cond: (((b.client_id)::text = (e.client_id)::text) AND ((b.member_id)::text = (e.member_id)::text))
2. 0.007 1,753.485 ↑ 2,568.0 1 1

Hash Left Join (cost=37,584.50..79,333.57 rows=2,568 width=377) (actual time=1,753.483..1,753.485 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))
3. 0.018 1,753.469 ↑ 2,568.0 1 1

Hash Left Join (cost=37,583.33..79,303.59 rows=2,568 width=367) (actual time=1,753.468..1,753.469 rows=1 loops=1)

  • Hash Cond: (((b.client_id)::text = (c.client_id)::text) AND (b.member_kb_no = c.member_kb_no))
4. 71.743 1,753.434 ↑ 2,568.0 1 1

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

  • Hash Cond: (((b.client_id)::text = (a.client_id)::text) AND ((b.member_id)::text = (a.member_id)::text))
5. 1,133.121 1,133.121 ↑ 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,133.121 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
6. 0.000 548.570 ↑ 2,568.0 1 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
7. 5.073 548.657 ↑ 2,568.0 1 1

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

  • Workers Planned: 2
  • Workers Launched: 2
8. 543.584 543.584 ↓ 0.0 0 3

Parallel Seq Scan on m_member a (cost=0.00..36,286.78 rows=1,070 width=303) (actual time=543.579..543.584 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
9. 0.005 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
10. 0.012 0.012 ↑ 1.0 5 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.006 0.006 ↑ 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.006 rows=4 loops=1)

  • Filter: ((client_id)::text = 'hip'::text)
  • Rows Removed by Filter: 4
13. 0.001 2,066.243 ↓ 0.0 0 1

Hash (cost=77,629.31..77,629.31 rows=2,567 width=47) (actual time=2,066.243..2,066.243 rows=0 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
14. 0.001 2,066.242 ↓ 0.0 0 1

Subquery Scan on e (cost=77,552.30..77,629.31 rows=2,567 width=47) (actual time=2,066.242..2,066.242 rows=0 loops=1)

15. 0.002 2,066.241 ↓ 0.0 0 1

GroupAggregate (cost=77,552.30..77,603.64 rows=2,567 width=47) (actual time=2,066.241..2,066.241 rows=0 loops=1)

  • Group Key: hmb.client_id, hmb.member_id
16. 0.005 2,066.239 ↓ 0.0 0 1

Sort (cost=77,552.30..77,558.72 rows=2,567 width=24) (actual time=2,066.239..2,066.239 rows=0 loops=1)

  • Sort Key: hmb.member_id
  • Sort Method: quicksort Memory: 25kB
17. 68.499 2,066.234 ↓ 0.0 0 1

Hash Join (cost=37,044.23..77,406.94 rows=2,567 width=24) (actual time=2,066.234..2,066.234 rows=0 loops=1)

  • Hash Cond: ((hmb.member_id)::text = (mmb.member_id)::text)
18. 1,466.974 1,466.974 ↑ 1.0 510,021 1

Seq Scan on h_member hmb (cost=0.00..39,023.87 rows=510,029 width=24) (actual time=0.007..1,466.974 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)) AND ((member_end_date)::text >= to_char(now(), 'yyyymmdd'::text)))
  • Rows Removed by Filter: 12
19. 0.000 530.761 ↑ 2,568.0 1 1

Hash (cost=37,012.13..37,012.13 rows=2,568 width=15) (actual time=530.761..530.761 rows=1 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
20. 4.734 530.793 ↑ 2,568.0 1 1

Gather (cost=1,000.00..37,012.13 rows=2,568 width=15) (actual time=530.167..530.793 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 526.059 526.059 ↓ 0.0 0 3

Parallel Seq Scan on m_member mmb (cost=0.00..35,755.33 rows=1,070 width=15) (actual time=526.053..526.059 rows=0 loops=3)

  • Filter: (((client_id)::text = 'hip'::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
Planning time : 1.238 ms
Execution time : 3,819.996 ms