explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TGTQ : Optimization for: plan #BivM

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.079 40.924 ↓ 10.1 81 1

WindowAgg (cost=374,496.07..374,496.39 rows=8 width=68) (actual time=40.890..40.924 rows=81 loops=1)

2. 0.145 40.845 ↓ 10.1 81 1

GroupAggregate (cost=374,496.07..374,496.29 rows=8 width=60) (actual time=40.694..40.845 rows=81 loops=1)

  • Group Key: d.keyword_id, d.account_id, d.campaign_id, d.ad_group_id
3. 0.053 40.700 ↓ 10.1 81 1

Sort (cost=374,496.07..374,496.09 rows=8 width=28) (actual time=40.679..40.700 rows=81 loops=1)

  • Sort Key: d.keyword_id, d.account_id, d.campaign_id, d.ad_group_id
  • Sort Method: quicksort Memory: 31kB
4. 0.036 40.647 ↓ 10.1 81 1

Subquery Scan on d (cost=374,495.71..374,495.95 rows=8 width=28) (actual time=39.692..40.647 rows=81 loops=1)

5. 40.611 40.611 ↓ 10.1 81 1

CTE Scan on data (cost=374,495.71..374,495.87 rows=8 width=60) (actual time=39.691..40.611 rows=81 loops=1)

6.          

CTE data

7. 0.472 40.560 ↓ 10.1 81 1

Group (cost=374,495.63..374,495.71 rows=8 width=60) (actual time=39.688..40.560 rows=81 loops=1)

  • Group Key: d_1.keyword_id, d_1.campaign_id, d_1.ad_group_id
8. 1.037 40.088 ↓ 219.4 1,755 1

Sort (cost=374,495.63..374,495.65 rows=8 width=24) (actual time=39.686..40.088 rows=1,755 loops=1)

  • Sort Key: d_1.keyword_id, d_1.ad_group_id
  • Sort Method: quicksort Memory: 186kB
9. 0.682 39.051 ↓ 219.4 1,755 1

Append (cost=0.00..374,495.51 rows=8 width=24) (actual time=4.587..39.051 rows=1,755 loops=1)

10. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on yandex_direct_keyword d_1 (cost=0.00..0.00 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date) AND (account_id = 1) AND (campaign_id = 35227015))
11. 8.187 12.369 ↓ 109.0 436 1

Bitmap Heap Scan on data_yandex_direct_keyword_2018_11_0 d_2 (cost=3,135.59..274,678.41 rows=4 width=24) (actual time=4.584..12.369 rows=436 loops=1)

  • Recheck Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))
  • Filter: (campaign_id = 35227015)
  • Rows Removed by Filter: 56113
  • Heap Blocks: exact=1187
12. 4.182 4.182 ↑ 1.5 56,549 1

Bitmap Index Scan on data_yandex_direct_keyword_2018_11_0_account_id_date_idx (cost=0.00..3,135.59 rows=84,402 width=0) (actual time=4.182..4.182 rows=56,549 loops=1)

  • Index Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))
13. 4.904 7.539 ↓ 320.0 320 1

Bitmap Heap Scan on data_yandex_direct_keyword_2018_11_1 d_3 (cost=306.28..31,300.46 rows=1 width=24) (actual time=2.951..7.539 rows=320 loops=1)

  • Recheck Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))
  • Filter: (campaign_id = 35227015)
  • Rows Removed by Filter: 35208
  • Heap Blocks: exact=696
14. 2.635 2.635 ↓ 4.4 35,528 1

Bitmap Index Scan on data_yandex_direct_keyword_2018_11_1_account_id_date_idx (cost=0.00..306.28 rows=8,137 width=0) (actual time=2.635..2.635 rows=35,528 loops=1)

  • Index Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))
15. 4.401 6.729 ↓ 422.0 422 1

Bitmap Heap Scan on data_yandex_direct_keyword_2018_11_2 d_4 (cost=295.79..29,350.11 rows=1 width=24) (actual time=2.661..6.729 rows=422 loops=1)

  • Recheck Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))
  • Filter: (campaign_id = 35227015)
  • Rows Removed by Filter: 31166
  • Heap Blocks: exact=629
16. 2.328 2.328 ↓ 4.1 31,588 1

Bitmap Index Scan on data_yandex_direct_keyword_2018_11_2_account_id_date_idx (cost=0.00..295.79 rows=7,618 width=0) (actual time=2.328..2.328 rows=31,588 loops=1)

  • Index Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))
17. 7.080 11.730 ↓ 577.0 577 1

Bitmap Heap Scan on data_yandex_direct_keyword_2018_11_3 d_5 (cost=399.43..39,166.52 rows=1 width=24) (actual time=5.406..11.730 rows=577 loops=1)

  • Recheck Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))
  • Filter: (campaign_id = 35227015)
  • Rows Removed by Filter: 48283
  • Heap Blocks: exact=1194
18. 4.650 4.650 ↓ 6.1 62,101 1

Bitmap Index Scan on data_yandex_direct_keyword_2018_11_3_account_id_date_idx (cost=0.00..399.43 rows=10,149 width=0) (actual time=4.650..4.650 rows=62,101 loops=1)

  • Index Cond: ((account_id = 1) AND (date >= '2018-11-01'::date) AND (date <= '2018-11-30'::date))