Hi, On 17.3.2015 08:41, Vivekanand Joshi wrote: > Hi Guys, > > Next level of query is following: > > If this works, I guess 90% of the problem will be solved. > > SELECT > COUNT(DISTINCT TARGET_ID) > FROM > S_V_F_PROMOTION_HISTORY_EMAIL PH > INNER JOIN S_V_D_CAMPAIGN_HIERARCHY CH > ON PH.TOUCHPOINT_EXECUTION_ID = > CH.TOUCHPOINT_EXECUTION_ID > WHERE > 1=1 > AND SEND_DT >= '2014-03-13' > AND SEND_DT <= '2015-03-14' > > > In this query, I am joining two views which were made earlier with CTEs. I > have replaced the CTE's with subqueries. The view were giving me output in > around 5-10 minutes and now I am getting the same result in around 3-4 > seconds. > > But when I executed the query written above, I am again stuck. I am > attaching the query plan as well the link. > > http://explain.depesz.com/s/REeu > > I can see most of the time is spending inside a nested loop and total > costs comes out be cost=338203.81..338203.82. Most of that cost comes from this: Seq Scan on s_f_promotion_history base (cost=0.00..283,333.66 rows=1 width=32) Filter: ((send_dt >= '2014-03-13 00:00:00'::timestamp without time zone) AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time That's a bit weird, I guess. If you analyze this part of the query separately, i.e. EXPLAIN ANALYZE SELECT * FROM s_f_promotion_history WHERE (send_dt >= '2014-03-13 00:00:00') AND (send_dt <= '2015-03-14 00:00:00') what do you get? I suspect it's used in EXISTS, i.e. something like this: ... WHERE EXISTS (SELECT * FROM s_f_promotion_history WHERE ... send_dt conditions ... AND touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) and this is transformed into a nested loop join. If there's a misestimate, this may be quite expensive - try to create index on s_f_promotion_history (touchpoint_execution_id, send_date) regards -- Tomas Vondra http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
