explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KHed

Settings
# exclusive inclusive rows x rows loops node
1. 6.510 50,663.395 ↑ 7.5 29,047 1

Unique (cost=3,816,994.96..3,818,712.90 rows=219,092 width=57) (actual time=50,646.432..50,663.395 rows=29,047 loops=1)

2. 6.387 50,656.885 ↑ 7.5 29,047 1

Merge Anti Join (cost=3,816,994.96..3,818,165.17 rows=219,092 width=57) (actual time=50,646.431..50,656.885 rows=29,047 loops=1)

  • Merge Cond: (mc.id = mcc.member_campaign_id)
  • Join Filter: (cc.id = mcc.campaign_communication_id)
3. 32.722 50,644.254 ↑ 7.5 29,052 1

Sort (cost=3,807,561.31..3,808,109.26 rows=219,179 width=61) (actual time=50,640.171..50,644.254 rows=29,052 loops=1)

  • Sort Key: mc.id
  • Sort Method: external merge Disk: 1992kB
4. 5,387.196 50,611.532 ↑ 7.5 29,052 1

Hash Join (cost=2,831,360.95..3,779,875.72 rows=219,179 width=61) (actual time=36,004.634..50,611.532 rows=29,052 loops=1)

  • Hash Cond: (ma.member_id = m.id)
5. 9,272.674 15,293.183 ↓ 1.0 19,482,852 1

Bitmap Heap Scan on member_addresses ma (cost=851,974.23..1,570,903.76 rows=19,461,453 width=5) (actual time=6,033.566..15,293.183 rows=19,482,852 loops=1)

  • Filter: current
  • Rows Removed by Filter: 13436073
  • Heap Blocks: exact=48106 lossy=449286
6. 6,020.509 6,020.509 ↓ 1.0 19,482,852 1

Bitmap Index Scan on index_member_addresses_on_postal_code_and_current_and_id (cost=0.00..847,108.86 rows=19,461,453 width=0) (actual time=6,020.508..6,020.509 rows=19,482,852 loops=1)

  • Index Cond: (current = true)
7. 18.212 29,931.153 ↑ 7.6 29,052 1

Hash (cost=1,974,261.71..1,974,261.71 rows=220,481 width=64) (actual time=29,931.153..29,931.153 rows=29,052 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 849kB
8. 3,115.116 29,912.941 ↑ 7.6 29,052 1

Hash Join (cost=1,396,269.60..1,974,261.71 rows=220,481 width=64) (actual time=21,258.621..29,912.941 rows=29,052 loops=1)

  • Hash Cond: (mc.member_id = m.id)
9. 20.939 5,625.032 ↑ 8.9 29,063 1

Nested Loop (cost=7,224.38..505,865.08 rows=257,415 width=57) (actual time=29.281..5,625.032 rows=29,063 loops=1)

10. 1.092 1.092 ↑ 1.0 1 1

Index Scan using campaign_communications_pkey on campaign_communications cc (cost=0.28..8.30 rows=1 width=8) (actual time=1.091..1.092 rows=1 loops=1)

  • Index Cond: (id = 2801)
11. 5,580.177 5,603.001 ↑ 6.6 29,063 1

Bitmap Heap Scan on member_campaigns mc (cost=7,224.10..503,937.16 rows=191,962 width=57) (actual time=28.123..5,603.001 rows=29,063 loops=1)

  • Recheck Cond: (campaign_id = cc.campaign_id)
  • Filter: ((state)::text IS DISTINCT FROM 'deactivated'::text)
  • Rows Removed by Filter: 2050
  • Heap Blocks: exact=25567
12. 22.824 22.824 ↑ 7.1 31,113 1

Bitmap Index Scan on index_member_campaigns_on_campaign_id (cost=0.00..7,176.11 rows=221,273 width=0) (actual time=22.824..22.824 rows=31,113 loops=1)

  • Index Cond: (campaign_id = cc.campaign_id)
13. 7,843.265 21,172.793 ↑ 1.0 16,725,157 1

Hash (cost=1,113,941.86..1,113,941.86 rows=16,768,188 width=7) (actual time=21,172.793..21,172.793 rows=16,725,157 loops=1)

  • Buckets: 131072 Batches: 256 Memory Usage: 3495kB
14. 11,050.725 13,329.528 ↑ 1.0 16,725,157 1

Bitmap Heap Scan on members m (cost=318,629.88..1,113,941.86 rows=16,768,188 width=7) (actual time=2,291.510..13,329.528 rows=16,725,157 loops=1)

  • Recheck Cond: (deleted_at IS NULL)
  • Rows Removed by Index Recheck: 2223240
  • Filter: (receive_mail AND ((status)::text IS DISTINCT FROM 'opted_out'::text))
  • Rows Removed by Filter: 265030
  • Heap Blocks: exact=49783 lossy=526468
15. 2,278.803 2,278.803 ↑ 1.0 16,990,187 1

Bitmap Index Scan on index_members_on_deleted_at (cost=0.00..314,437.83 rows=17,032,719 width=0) (actual time=2,278.803..2,278.803 rows=16,990,187 loops=1)

  • Index Cond: (deleted_at IS NULL)
16. 0.073 6.244 ↑ 2,929.6 5 1

Sort (cost=9,433.63..9,470.25 rows=14,648 width=8) (actual time=6.244..6.244 rows=5 loops=1)

  • Sort Key: mcc.member_campaign_id
  • Sort Method: quicksort Memory: 25kB
17. 6.171 6.171 ↑ 2,929.6 5 1

Index Scan using mcc_index_on_cc_id on member_campaign_communications mcc (cost=0.56..8,420.11 rows=14,648 width=8) (actual time=3.654..6.171 rows=5 loops=1)

  • Index Cond: (campaign_communication_id = 2801)