1

I have a database that includes the tables shown below:

CREATE TABLE metadata
(
    keyword_id bigint NOT NULL,
    campaign_id bigint NOT NULL,
    org_id bigint NOT NULL,
    keyword character varying(255) COLLATE pg_catalog."default",
    bid_currency character varying(5) COLLATE pg_catalog."default",
    bid_amount double precision,
    status character varying(16) COLLATE pg_catalog."default",
    ad_group_id bigint,
    keyword_type character varying(16) COLLATE pg_catalog."default" NOT NULL,
    country_or_region character varying(3) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT idx_primary PRIMARY KEY (org_id, keyword_type, country_or_region, campaign_id, keyword_id)
);

CREATE TABLE report
(
    org_id bigint NOT NULL,
    campaign_id bigint NOT NULL,
    ad_group_id bigint NOT NULL,
    keyword_id bigint NOT NULL,
    keyword character varying(255) COLLATE pg_catalog."default",
    impressions bigint,
    taps bigint,
    installs bigint,
    local_spend_currency character varying(5) COLLATE pg_catalog."default",
    report_date date NOT NULL,
    country_or_region character varying(5) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT unx_org_cty_camp_key_date_di UNIQUE (org_id, country_or_region, campaign_id, keyword_id, report_date)

);

And I have a query like the following one:

Select 
r.report_date as reportDate, 
max(r.local_spend_currency) as currency, 
sum(r.local_spend_amount) as spend, 
sum(r.impressions) as impressions, 
sum(r.taps) as taps, 
...
from report r 
left join  metadata m  
    on m.org_id = r.org_id 
    and m.country_or_region = r.country_or_region 
    and m.campaign_id = r.campaign_id  
    and m.keyword_id = r.keyword_id  
 where m.org_id = 1 
     and m.keyword_type = 'KEYWORD'
           and (r.report_date between '2019-09-01' and '2019-10-10')
    group by r.report_date 
 order  by r.report_date
    offset 0  limit 180;

And for this query, I want to create indexes. I just created some indexes but they dont work.

For now, the indexes I defined are:

Metadata:

PRIMARY KEY (org_id, keyword_type, country_or_region, campaign_id, keyword_id);

Report:

unx_org_cty_camp_key_date_di (org_id, country_or_region, campaign_id, keyword_id, report_date);
idx_kr_org_date(org_id, report_date);

Explain results look like below:

"Limit  (cost=2147246.64..2147248.79 rows=33 width=294)"
"  ->  GroupAggregate  (cost=2147246.64..2147248.79 rows=33 width=294)"
"        Group Key: m.org_id, m.country_or_region, m.campaign_id, m.keyword_id"
"        ->  Sort  (cost=2147246.64..2147246.72 rows=33 width=98)"
"              Sort Key: m.country_or_region, m.campaign_id, m.keyword_id"
"              ->  Gather  (cost=489239.37..2147245.81 rows=33 width=98)"
"                    Workers Planned: 2"
"                    ->  Parallel Hash Join  (cost=488239.37..2146242.51 rows=14 width=98)"
"                          Hash Cond: (((r.country_or_region)::text = (m.country_or_region)::text) AND (r.campaign_id = m.campaign_id) AND (r.keyword_id = m.keyword_id))"
"                          ->  Parallel Bitmap Heap Scan on report r  (cost=22465.50..1661304.60 rows=332449 width=99)"
"                                Recheck Cond: ((org_id = 479360) AND (report_date >= '2019-09-01'::date) AND (report_date <= '2019-10-10'::date))"
"                                ->  Bitmap Index Scan on idx_kr_org_date  (cost=0.00..22266.03 rows=797877 width=0)"
"                                      Index Cond: ((org_id = 479360) AND (report_date >= '2019-09-01'::date) AND (report_date <= '2019-10-10'::date))"
"                          ->  Parallel Hash  (cost=443861.05..443861.05 rows=900390 width=26)"
"                                ->  Parallel Seq Scan on metadata m  (cost=0.00..443861.05 rows=900390 width=26)"
"                                      Filter: ((org_id = 479360) AND ((keyword_type)::text = 'KEYWORD'::text))"

Explain (Analyze):

"Limit  (cost=2397014.94..2397019.73 rows=87 width=268) (actual time=121193.808..122001.572 rows=97 loops=1)"
"  ->  GroupAggregate  (cost=2397014.94..2397019.73 rows=87 width=268) (actual time=121193.807..122001.553 rows=97 loops=1)"
"        Group Key: r.report_date"
"        ->  Sort  (cost=2397014.94..2397015.16 rows=87 width=72) (actual time=121189.172..121451.846 rows=2295035 loops=1)"
"              Sort Key: r.report_date"
"              Sort Method: external merge  Disk: 184168kB"
"              ->  Gather  (cost=466773.88..2397012.14 rows=87 width=72) (actual time=118071.231..120119.593 rows=2295035 loops=1)"
"                    Workers Planned: 2"
"                    Workers Launched: 2"
"                    ->  Parallel Hash Join  (cost=465773.88..2396003.44 rows=36 width=72) (actual time=118085.595..119674.465 rows=765012 loops=3)"
"                          Hash Cond: (((r.country_or_region)::text = (m.country_or_region)::text) AND (r.campaign_id = m.campaign_id) AND (r.keyword_id = m.keyword_id))"
"                          ->  Parallel Seq Scan on report r  (cost=0.00..1889731.54 rows=877718 width=99) (actual time=5236.366..112636.044 rows=765012 loops=3)"
"                                Filter: ((report_date >= '2019-07-01'::date) AND (report_date <= '2019-10-10'::date) AND (org_id = 479360))"
"                                Rows Removed by Filter: 14384864"
"                          ->  Parallel Hash  (cost=443861.05..443861.05 rows=900390 width=26) (actual time=4760.854..4760.854 rows=727133 loops=3)"
"                                Buckets: 65536  Batches: 64  Memory Usage: 2688kB"
"                                ->  Parallel Seq Scan on metadata m  (cost=0.00..443861.05 rows=900390 width=26) (actual time=0.009..4326.621 rows=727133 loops=3)"
"                                      Filter: ((org_id = 479360) AND ((keyword_type)::text = 'KEYWORD'::text))"
"                                      Rows Removed by Filter: 2826534"
"Planning Time: 0.436 ms"
"Execution Time: 122084.375 ms"

So I want to create indexes for metadata and report tables to cover all parts of conditions. How can I create them?

  • 1
    @jjanes, they were added. FYI – Sha Oct 11 at 21:09
  • Because it getting such a large percentage of rows from each table, it would probably prefer to scan the table in bulk rather than to use the indexes. You can force it to use the usable indexes by SET enable_seqscan=off, just to see what happens. Don't be surprised if that actually makes it slower, though. – jjanes Oct 12 at 13:03
  • @jjanes I tried already force indexing. It's better. But I wonder that these indexes will be enough with force indexing for best performance which can be handled? – Sha Oct 12 at 13:26
  • With seeing the plan which uses the index, that is hard to say. Also, only one of the plans you posted can possibly belong to your posted query--the other is grouping by something different. – jjanes Oct 12 at 13:45
0

Rows removed are really high, so it would be first indication for having index on report_date. But if your data has very cardinality for report_date then index selection may differ.

Did you try adding index for just report_date order by desc nulls last?

One of the merges is using disk which is costly. So adding the index on report_date would help.

Also considering having another single column index for org_id.

  • tried but no effect. – Sha Oct 12 at 8:00
  • Did you try after doing VACUUM ANALYZE and then REINDEX TABLE for both the tables? If your stats are outdated, query optimization efforts are useless. – Shaunak Sontakke Oct 12 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.