Horton

Re: [PoC] Reducing planning time when tables have many partitions

David Rowley <dgrowleyml@gmail.com>

View thread
From: David Rowley <dgrowleyml@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Yuya Watari <watari.yuya@gmail.com>, Andrey Lepikhov <a.lepikhov@postgrespro.ru>, Thom Brown <thom@linux.com>, Zhang Mingli <zmlpostgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: 2023-03-08T21:23:28Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Update wording in optimizer/README for EquivalenceClasses

  2. Speedup child EquivalenceMember lookup in planner

  3. Allow planner to use Merge Append to efficiently implement UNION

  4. Remove trailing zero words from Bitmapsets

  5. Make Vars be outer-join-aware.

  6. Avoid making commutatively-duplicate clauses in EquivalenceClasses.

On Thu, 9 Mar 2023 at 01:34, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> David, do you intend to continue to be involved in reviewing this one?

Yes. I'm currently trying to make a few Bitmapset improvements which
include the change made in this thread's 0001 patch over on [1].

For the main patch, I've been starting to wonder if it should work
completely differently.  Instead of adding members for partitioned and
inheritance children, we could just translate the Vars from child to
top-level parent and find the member that way. I wondered if this
method might be even faster as it would forego
add_child_rel_equivalences(). I think we'd still need em_is_child for
UNION ALL children.  So far, I've not looked into this in detail. I
was hoping to find an idea that would allow some means to have the
planner realise that a LIST partition which allows a single Datum
could skip pushing base quals which are constantly true. i.e:

create table lp (a int) partition by list(a);
create table lp1 partition of lp for values in(1);
explain select * from lp where a = 1;

 Seq Scan on lp1 lp  (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 1)

David

[1] https://postgr.es/m/CAApHDvq9eq0W_aFUGrb6ba28ieuQN4zM5Uwqxy7+LMZjJc+VGg@mail.gmail.com