From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query - lots of temporary files.
Date: Wed, 10 Jun 2015 16:50:50 +0200
Message-ID: <55784ECA.6000406@2ndquadrant.com> (raw)
In-Reply-To: <CAGZ55DQ_TeUkpvuL4YvFneqrDEGV1PsiVyqcmJqpq-6hazxGsA@mail.gmail.com>
References: <CAGZ55DSB+9m4VxeptJnQOJYkcYpduJkpa5z24Tv3xKOgvdN3pg@mail.gmail.com>
<CAGTBQpa2ynp51h2xbbSuft7tm7nhbYQR3nzn-8FCGx=7-a-QMQ@mail.gmail.com>
<CAGZ55DQ_TeUkpvuL4YvFneqrDEGV1PsiVyqcmJqpq-6hazxGsA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/10/15 15:42, Johann Spies wrote:
> On 10 June 2015 at 15:02, Claudio Freire <klaussfreire@gmail.com
> <mailto:klaussfreire@gmail.com>> wrote:
>
>
> The joins are different on both versions, and the most likely culprit
> is the join against D. It's probably wrong, and the first query is
> building a cartesian product.
>
> Without more information about the schema it's difficult to be sure
> though.
>
>
> Thanks for your reply. I will experiment futher with different joins.
I don't know what you mean by "experimenting with joins" - that should
be determined by the schema.
The problematic piece of the explain plan is this:
-> Merge Join (cost=4384310.92..21202716.78 rows=6664163593
width=390)"
Output: a.ut, c.gt, b.go, b.gn, d.au"
Merge Cond: ((c.ut)::text = (d.rart_id)::text)"
That is, the planner expects ~6.7 billion rows, each ~390B wide. That's
~2.5TB of data that needs to be stored to disk (so that the sort can
process it).
The way the schema is designed might be one of the issues - ISTM the
'ut' column is somehow universal, mixing values referencing different
columns in multiple tables. Not only that's utterly misleading for the
planner (and may easily cause issues with huge intermediate results),
but it also makes formulating the queries very difficult. And of course,
the casting between text and int is not very good either.
Fix the schema to follow relational best practices - separate the values
into multiple columns, and most of this will go away.
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
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: Slow query - lots of temporary files.
In-Reply-To: <55784ECA.6000406@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