explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mpj7

Settings

Optimization(s) for this plan:

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

Hash Left Join (cost=115,252.32..157,027.71 rows=2,568 width=385) (actual time=3,491.875..3,491.878 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,745.406 ↑ 2,568.0 1 1

Hash Left Join (cost=37,584.50..79,333.57 rows=2,568 width=377) (actual time=1,745.404..1,745.406 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.017 1,745.391 ↑ 2,568.0 1 1

Hash Left Join (cost=37,583.33..79,303.59 rows=2,568 width=367) (actual time=1,745.389..1,745.391 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.267 1,745.357 ↑ 2,568.0 1 1

Hash Right Join (cost=37,582.10..79,283.64 rows=2,568 width=352) (actual time=1,745.356..1,745.357 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,112.829 1,112.829 ↑ 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,112.829 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 561.261 ↑ 2,568.0 1 1

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

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

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

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

Parallel Seq Scan on m_member a (cost=0.00..36,286.78 rows=1,070 width=303) (actual time=555.120..555.124 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.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
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.004..0.005 rows=4 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
14. 0.000 1,746.460 ↓ 0.0 0 1

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

15. 0.002 1,746.460 ↓ 0.0 0 1

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

  • Group Key: hmb.client_id, hmb.member_id
16. 0.006 1,746.458 ↓ 0.0 0 1

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

  • Sort Key: hmb.member_id
  • Sort Method: quicksort Memory: 25kB
17. 56.413 1,746.452 ↓ 0.0 0 1

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

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

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

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

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

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

Parallel Seq Scan on m_member mmb (cost=0.00..35,755.33 rows=1,070 width=15) (actual time=523.143..523.148 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.226 ms
Execution time : 3,492.111 ms