Re: Materialized view performance problems
| От | Tom McLoughlin |
|---|---|
| Тема | Re: Materialized view performance problems |
| Дата | |
| Msg-id | CA+dBN5OM45poaFtDzp6j6UHsbD1ZH7H_SeWx6Vz+HewJLVL-QA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Materialized view performance problems (Andreas Kretschmer <andreas@a-kretschmer.de>) |
| Ответы |
Re: Materialized view performance problems
(Andreas Kretschmer <akretschmer@spamfence.net>)
|
| Список | pgsql-performance |
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@a-kretschmer.de> wrote:
> Tom McLoughlin <tom@dynamiccreative.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.
В списке pgsql-performance по дате отправления: