Re: Partitioning and postgres_fdw optimisations for multi-tenancy

On 2020-07-14 15:27, Ashutosh Bapat wrote:
> On Tue, Jul 14, 2020 at 12:48 AM Alexey Kondratov
> <a(dot)kondratov(at)postgrespro(dot)ru> wrote:
>> I built a simple two node multi-tenant schema for tests, which can be
>> easily set up with attached scripts. It creates three tables
>> (companies,
>> users, documents) distributed over two nodes. Everything can be found
>> in
>> this Gist [2] as well.
>>
>> Some real-life test queries show, that all single-node queries aren't
>> pushed-down to the required node. For example:
>>
>> SELECT
>> *
>> FROM
>> documents
>> INNER JOIN users ON documents.user_id = users.id
>> WHERE
>> documents.company_id = 5
>> AND users.company_id = 5;
>
> There are a couple of things happening here
> 1. the clauses on company_id in WHERE clause are causing partition
> pruning. Partition-wise join is disabled with partition pruning before
> PG13. In PG13 we have added advanced partition matching algorithm
> which will allow partition-wise join with partition pruning.
>

I forgot to mention that I use a recent master (991c444e7a) for tests
with

enable_partitionwise_join = 'on'
enable_partitionwise_aggregate = 'on'

of course. I've also tried postgres_fdw.use_remote_estimate = true
followed by ANALYSE on both nodes (it is still used in setup.sh script).

BTW, can you, please, share a link to commit / thread about allowing
partition-wise join and partition pruning to work together in PG13?

>
> 2. the query has no equality condition on the partition key of the
> tables being joined. Partitionwise join is possible only when there's
> an equality condition on the partition keys (company_id) of the
> joining tables. PostgreSQL's optimizer is not smart enough to convert
> the equality conditions in WHERE clause into equality conditions on
> partition keys. So having those conditions just in WHERE clause does
> not help. Instead please add equality conditions on partition keys in
> JOIN .. ON clause or WHERE clause (only for INNER join).
>

With adding documents.company_id = users.company_id

SELECT *
FROM
documents
INNER JOIN users ON (documents.company_id = users.company_id
AND documents.user_id = users.id)
WHERE
documents.company_id = 5
AND users.company_id = 5;

query plan remains the same.

>>
>> executed as following
>>
>> QUERY PLAN
>> -------------------------------------------------------
>> Nested Loop
>> Join Filter: (documents.user_id = users.id)
>> -> Foreign Scan on users_node2 users
>> -> Materialize
>> -> Foreign Scan on documents_node2 documents
>>
>> i.e. it uses two foreign scans and does the final join locally.
>> However,
>> once I specify target partitions explicitly, then the entire query is
>> pushed down to the foreign node:
>>
>> QUERY PLAN
>> ---------------------------------------------------------
>> Foreign Scan
>> Relations: (documents_node2) INNER JOIN (users_node2)
>>
>> Execution time is dropped significantly as well — by more than 3 times
>> even for this small test database. Situation for simple queries with
>> aggregates or joins and aggregates followed by the sharding key filter
>> is the same. Something similar was briefly discussed in this thread
>> [3].
>>
>> IIUC, it means that push-down of queries through the postgres_fdw
>> works
>> perfectly well, the problem is with partition-wise operation detection
>> at the planning time. Currently, partition-wise aggregate routines,
>> e.g., looks for a GROUP BY and checks whether sharding key exists
>> there
>> or not. After that PARTITIONWISE_AGGREGATE_* flag is set. However, it
>> doesn't look for a content of WHERE clause, so frankly speaking it
>> isn't
>> a problem, this functionality is not yet implemented.
>>
>> Actually, sometimes I was able to push down queries with aggregate
>> simply by adding an additional GROUP BY with sharding key, like this:
>>
>> SELECT
>> count(*)
>> FROM
>> documents
>> WHERE
>> company_id = 5
>> GROUP BY company_id;
>
> This gets pushed down since GROUP BY clause is on the partition key.
>

Sure, but it only works *sometimes*, I've never seen most of such simple
queries with aggregates to be pushed down, e.g.:

SELECT
sum(id)
FROM
documents_node2
WHERE
company_id = 5
GROUP BY
company_id;

whether 'GROUP BY company_id' is used or not.

>>
>> Although it seems that it will be easier to start with aggregates,
>> probably we should initially plan a more general solution? For
>> example,
>> check that all involved tables are filtered by partitioning key and
>> push
>> down the entire query if all of them target the same foreign server.
>>
>> Any thoughts?
>
> I think adding just equality conditions on the partition key will be
> enough. No need for any code change.

So, it hasn't helped. Maybe I could modify some costs to verify that
push-down of such joins is ever possible?

Anyway, what about aggregates? Partition-wise aggregates work fine for
queries like

SELECT
count(*)
FROM
documents
GROUP BY
company_id;

but once I narrow it to a single partition with 'WHERE company_id = 5',
then it is being executed in a very inefficient way — takes all rows
from remote partition / node and performs aggregate locally. It doesn't
seem like a problem with query itself.

In my experience, both partition-wise joins and aggregates work well
with simple GROUP or JOIN by the partitioning key, which corresponds to
massive multi-partition OLAP queries. However, both stop working for a
single-partition queries with WHERE, when postgres_fdw and partitioning
are used. I'd be glad if you share any new guesses of how to make them
working without code modification.

Thanks
--
Alexey Kondratov

Postgres Professional https://www.postgrespro.com
Russian Postgres Company