explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AVSj : Optimization for: plan #LG2f

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,394.562 27,415.264 ↓ 8.0 8 1

GroupAggregate (cost=4,872.01..4,872.07 rows=1 width=543) (actual time=20,564.239..27,415.264 rows=8 loops=1)

  • Group Key: a.campaignid, b.campaign_name, b.message_type_id, c.username, b.schedule_time, f.aggregator_type, e.cli_manager_id
2. 12,934.458 25,020.702 ↓ 3,206,196.0 3,206,196 1

Sort (cost=4,872.01..4,872.01 rows=1 width=483) (actual time=19,627.424..25,020.702 rows=3,206,196 loops=1)

  • Sort Key: a.campaignid, b.campaign_name, b.message_type_id, c.username, b.schedule_time, f.aggregator_type, e.cli_manager_id
  • Sort Method: external merge Disk: 281,456kB
3. 1,155.319 12,086.244 ↓ 3,206,196.0 3,206,196 1

Nested Loop (cost=22.03..4,872.00 rows=1 width=483) (actual time=99.704..12,086.244 rows=3,206,196 loops=1)

  • Join Filter: (b.user_id = g.user_id)
4. 2,157.868 4,518.533 ↓ 3,206,196.0 3,206,196 1

Nested Loop Left Join (cost=21.89..4,871.79 rows=1 width=495) (actual time=99.688..4,518.533 rows=3,206,196 loops=1)

5. 94.825 935.689 ↓ 356,244.0 356,244 1

Nested Loop (cost=21.75..4,871.54 rows=1 width=77) (actual time=99.664..935.689 rows=356,244 loops=1)

6. 0.537 2.376 ↓ 588.0 588 1

Nested Loop (cost=21.33..31.57 rows=1 width=65) (actual time=0.295..2.376 rows=588 loops=1)

  • Join Filter: (b.user_id = c.user_master_id)
7. 0.295 0.663 ↓ 98.0 588 1

Merge Join (cost=21.18..30.22 rows=6 width=46) (actual time=0.246..0.663 rows=588 loops=1)

  • Merge Cond: (e.user_id = b.user_id)
8. 0.035 0.035 ↑ 152.4 5 1

Index Scan using "idx_FK_7hc6agd_tbl_cli_ma_1592228110_32" on tbl_cli_manager e (cost=0.42..6,281.84 rows=762 width=12) (actual time=0.014..0.035 rows=5 loops=1)

  • Filter: (cli_manager_id = COALESCE(cli_manager_id))
9. 0.179 0.333 ↓ 4.0 585 1

Sort (cost=20.76..21.13 rows=147 width=34) (actual time=0.225..0.333 rows=585 loops=1)

  • Sort Key: b.user_id
  • Sort Method: quicksort Memory: 36kB
10. 0.154 0.154 ↑ 1.0 147 1

Seq Scan on tbl_campaign b (cost=0.00..15.47 rows=147 width=34) (actual time=0.013..0.154 rows=147 loops=1)

11. 1.176 1.176 ↑ 1.0 1 588

Index Scan using ind_user_master_c_user on tbl_users_master c (cost=0.14..0.21 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=588)

  • Index Cond: (user_master_id = e.user_id)
  • Filter: ((username)::text = (COALESCE(username))::text)
12. 51.156 838.488 ↓ 202.0 606 588

Append (cost=0.42..4,839.94 rows=3 width=20) (actual time=0.546..1.426 rows=606 loops=588)

13. 319.284 319.284 ↓ 0.0 0 588

Index Scan using testh11_campaignid_idx on testh11 a (cost=0.42..4,253.99 rows=2 width=20) (actual time=0.543..0.543 rows=0 loops=588)

  • Index Cond: (campaignid = b.tbl_campaign_id)
  • Filter: ((campaignid = COALESCE(campaignid)) AND (date(insert_datetime) >= '2020-05-23'::date) AND (date(insert_datetime) <= '2020-06-23'::date))
  • Rows Removed by Filter: 656
14. 468.048 468.048 ↓ 606.0 606 588

Index Scan using testh21_campaignid_idx on testh21 a_1 (cost=0.42..585.94 rows=1 width=20) (actual time=0.002..0.796 rows=606 loops=588)

  • Index Cond: (campaignid = b.tbl_campaign_id)
  • Filter: ((campaignid = COALESCE(campaignid)) AND (date(insert_datetime) >= '2020-05-23'::date) AND (date(insert_datetime) <= '2020-06-23'::date))
15. 1,424.976 1,424.976 ↓ 9.0 9 356,244

Index Scan using idx_user_id_tbl_user_c_1592227657_19 on tbl_user_channel f (cost=0.14..0.24 rows=1 width=422) (actual time=0.002..0.004 rows=9 loops=356,244)

  • Index Cond: (user_id = b.user_id)
16. 6,412.392 6,412.392 ↑ 1.0 1 3,206,196

Index Scan using "idx_FK_6958qvy_tbl_user_c_1592228774_151" on tbl_user_configurations g (cost=0.14..0.20 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3,206,196)

  • Index Cond: (user_id = e.user_id)
  • Filter: (msg_cat_id = COALESCE(msg_cat_id))
Planning time : 6.561 ms
Execution time : 27,477.860 ms