Re: FDW, too long to run explain

On Monday, 4 February 2019 09:14:14 EET Vijaykumar Jain wrote:
> Hi,

Hi,

> with pg v10.1

> we use writes directly to shards, and reads via FDW from all shards (RO)
> our DB size is ~ 500GB each shard, and tables are huge too.
> 1 table ~ 200GB, 1 ~55GB, 1 ~40GB and a lot of small tables, but large
> indices on large table.
>
> the sharding was done based on a key to enable shard isolation at app layer
> using a fact table.
> select id,shard from fact_table;
>
> server resources are,
> 32GB mem, 8 vcpu, 500GB SSD.
>
> the FDW connect to each other shard via FDW fronted by haproxy -> pgbouncer
> -> postgresql.
> Hope this is good enough background :)
>
> now we have some long running queries via FDW that take minutes and get
> killed explain runs as idle in transaction on remote servers. (we set
> use_remote_estimate = true )
> when the query is run on individual shards directly, it runs pretty
> quickly,
> but when run via FDW, it takes very long.
> i even altered fetch_sie to 10000, so that in case some filters do not get
> pushed, those can be applied on the FDW quickly.

In general, the plans via FDW are not the same as the ones running locally. We're having similar issues and the reason seems to be that queries via FDW are optimized for startup cost or few rows.

> Regards,
> Vijay

--
Regards,
Peter