Unexpected plan width with foreign data wrapper - Mailing list pgsql-general

From Dmitry Bogatov
Subject Unexpected plan width with foreign data wrapper
Date
Msg-id 20191004143550.GA30189@debian
Whole thread Raw
List pgsql-general
Hello.

I work on fixing test suite (make installcheck) failures in Multicorn
project[1]. The purpose of project is to make it possible to write
Foreign Data Wrappers[2] in Python, not in C[3].

Test suite executes SQL statements with psql and compares output with
expected. If they are different, test is considered failed.

Major source of test failures is width estimation in query plan
explaination. For example, for following statements:

    CREATE EXTENSION multicorn;
    CREATE server multicorn_srv foreign data wrapper multicorn options (
        wrapper 'multicorn.testfdw.TestForeignDataWrapper'
    );
    CREATE user mapping for postgres server multicorn_srv options (usermapping 'test');

    CREATE foreign table testmulticorn (
        test1 date,
        test2 timestamp
    ) server multicorn_srv options (
        option1 'option1',
        test_type 'date'
    );

    -- Test sort pushdown asked
    EXPLAIN SELECT * FROM testmulticorn ORDER BY test1 DESC;

I have mismatch, described by this `diff expected actual`:

    - Foreign Scan on testmulticorn  (cost=10.00..400.00 rows=20 width=20)
    + Foreign Scan on testmulticorn  (cost=10.00..400.00 rows=20 width=12)

Width 20 is understandable: this is value assigned to
`baserel->reltarget->width` in GetForeignRelSize callback function.
Origin of value `12' is unknown to me. I have some observations on how
width estimate propagates through callback functions, though.

Behaviour differs on different versions of postgresql:

1. Postgresql-11

 * Value assigned to `baserel->reltarget->width` in GetForeignRelSize
   callback stays same in GetForeignPaths, but in GetForeignPlan it is
   changed. In this test, from 20 to 12.

 * If I assign it in GetForeignPlan function back to value 20 (value can be
   transfered via `baserel->fdw_private` field), expected value 20 will
   appear in output of `psql`. Test fixed, but with no understanding
   why.

2. Postgresql-9.6

 * Value assigned to `baserel->reltarget->width` in GetForeignRelSize
   stays the same in GetForeignPaths and GetForeignPlan, but `psql`
   still outputs another value. Test failed.

From what I can grep, psql output is generated in
`src/backend/commands/explain.c:1206`:

    appendStringInfo(es->str, "  (cost=%.2f..%.2f rows=%.0f width=%d)",
                     plan->startup_cost, plan->total_cost,
                     plan->plan_rows, plan->plan_width);

But I do not understand, where these values flow from.

Questions are:

 * Why do postgresql overrides width estimate, provided by foreign data
   wrapper?
 * How to fix tests in presence of such behaviour?

By the way, documentation in [2] is outdated: it refers to
`baserel->width`, which is replaced by `baserel->reltarget->width` in
commit 19a5411, and not present in 9.6.

 [1] https://github.com/Kozea/Multicorn
 [2] https://www.postgresql.org/docs/11/fdw-callbacks.html
 [3] https://multicorn.org



pgsql-general by date:

Previous
From: Christoph Moench-Tegeder
Date:
Subject: Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to theserver
Next
From: "Daniel Verite"
Date:
Subject: Re: Postgres 12: backend crashes when creating non-deterministic collation