Horton

Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, vignesh C <vignesh21@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, Richard Guo <guofenglinux@gmail.com>, David Rowley <dgrowleyml@gmail.com>
Date: 2024-02-18T23:05:44Z
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. Add assertion to verify derived clause has constant RHS

  2. Make derived clause lookup in EquivalenceClass more efficient

  3. Doc: improve documentation for jsonpath behavior.

  4. Work around implementation restriction in adjust_appendrel_attrs.

Hi,

After taking a look at the patch optimizing SpecialJoinInfo allocations,
I decided to take a quick look at this one too. I don't have any
specific comments on the code yet, but it seems quite a bit more complex
than the other patch ... it's introducing a HTAB into the optimizer,
surely that has costs too.

I started by doing the same test as with the other patch, comparing
master to the two patches (applied independently and both). And I got
about this (in MB):

  tables    master     sjinfo    rinfo      both
  -----------------------------------------------
       2        37         36       34        33
       3       138        129      122       113
       4       421        376      363       318
       5      1495       1254     1172       931

Unlike the SpecialJoinInfo patch, I haven't seen any reduction in
planning time for this one.

The reduction in allocated memory is nice. I wonder what's allocating
the remaining memory, and we'd have to do to reduce it further.

However, this is a somewhat extreme example - it's joining 5 tables,
each with 1000 partitions, using a partition-wise join. It reduces the
amount of memory, but the planning time is still quite high (and
essentially the same as without the patch). So it's not like it'd make
them significantly more practical ... do we have any other ideas/plans
how to improve that?

AFAIK we don't expect this to improve "regular" cases with modest number
of tables / partitions, etc. But could it make them slower in some case?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company