> On 26 Jul 2024, at 10:10, Michał Kłeczek <mic...@kleczek.org> wrote: > > > >> On 26 Jul 2024, at 01:28, Matthias van de Meent >> <boekewurm+postg...@gmail.com> wrote: >> >> All in all, this still seems like a very (very) specific optimization, >> of which I'm not sure that it is generalizable. However, array >> introspection and filtering for SAOP equality checks feel like a >> relatively easy (?) push-down optimization in (e.g.) runtime partition >> pruning (or planning); isn't that even better patch potential here? > > The issue is not partition pruning for SAOP - it works fine. The issue is > lack of SAOP support in GIST. > > Because I cannot use SAOP I have two options: > > 1) LATERAL JOIN (ie. iterate through input array elements, SELECT rows for > each, merge results) > 2) Implement a custom operator that emulates SAOP and provide consistent > function for it. Additionally provide SAOP clause (redundantly) to enable > partition pruning. > > In case of 1): > - the query becomes convoluted with multiple redundant ORDER BY and LIMIT > clauses > - unnecessary sort is performed (because we have to merge results of > subqueries) > - some partitions are scanned multiple times (per each element in input array > that happens to land in the same partition) > > In case of 2): > - the whole input array is passed to consistent function for each partition > so we unnecessarily search for non-existent rows
Which is especially painful in case of sharding implementation based on partitioning and postgres_fdw as it requires multiple SELECTS from remote partition. > > To fix the issue in 2) I need to somehow filter input array per partition - > hence this patch. > Regards, — Michal
