Re: SQL/MED - file_fdw

On Mon, 7 Feb 2011 21:00:53 +0900
Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> On Mon, Feb 7, 2011 at 16:01, Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp> wrote:
> > This patch is based on latest FDW API patches which are posted in
> > another thread "SQL/MED FDW API", and copy_export-20110104.patch which
> > was posted by Itagaki-san.
>
> I have questions about estimate_costs().
>
> * What value does baserel->tuples have?
> Foreign tables are never analyzed for now. Is the number correct?

No, baserel->tuples is always 0, and baserel->pages is 0 too.
In addition, width and rows are set to 100 and 1, as default values.
I think ANALYZE support is needed to make PostgreSQL's standard
optimization for foreign scans. At present, estimation for foreign
tables would be awful.

> * Your previous measurement showed it has much more startup_cost.
> When you removed ReScan, it took long time but planner didn't choose
> materialized plans. It might come from lower startup costs.

I tested joining file_fdw tables, accounts and branches, which are
initialized with "pgbench -i -s 10" and exported to csv files.

At first, I tried adding random_page_cost (4.0) to startup_cost as
cost to open file (though it's groundless), but materialized was not
chosen. After updating pg_class.reltuples to correct value, Hash-join
was choosen for same query. With disabling Hash-join, finally
materialized was choosen.

ISTM that choosing simple nested loop would come from wrong
estimation of loop count, but not from startup cost. IMHO, supporting
analyze (PG-style statistics) is necessary to make PostgreSQL to
generate sane plan.

> * Why do you use lstat() in it?
> Even if the file is a symlink, we will read the linked file in the
> succeeding copy. So, I think it should be stat() rather than lstat().

Good catch! Fixed version is attached.

Regards,
--
Shigeru Hanada