explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DcS

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 4,106.554 ↑ 2,568.0 1 1

Hash Left Join (cost=115,252.32..157,027.71 rows=2,568 width=385) (actual time=4,106.551..4,106.554 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.014 1,756.043 ↑ 2,568.0 1 1

Hash Left Join (cost=37,584.50..79,333.57 rows=2,568 width=377) (actual time=1,756.040..1,756.043 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.020 1,756.020 ↑ 2,568.0 1 1

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

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

Hash Right Join (cost=37,582.10..79,283.64 rows=2,568 width=352) (actual time=1,755.984..1,755.986 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,150.255 1,150.255 ↑ 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,150.255 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 532.662 ↑ 2,568.0 1 1

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

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

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

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

Parallel Seq Scan on m_member a (cost=0.00..36,286.78 rows=1,070 width=303) (actual time=526.064..526.069 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.014 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.009 0.009 ↑ 1.0 5 1

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

  • Filter: ((client_id)::text = 'hip'::text)
  • Rows Removed by Filter: 7
11. 0.004 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.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.003..0.005 rows=4 loops=1)

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

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

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

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

15. 0.002 2,350.488 ↓ 0.0 0 1

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

  • Group Key: hmb.client_id, hmb.member_id
16. 0.009 2,350.486 ↓ 0.0 0 1

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

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

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

  • Hash Cond: ((hmb.member_id)::text = (mmb.member_id)::text)
18. 1,744.696 1,744.696 ↑ 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.010..1,744.696 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 524.748 ↑ 2,568.0 1 1

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

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

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

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

Parallel Seq Scan on m_member mmb (cost=0.00..35,755.33 rows=1,070 width=15) (actual time=519.677..519.691 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.234 ms
Execution time : 4,106.841 ms