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 <andr...@a-kretschmer.de> wrote: > > > > Tom McLoughlin <t...@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. >