Re: Wrong cost estimation for foreign tables join with use_remote_estimate disabled

On 21.06.2018 20:08, Tom Lane wrote:
> Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> writes:
>> The following very simple test reduce the problem with wrong cost
>> estimation:
>> create foreign table t1_fdw(x integer, y integer) server pg_fdw options
>> (table_name 't1', use_remote_estimate 'false');
>> create foreign table t2_fdw(x integer) server pg_fdw options (table_name
>> 't2', use_remote_estimate 'false');
>> It is possible to force Postgres to use correct plan by setting
>> "fdw_startup_cost" to some very large value (100000000 for example).
>> ...
>> Also correct plan is used when use_remote_estimate is true.
> If you are unhappy about the results with use_remote_estimate off, don't
> run it that way. The optimizer does not have a crystal ball.

As I wrote, use_remote_estimate can not be used because in this case
query compilation time is unacceptable (10 seconds, while time of query
execution itself is ~200msec).
So the problem can be addressed in two ways:

1. Try to reduce time of remote estimation. I wonder why postgres_fdw
sends so much queries to remote server. For join of two tables there are
7 queries.
I suspect that for ~20 joined tables in the original query number of
calls is more than hundred,  so on wonder that it takes so much time.
2. Try to make optimizer make better estimation of join cost based on
local statistic (please notice that ANALYZE is explicitly called for all
foreign tables and number of rows in the result was correctly calculated).

What do you think: which of this two direction is more perspective? Or
it is better to address both of them?

By the way, below is list of remote EXPLAIN statements performed by
postgres_fdw for the mentioned above query when use_remote_estimate is on:

Breakpoint 1, get_remote_estimate (
    sql=0x1940008 "EXPLAIN SELECT x, y FROM public.t1 WHERE ((y = ANY
('{1234567,1234577,1234667,1235567,1244567,1334567}'::integer[])))",
conn=0x190e0d0,
    rows=0x7ffdd9e93388, width=0x7ffdd9e9337c,
startup_cost=0x7ffdd9e93390, total_cost=0x7ffdd9e93398) at
postgres_fdw.c:2984
2984    {
(gdb) cont
Continuing.

Breakpoint 1, get_remote_estimate (sql=0x196fa68 "EXPLAIN SELECT x FROM
public.t2", conn=0x190e0d0, rows=0x7ffdd9e93388, width=0x7ffdd9e9337c,
    startup_cost=0x7ffdd9e93390, total_cost=0x7ffdd9e93398) at
postgres_fdw.c:2984
2984    {
(gdb)
Continuing.

Breakpoint 1, get_remote_estimate (
    sql=0x19208f8 "EXPLAIN SELECT x, y FROM public.t1 WHERE ((y = ANY
('{1234567,1234577,1234667,1235567,1244567,1334567}'::integer[]))) ORDER
BY x ASC NULLS LAST", conn=0x190e0d0, rows=0x7ffdd9e932c8,
width=0x7ffdd9e932bc, startup_cost=0x7ffdd9e932d0,
total_cost=0x7ffdd9e932d8) at postgres_fdw.c:2984
2984    {
(gdb)
Continuing.

Breakpoint 1, get_remote_estimate (
    sql=0x19227b0 "EXPLAIN SELECT x, y FROM public.t1 WHERE ((((SELECT
null::integer)::integer) = x)) AND ((y = ANY
('{1234567,1234577,1234667,1235567,1244567,1334567}'::integer[])))",
conn=0x190e0d0, rows=0x7ffdd9e93348, width=0x7ffdd9e9333c,
startup_cost=0x7ffdd9e93350, total_cost=0x7ffdd9e93358)
    at postgres_fdw.c:2984
2984    {
(gdb)
Continuing.

Breakpoint 1, get_remote_estimate (sql=0x19236c0 "EXPLAIN SELECT x FROM
public.t2 ORDER BY x ASC NULLS LAST", conn=0x190e0d0, rows=0x7ffdd9e932c8,
    width=0x7ffdd9e932bc, startup_cost=0x7ffdd9e932d0,
total_cost=0x7ffdd9e932d8) at postgres_fdw.c:2984
2984    {
(gdb)
Continuing.

Breakpoint 1, get_remote_estimate (sql=0x19247c0 "EXPLAIN SELECT x FROM
public.t2 WHERE ((((SELECT null::integer)::integer) = x))", conn=0x190e0d0,
    rows=0x7ffdd9e93348, width=0x7ffdd9e9333c,
startup_cost=0x7ffdd9e93350, total_cost=0x7ffdd9e93358) at
postgres_fdw.c:2984
2984    {
(gdb)
Continuing.

Breakpoint 1, get_remote_estimate (
    sql=0x19267d0 "EXPLAIN SELECT r1.x, r1.y, r2.x FROM (public.t1 r1
INNER JOIN public.t2 r2 ON (((r1.x = r2.x)) AND ((r1.y = ANY
('{1234567,1234577,1234667,1235567,1244567,1334567}'::integer[])))))",
conn=0x190e0d0, rows=0x7ffdd9e93108, width=0x7ffdd9e930fc,
startup_cost=0x7ffdd9e93110, total_cost=0x7ffdd9e93118)
    at postgres_fdw.c:2984

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company