| From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
|---|---|
| To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
| Cc: | sanyo(dot)moura(at)tatic(dot)net, pgsql-performance(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0 |
| Date: | 2018-11-28 04:17:50 |
| Message-ID: | 20181128041750.GH30707@telsasoft.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers Postg스포츠 토토SQL : Postg스포츠 |
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:
/message-id/CAKJS1f8qkcwr2DULd%2B04rBmubHkKzp4abuFykgoPUsVM-4-38g%40mail.gmail.com
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Rowley | 2018-11-28 04:22:51 | Re: Planning time of Generic plan for a table partitioned into a lot |
| Previous Message | Kyotaro HORIGUCHI | 2018-11-28 04:14:28 | Re: Tab completion for ALTER INDEX|TABLE ALTER COLUMN SET STATISTICS |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Scott Rankin | 2018-11-28 19:08:53 | Slow Bitmap Index Scan |
| Previous Message | David Rowley | 2018-11-28 04:03:15 | Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0 |