>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@xxxxxxxxxxxxxxxxx] >Sent: Sunday, March 15, 2015 3:02 AM >To: Varadharajan Mukundan >Cc: Tomas Vondra; vjoshi@xxxxxxxxxxxxxxxxxxx; Scott Marlowe; >pgsql-performance@xxxxxxxxxxxxxx >Subject: Re: 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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
