Re: Materialized view performance problems

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx> wrote:


> Tom McLoughlin <tom@xxxxxxxxxxxxxxxxxxx> 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.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux