Re: Performance issues

>Hi Team,
>
>This is the EXPLAIN ANALYZE for one of the view : S_V_D_CAMPAIGN_HIERARCHY:


    >Rows Removed by Join Filter: 3577676116

    That's quite a lot.
    You're possibly missing a clause in a join, resulting in a cross join.
    It is also helpful to put your result here:
    http://explain.depesz.com/
    regards,

    Marc Mamin


>
>===========================================
>
>
>Nested Loop  (cost=33666.96..37971.39 rows=1 width=894) (actual
>time=443.556..966558.767 rows=45360 loops=1)
>   Join Filter: (tp_exec.touchpoint_execution_id =
>valid_executions.touchpoint_execution_id)
>   Rows Removed by Join Filter: 3577676116
>   CTE valid_executions
>     ->  Hash Join  (cost=13753.53..31711.17 rows=1 width=8) (actual
>time=232.571..357.749 rows=52997 loops=1)
>           Hash Cond:
>((s_f_touchpoint_execution_status_history_1.touchpoint_execution_id =
>s_f_touchpoint_execution_status_history.touchpoint_execution_id) AND ((max(s
>_f_touchpoint_execution_status_history_1.creation_dt)) =
>s_f_touchpoint_execution_status_history.creation_dt))
>           ->  HashAggregate  (cost=6221.56..6905.66 rows=68410 width=16)
>(actual time=139.713..171.340 rows=76454 loops=1)
>                 ->  Seq Scan on s_f_touchpoint_execution_status_history
>s_f_touchpoint_execution_status_history_1  (cost=0.00..4766.04 rows=291104
>width=16) (actual ti
>me=0.006..38.582 rows=291104 loops=1)
>           ->  Hash  (cost=5493.80..5493.80 rows=135878 width=16) (actual
>time=92.737..92.737 rows=136280 loops=1)
>                 Buckets: 16384  Batches: 1  Memory Usage: 6389kB
>                 ->  Seq Scan on s_f_touchpoint_execution_status_history
>(cost=0.00..5493.80 rows=135878 width=16) (actual time=0.012..55.078
>rows=136280 loops=1)
>                       Filter: (touchpoint_execution_status_type_id = ANY
>('{3,4}'::integer[]))
>                       Rows Removed by Filter: 154824
>   ->  Nested Loop Left Join  (cost=1955.80..6260.19 rows=1 width=894)
>(actual time=31.608..3147.015 rows=67508 loops=1)
>         ->  Nested Loop  (cost=1955.67..6260.04 rows=1 width=776) (actual
>time=31.602..2912.625 rows=67508 loops=1)
>               ->  Nested Loop Left Join  (cost=1955.54..6259.87 rows=1
>width=658) (actual time=31.595..2713.696 rows=72427 loops=1)
>                     ->  Nested Loop Left Join  (cost=1955.40..6259.71
>rows=1 width=340) (actual time=31.589..2532.926 rows=72427 loops=1)
>                           ->  Nested Loop Left Join  (cost=1955.27..6259.55
>rows=1 width=222) (actual time=31.581..2354.662 rows=72427 loops=1)
>                                 ->  Nested Loop  (cost=1954.99..6259.24
>rows=1 width=197) (actual time=31.572..2090.104 rows=72427 loops=1)
>                                       ->  Nested Loop
>(cost=1954.71..6258.92 rows=1 width=173) (actual time=31.562..1802.857
>rows=72427 loops=1)
>                                             Join Filter:
>(camp_exec.campaign_id = wave.campaign_id)
>                                             Rows Removed by Join Filter:
>243
>                                             ->  Nested Loop
>(cost=1954.42..6254.67 rows=13 width=167) (actual time=31.551..1468.718
>rows=72670 loops=1)
>                                                   ->  Hash Join
>(cost=1954.13..6249.67 rows=13 width=108) (actual time=31.525..402.039
>rows=72670 loops=1)
>                                                         Hash Cond:
>((tp_exec.touchpoint_id = tp.touchpoint_id) AND (wave_exec.wave_id =
>tp.wave_id))
>                                                         ->  Hash Join
>(cost=1576.83..4595.51 rows=72956 width=90) (actual time=26.254..256.328
>rows=72956 loops=1)
>                                                               Hash Cond:
>(tp_exec.wave_execution_id = wave_exec.wave_execution_id)
>                                                               ->  Seq Scan
>on s_d_touchpoint_execution tp_exec  (cost=0.00..1559.56 rows=72956
>width=42) (actual time=0.005..76.099 rows=72956 loops=1)
>                                                               ->  Hash
>(cost=1001.37..1001.37 rows=46037 width=56) (actual time=26.178..26.178
>rows=46037 loops=1)
>                                                                     Buckets:
>8192  Batches: 1  Memory Usage: 4104kB
>                                                                     ->  Seq
>Scan on s_d_wave_execution wave_exec  (cost=0.00..1001.37 rows=46037
>width=56) (actual time=0.006..10.388 rows=46037 loops=1)
>                                                         ->  Hash
>(cost=212.72..212.72 rows=10972 width=26) (actual time=5.252..5.252
>rows=10972 loops=1)
>                                                               Buckets: 2048
>Batches: 1  Memory Usage: 645kB
>                                                               ->  Seq Scan
>on s_d_touchpoint tp  (cost=0.00..212.72 rows=10972 width=26) (actual
>time=0.012..2.319 rows=10972 loops=1)
>                                                   ->  Index Scan using
>s_d_campaign_execution_idx on s_d_campaign_execution camp_exec
>(cost=0.29..0.37 rows=1 width=67) (actual time=0.013..0.013 rows=1
>loops=72670)
>                                                         Index Cond:
>(campaign_execution_id = wave_exec.campaign_execution_id)
>                                             ->  Index Scan using
>s_d_wave_pkey on s_d_wave wave  (cost=0.29..0.31 rows=1 width=22) (actual
>time=0.003..0.003 rows=1 loops=72670)
>                                                   Index Cond: (wave_id =
>wave_exec.wave_id)
>                                       ->  Index Scan using
>s_d_campaign_pkey on s_d_campaign camp  (cost=0.29..0.32 rows=1 width=40)
>(actual time=0.003..0.003 rows=1 loops=72427)
>                                             Index Cond: (campaign_id =
>camp_exec.campaign_id)
>                                 ->  Index Scan using s_d_content_pkey on
>s_d_content content  (cost=0.28..0.30 rows=1 width=33) (actual
>time=0.002..0.003 rows=1 loops=72427)
>                                       Index Cond: (tp_exec.content_id =
>content_id)
>                           ->  Index Scan using s_d_message_type_pkey on
>s_d_message_type message_type  (cost=0.13..0.15 rows=1 width=120) (actual
>time=0.001..0.002 rows=1 loops=72427)
>                                 Index Cond: (tp_exec.message_type_id =
>message_type_id)
>                     ->  Index Scan using s_d_group_pkey on s_d_group grup
>(cost=0.13..0.15 rows=1 width=320) (actual time=0.001..0.002 rows=1
>loops=72427)
>                           Index Cond: (camp_exec.group_id = group_id)
>               ->  Index Scan using d_channel_pk on s_d_channel_type channel
>(cost=0.13..0.15 rows=1 width=120) (actual time=0.001..0.002 rows=1
>loops=72427)
>                     Index Cond: (channel_type_id = tp.channel_type_id)
>         ->  Index Scan using s_d_category_pkey on s_d_category "CATEGORY"
>(cost=0.13..0.15 rows=1 width=120) (actual time=0.001..0.002 rows=1
>loops=67508)
>               Index Cond: (camp.category_id = category_id)
>   ->  CTE Scan on valid_executions  (cost=0.00..0.02 rows=1 width=8)
>(actual time=0.004..6.803 rows=52997 loops=67508)
> Total runtime: 966566.574 ms
>
>========================================================
>
>Can you please see it an let me know where is the issue?
>
>
>-----Original Message-----
>From: Gavin Flower [mailto:GavinFlower@archidevsys.co.nz]
>Sent: Sunday, March 15, 2015 3:02 AM
>To: Varadharajan Mukundan
>Cc: Tomas Vondra; vjoshi@zetainteractive.com; Scott Marlowe;
>pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] Performance issues
>
>On 15/03/15 10:23, Varadharajan Mukundan wrote:
>> Hi Gavin,
>>
>> Vivekanand is his first mail itself mentioned the below configuration
>> of postgresql.conf. It looks good enough to me.
>>
>> Total Memory : 8 GB
>>
>> shared_buffers = 2GB
>>
>> work_mem = 64MB
>>
>> maintenance_work_mem = 700MB
>>
>> effective_cache_size = 4GB
>
>
>Sorry, it didn't register when I read it!
>(Probably reading too fast)
>>
>> On Sat, Mar 14, 2015 at 10:06 PM, Gavin Flower
>> <GavinFlower@archidevsys.co.nz> wrote:
>>> On 14/03/15 13:12, Tomas Vondra wrote:
>>>> On 14.3.2015 00:28, Vivekanand Joshi wrote:
>>>>> Hi Guys,
>>>>>
>>>>> So here is the full information attached as well as in the link
>>>>> provided below:
>>>>>
>>>>> http://pgsql.privatepaste.com/41207bea45
>>>>>
>>>>> I can provide new information as well.
>>>> Thanks.
>>>>
>>>> We still don't have EXPLAIN ANALYZE - how long was the query running
>>>> (I assume it got killed at some point)? It's really difficult to
>>>> give you any advices because we don't know where the problem is.
>>>>
>>>> If EXPLAIN ANALYZE really takes too long (say, it does not complete
>>>> after an hour / over night), you'll have to break the query into
>>>> parts and first tweak those independently.
>>>>
>>>> For example in the first message you mentioned that select from the
>>>> S_V_D_CAMPAIGN_HIERARCHY view takes ~9 minutes, so start with that.
>>>> Give us EXPLAIN ANALYZE for that query.
>>>>
>>>> Few more comments:
>>>>
>>>> (1) You're using CTEs - be aware that CTEs are not just aliases, but
>>>>       impact planning / optimization, and in some cases may prevent
>>>>       proper optimization. Try replacing them with plain views.
>>>>
>>>> (2) Varadharajan Mukundan already recommended you to create index on
>>>>       s_f_promotion_history.send_dt. Have you tried that? You may also
>>>>       try creating an index on all the columns needed by the query, so
>>>>       that "Index Only Scan" is possible.
>>>>
>>>> (3) There are probably additional indexes that might be useful here.
>>>>       What I'd try is adding indexes on all columns that are either a
>>>>       foreign key or used in a WHERE condition. This might be an
>>>>       overkill in some cases, but let's see.
>>>>
>>>> (4) I suspect many of the relations referenced in the views are not
>>>>       actually needed in the query, i.e. the join is performed but
>>>>       then it's just discarded because those columns are not used.
>>>>       Try to simplify the views as much has possible - remove all the
>>>>       tables that are not really necessary to run the query. If two
>>>>       queries need different tables, maybe defining two views is
>>>>       a better approach.
>>>>
>>>> (5) The vmstat / iostat data are pretty useless - what you provided are
>>>>       averages since the machine was started, but we need a few samples
>>>>       collected when the query is running. I.e. start the query, and
>>>> then
>>>>       give us a few samples from these commands:
>>>>
>>>>       iostat -x -k 1
>>>>       vmstat 1
>>>>
>>>>> Would like to see if queries of these type can actually run in
>>>>> postgres server?
>>>> Why not? We're running DWH applications on tens/hundreds of GBs.
>>>>
>>>>> If yes, what would be the minimum requirements for hardware? We
>>>>> would like to migrate our whole solution on PostgreSQL as we can
>>>>> spend on hardware as much as we can but working on a proprietary
>>>>> appliance is becoming very difficult for us.
>>>> That's difficult to say, because we really don't know where the
>>>> problem is and how much the queries can be optimized.
>>>>
>>>>
>>> I notice that no one appears to have suggested the default setting in
>>> postgresql.conf - these need changing as they are initially set up
>>> for small machines, and to let PostgreSQL take anywhere near full
>>> advantage of a box have large amounts of RAM, you need to change some
>>> of the configuration settings!
>>>
>>> For example 'temp_buffers' (default 8MB) and 'maintenance_work_mem'
>>> (default
>>> 16MB) should be drastically increased,  and there are other settings
>>> that need changing.  The precise values depend on many factors, but
>>> the initial values set by default are definitely far too small for your
>>> usage.
>>>
>>> Am assuming that you are looking at PostgreSQL 9.4.
>>>
>>>
>>>
>>> Cheers,
>>> Gavin
>>>
>>>
>>
>>
>>
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>