Hey guys, thanks a lot.
This is really helping. I am learning a lot. BTW, I changed CTE into subquery and it improved the performance by miles. I am getting the result in less than 3 seconds, though I am using a 24 GB ram server. It is still a great turnaround time as compared to previous execution time.
Now I will look into the bigger query. I read explain analyze and that helped a lot. I will be coming up with more questions tomorrow as bigger query still has got some problems.
On 16 Mar 2015 23:55, "Tomas Vondra" <tomas.vondra@xxxxxxxxxxxxxxx> wrote:
On 16.3.2015 18:49, Marc Mamin wrote:
>
>> Hi Team,
>>
>> This is the EXPLAIN ANALYZE for one of the view : S_V_D_CAMPAIGN_HIERARCHY:
FWIW, this is a somewhat more readable version of the plan:
http://explain.depesz.com/s/nbB
In the future, please do two things:
(1) Attach the plan as a text file, because the mail clients tend to
screw things up (wrapping long lines). Unless the plan is trivial,
of course - but pgsql-performance usually deals with complex stuff.
(2) Put the plan on explain.depesz.com helps too, because it's
considerably more readable (but always do 1, because resorces
placed somewhere else tends to disappear, and the posts then make
very little sense, which is bad when searching in the archives)
(3) Same for stuff pasted somewhere else - always attach it to the
message. For example I'd like to give you more accurate advice, but
I can't as http://pgsql.privatepaste.com/41207bea45 is unavailable.
>
>
> >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,
IMHO this is merely a consequence of using the CTE, which produces 52997
rows and is scanned 67508x as the inner relation of a nested loop. That
gives you 3577721476 tuples in total, and only 45360 are kept (hence
3577676116 are removed).
This is a prime example of why CTEs are not just aliases for subqueries,
but may actually cause serious trouble.
There are other issues (e.g. the row count estimate of the CTE is
seriously off, most likely because of the HashAggregate in the outer
branch), but that's a secondary issue IMHO.
Vivekanand, try this (in the order of intrusiveness):
(1) Get rid of the CTE, and just replace it with subselect in the FROM
part of the query, so instead of this:
WITH valid_executions AS (...)
SELECT ... FROM ... JOIN valid_executions ON (...)
you'll have something like this:
SELECT ... FROM ... JOIN (...) AS valid_executions ON (...)
This way the subselect will optimized properly.
(2) Replace the CTE with a materialized view, or a temporary table.
This has both advantages and disadvantages - the main advantage is
that you can create indexes, collect statistics. Disadvantage is
you have to refresh the MV, fill temporary table etc.
I expect (1) to improve the performance significantly, and (2) might
improve it even further by fixing the misestimates.
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
