Re: speeding up planning with partitions

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: Imai Yoshikazu <yoshikazu_i443(at)live(dot)jp>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, "jesper(dot)pedersen(at)redhat(dot)com" <jesper(dot)pedersen(at)redhat(dot)com>, "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: speeding up planning with partitions
Date: 2019-04-01 08:37:23
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2019/04/01 3:46, Tom Lane wrote:
> One thing that I intentionally left out of the committed patch was changes
> to stop short of scanning the whole simple_rel_array when looking only for
> baserels. I thought that had been done in a rather piecemeal fashion
> and it'd be better to address it holistically, which I've now done in the
> attached proposed patch.
> This probably makes little if any difference in the test cases we've
> mostly focused on in this thread, since there wouldn't be very many
> otherrels anyway now that we don't create them for pruned partitions.
> However, in a case where there's a lot of partitions that we can't prune,
> this could be useful.
> I have not done any performance testing to see if this is actually
> worth the trouble, though. Anybody want to do that?

Thanks for creating the patch.

I spent some time looking for cases where this patch would provide
recognizable benefit, but couldn't find one. As one would suspect, it's
hard to notice it if only looking at the overall latency of the query,
because time spent doing other things with such plans tends to be pretty
huge anyway (both in the planner itself and other parts of the backend).
I even devised a query on a partitioned table such that planner has to
process all partitions, but ExecInitAppend can prune all but one, thus
reducing the time spent in the executor, but still wasn't able to see an
improvement in the overall latency of the query due to planner not doing
looping over the long simple_rel_array.


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Christian Ullrich 2019-04-01 08:38:56 C_C_A animal on HEAD gets stuck in initdb
Previous Message Magnus Hagander 2019-04-01 08:25:57 Re: Enable data checksums by default