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

Yuya Watari <watari.yuya@gmail.com>

From: Yuya Watari <watari.yuya@gmail.com>

To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>, Thom Brown <thom@linux.com>

Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Zhang Mingli <zmlpostgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <dgrowleyml@gmail.com>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>

Date: 2022-11-29T08:58:25Z

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 →

Update wording in optimizer/README for EquivalenceClasses
- d7c04db27aeb 18.0 landed
Speedup child EquivalenceMember lookup in planner
- d69d45a5a956 18.0 landed
Allow planner to use Merge Append to efficiently implement UNION
- 66c0185a3d14 17.0 cited
Remove trailing zero words from Bitmapsets
- a8c09daa8bb1 17.0 cited
Make Vars be outer-join-aware.
- 2489d76c4906 16.0 cited
Avoid making commutatively-duplicate clauses in EquivalenceClasses.
- a5fc46414deb 16.0 cited

Attachments

v9-0001-Apply-eclass_member_speedup_v3.patch.patch (application/octet-stream) patch v9-0001
v9-0002-Revert-one-of-the-optimizations.patch (application/octet-stream) patch v9-0002
v9-0003-Use-conventional-algorithm-for-smaller-size-cases.patch (application/octet-stream) patch v9-0003
v9-0004-Fix-incorrect-assertion.patch (application/octet-stream) patch v9-0004

Dear Andrey and Thom,

Thank you for reviewing and testing the patch. I really apologize for
my late response.

On Tue, Nov 8, 2022 at 8:31 PM Andrey Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> Looking into find_em_for_rel() changes I see that you replaced
> if (bms_is_subset(em->em_relids, rel->relids)
> with assertion statement.
> According of get_ecmember_indexes(), the em_relids field of returned
> equivalence members can contain relids, not mentioned in the relation.
> I don't understand, why it works now? For example, we can sort by t1.x,
> but have an expression t1.x=t1.y*t2.z. Or I've missed something? If it
> is not a mistake, maybe to add a comment why assertion here isn't failed?

As you pointed out, changing the bms_is_subset() condition to an
assertion is logically incorrect here. Thank you for telling me about
it. I fixed it and attached the modified patch to this email.

On Thu, Nov 17, 2022 at 9:05 PM Thom Brown <thom@linux.com> wrote:
> No issues with applying. Created 1024 partitions, each of which is
> partitioned into 64 partitions.
>
> I'm getting a generic planning time of 1415ms. Is that considered
> reasonable in this situation? Bear in mind that the planning time
> prior to this patch was 282311ms, so pretty much a 200x speedup.

Thank you for testing the patch with an actual query. This speedup is
very impressive. When I used an original query with 1024 partitions,
its planning time was about 200ms. Given that each partition is also
partitioned in your workload, I think the result of 1415ms is
reasonable.

-- 
Best regards,
Yuya Watari