pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance issues
Date: Mon, 16 Mar 2015 19:24:24 +0100
Message-ID: <55071FD8.6050106@2ndquadrant.com> (raw)
In-Reply-To: <B6F6FD62F2624C4C9916AC0175D56D8828B843AC@jenmbs01.ad.intershop.net>
References: <84a6fe4be04c3ef0a714851977860ac8@mail.gmail.com>
	<CACKkDGF=Qx_fUje5meLAMFWMJ-c7AUZ9V4GDtBDpr3zO3VKwpA@mail.gmail.com>
	<160b8780d89add7f23c4eb9c3debb99d@mail.gmail.com>
	<CACKkDGETLNUON-bLM0gCo_r-+ts3Q29aQwmargv1Usi=PgQvVg@mail.gmail.com>
	<CANwLwatODrcwDgO+JXG-L=jJJQTucvUrur01E-y_tE_PT8i7kQ@mail.gmail.com>
	<55034A48.4040409@2ndquadrant.com>
	<CANwLwavsL-+SDRB7VKxQ_OJq-T=MVQV6T6qH5Aug9PaSWt2C0w@mail.gmail.com>
	<CACKkDGHOnb2efYvLj557E5pSuzS+Wmu9dqG3Z=b_dbYhpi7muQ@mail.gmail.com>
	<CAOR=d=2L86fB+HWX8h078tYsd7LVKKFPQb=jfd8YNRU1Fc7mUA@mail.gmail.com>
	<849e75d9463938372dd4c516f5c5d89c@mail.gmail.com>
	<55037CEA.5050906@2ndquadrant.com>
	<5504A2CE.30007@archidevsys.co.nz>
	<CACKkDGFjjPG4BTJgZMzHrD608XaGdNzLRthmC6hhuLa41EdNtA@mail.gmail.com>
	<5504A8E7.7080008@archidevsys.co.nz>
	<698495ca8f09d9b320b81b381d5e7540@mail.gmail.com>
	<B6F6FD62F2624C4C9916AC0175D56D8828B843AC@jenmbs01.ad.intershop.net>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



view thread (59+ messages)  latest in thread

Message-ID: <55071FD8.6050106@2ndquadrant.com>
Permalink:  ../55071FD8.6050106@2ndquadrant.com/
Also on:    postgresql.org/message-id/55071FD8.6050106@2ndquadrant.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com
  Subject: Re: Performance issues
  In-Reply-To: <55071FD8.6050106@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox