Re: Materialized view performance problems

Thank you very much for your help.

It's difficult for me to run analyse explain for the query given because it
takes so long. However, the query below has a similar structure but has
less data to process.

create materialized view temp_camp_perf_unaggr
as
select
account_websites.id as website_id,
account_websites.namespace as website_namespace,
scenario_campaign_vendor_instances.inventory_disabled as
inventory_disabled,
scenario_campaign_vendor_instances.condition_disabled as
condition_disabled,
scenario_campaign_vendor_instances.manually_disabled as paused,
scenario_campaigns.id as campaign_id,
scenario_campaign_performances.*,
(select campaign_category_lookup.category_id from
campaign_category_lookup where campaign_category_lookup.campaign_id =
scenario_campaigns.id limit 1) as category_id
from
scenarios
inner join account_websites
on scenarios.website_id = account_websites.id
inner join scenario_campaigns
on scenario_campaigns.scenario_id = scenarios.id
left outer join scenario_campaign_vendor_instances
on scenario_campaigns.id =
scenario_campaign_vendor_instances.campaign_id
left outer join scenario_campaign_performances
on scenario_campaign_performances.api_id =
scenario_campaign_vendor_instances.api_id
and scenario_campaign_performances.date >= (date_trunc('month', now())
- '1 month'::interval)::date -- start of previous month
where
scenarios.deleted_at is null
and scenario_campaign_performances.campaign_name is not null
and account_websites.active = 't';

Here's it's EXPLAIN ANALYSE output:

Hash Join (cost=13094.58..3450145.63 rows=373025 width=220) (actual
time=87677.770..226340.511 rows=232357 loops=1)
Hash Cond: (scenario_campaign_performances.api_id =
scenario_campaign_vendor_instances.api_id)
-> Seq Scan on scenario_campaign_performances (cost=0.00..325848.93
rows=351341 width=191) (actual time=86942.746..221871.357 rows=230889
loops=1)
Filter: ((campaign_name IS NOT NULL) AND (date >=
((date_trunc('month'::text, now()) - '1 mon'::interval))::date))
Rows Removed by Filter: 77185
-> Hash (cost=12250.80..12250.80 rows=67502 width=37) (actual
time=709.034..709.034 rows=28545 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 1997kB
-> Hash Join (cost=6621.17..12250.80 rows=67502 width=37)
(actual time=164.772..690.399 rows=48805 loops=1)
Hash Cond: (scenario_campaign_vendor_instances.campaign_id =
scenario_campaigns.id)
-> Seq Scan on scenario_campaign_vendor_instances
(cost=0.00..3817.06 rows=130006 width=15) (actual time=0.049..405.396
rows=149939 loops=1)
-> Hash (cost=5641.32..5641.32 rows=78388 width=26)
(actual time=164.647..164.647 rows=49081 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 2839kB
-> Hash Join (cost=105.59..5641.32 rows=78388
width=26) (actual time=55.543..145.975 rows=49081 loops=1)
Hash Cond: (scenario_campaigns.scenario_id =
scenarios.id)
-> Seq Scan on scenario_campaigns
(cost=0.00..4185.71 rows=150971 width=8) (actual time=0.024..47.185
rows=150591 loops=1)
-> Hash (cost=90.56..90.56 rows=1202 width=26)
(actual time=55.499..55.499 rows=1428 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
79kB
-> Hash Join (cost=18.49..90.56
rows=1202 width=26) (actual time=48.435..54.931 rows=1428 loops=1)
Hash Cond: (scenarios.website_id =
account_websites.id)
-> Seq Scan on scenarios
(cost=0.00..52.15 rows=2108 width=8) (actual time=0.015..5.723 rows=2052
loops=1)
Filter: (deleted_at IS NULL)
Rows Removed by Filter: 201
-> Hash (cost=14.54..14.54
rows=316 width=22) (actual time=48.402..48.402 rows=289 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 16kB
-> Seq Scan on
account_websites (cost=0.00..14.54 rows=316 width=22) (actual
time=26.373..48.259 rows=289 loops=1)
Filter: active
Rows Removed by Filter:
211
SubPlan 1
-> Limit (cost=0.28..8.30 rows=1 width=4) (actual time=0.014..0.014
rows=0 loops=232357)
-> Index Scan using campaign_category_lookup_campaign_id_idx on
campaign_category_lookup (cost=0.28..8.30 rows=1 width=4) (actual
time=0.014..0.014 rows=0 loops=232357)
Index Cond: (campaign_id = scenario_campaigns.id)
Total runtime: 228236.708 ms

On 6 January 2016 at 22:10, Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
wrote:

>
>
> > Tom McLoughlin <tom(at)dynamiccreative(dot)com> hat am 6. Januar 2016 um 09:08
> > geschrieben:
> >
> >
>
> >
> > As you can see below it's a big query, and I didn't want to overwhelm
> > everyone with the schema, so let me know what bits you might need to
> help!
> >
> > Any help improving the performance will be greatly appreciated.
>
> can you show us the EXPLAIN ANALYSE - Output? I see a LOT of seq-scans,
> maybe
> you should create some indexes.
>