From: Justin Pryzby <pryzby@telsasoft.com>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: sanyo.moura@tatic.net
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
Date: Tue, 27 Nov 2018 22:17:50 -0600
Message-ID: <20181128041750.GH30707@telsasoft.com> (raw)
In-Reply-To: <CAKJS1f8cZUzzGQnYKSfzeEe-fscQc9s5wPy4ZNPn-Uq=pDcODw@mail.gmail.com>
References: <CAO698qZnrxoZu7MEtfiJmpmUtz3AVYFVnwzR+pqjF=rmKBTgpw@mail.gmail.com>
<CAKJS1f8cZUzzGQnYKSfzeEe-fscQc9s5wPy4ZNPn-Uq=pDcODw@mail.gmail.com>
On Wed, Nov 28, 2018 at 05:03:15PM +1300, David Rowley wrote:
> Does it still take that long after running ANALYZE on the partitioned table?
Yes ; I've just reproduced the problem with a variation on Sanyo's query,
retrofitted onto the empty "partbench" table you used for testing in July:
https://www.postgresql.org/message-id/CAKJS1f8qkcwr2DULd%2B04rBmubHkKzp4abuFykgoPUsVM-4-38g%40mail.g...
Note, Sanyo's original query appears to be a poor-man's window function,
joining two subqueries on a.value=max(b.value).
I reduced issue to this:
|postgres=# ANALYZE partbench;
|postgres=# explain SELECT * FROM (SELECT a.i2-b.i2 n FROM partbench a, (SELECT i2 FROM partbench)b)b, (SELECT max(partbench.i3) m FROM partbench, (SELECT i3 FROM partbench)y )y WHERE m=n;
|Time: 31555.582 ms (00:31.556)
Justin
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: pryzby@telsasoft.com, david.rowley@2ndquadrant.com, sanyo.moura@tatic.net, pgsql-performance@lists.postgresql.org
Subject: Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0
In-Reply-To: <20181128041750.GH30707@telsasoft.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