Horton

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

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>

View thread
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
To: Yuya Watari <watari.yuya@gmail.com>
Cc: Andrey Lepikhov <a.lepikhov@postgrespro.ru>, Thom Brown <thom@linux.com>, David Rowley <dgrowleyml@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Zhang Mingli <zmlpostgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: 2023-08-03T13:29:07Z
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 Wed, Aug 2, 2023 at 12:11 PM Yuya Watari <watari.yuya@gmail.com> wrote:

> Hello,
>
> I really appreciate sharing very useful scripts and benchmarking results.
>
> On Fri, Jul 28, 2023 at 6:51 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> > Given that most of the developers run assert enabled builds it would
> > be good to bring down the degradation there while keeping the
> > excellent speedup in non-assert builds.
>
> From my observation, this degradation in assert enabled build is
> caused by verifying the iteration results of EquivalenceMembers. My
> patch uses Bitmapset-based indexes to speed up the iteration. When
> assertions are enabled, we verify that the result of the iteration is
> the same with and without the indexes. This verification results in
> executing a similar loop three times, which causes the degradation. I
> measured planning time by using your script without this verification.
> The results are as follows:
>
> Master: 144.55 ms
> Patched (v19): 529.85 ms
> Patched (v19) without verification: 78.84 ms
> (*) All runs are with assertions.
>
> As seen from the above, verifying iteration results was the cause of
> the performance degradation. I agree that we should avoid such
> degradation because it negatively affects the development of
> PostgreSQL. Removing the verification when committing this patch is
> one possible option.
>

If you think that the verification is important to catch bugs, you may want
to encapsulate it with an #ifdef .. #endif such that the block within is
not compiled by default. See OPTIMIZER_DEBUG for example.


>
> > Queries on partitioned tables eat a lot of memory anyways, increasing
> > that further should be avoided.
> >
> > I have not studied the patches. But I think the memory increase has to
> > do with our Bitmapset structure. It's space inefficient when there are
> > thousands of partitions involved. See my comment at [2]
>
> Thank you for pointing this out. I have never considered the memory
> usage impact of this patch. As you say, the Bitmapset structure caused
> this increase. I will try to look into this further.
>
>
Do you think that the memory measurement patch I have shared in those
threads is useful in itself? If so, I will start another proposal to
address it.

-- 
Best Wishes,
Ashutosh Bapat