From: Antonin Houska <ah@cybertec.at>
To: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
cc: Tom Lane <tgl@sss.pgh.pa.us>,
    Andrew Gierth <andrew@tao11.riddles.org.uk>,
    pgsql-hackers@postgresql.org
Subject: Re: Problems with plan estimates in postgres_fdw
In-reply-to: <5C669DFD.30002@lab.ntt.co.jp>
References: <87pnz1aby9.fsf@news-spur.riddles.org.uk>
 <1175.1533220908@sss.pgh.pa.us> <5BB739CD.6040002@lab.ntt.co.jp>
 <5BBC4140.50403@lab.ntt.co.jp> <5C17A00E.5000005@lab.ntt.co.jp>
 <5C232F39.9060509@lab.ntt.co.jp> <5C25C7B5.2010802@lab.ntt.co.jp>
 <5C518631.5080401@lab.ntt.co.jp> <10994.1549559088@localhost>
 <5C5D778F.3080501@lab.ntt.co.jp> <19433.1549962210@localhost>
 <5C669DFD.30002@lab.ntt.co.jp>
Comments: In-reply-to Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
   message dated "Fri, 15 Feb 2019 20:09:49 +0900."
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Date: Fri, 15 Feb 2019 13:46:02 +0100
Message-ID: <16616.1550234762@localhost>
Precedence: bulk

Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:

> (2019/02/12 18:03), Antonin Houska wrote:
> > Etsuro Fujita<fujita.etsuro@lab.ntt.co.jp>  wrote:
> >> (2019/02/08 2:04), Antonin Houska wrote:
> >>>
> >>> And maybe related problem: why should FDW care about the effect of
> >>> apply_scanjoin_target_to_paths() like you claim to do here?
> >>>
> >>> 	/*
> >>> 	 * If this is UPPERREL_ORDERED and/or UPPERREL_FINAL steps on the
> >>> 	 * final scan/join relation, the costs obtained from the cache
> >>> 	 * wouldn't yet contain the eval cost for the final scan/join target,
> >>> 	 * which would have been updated by apply_scanjoin_target_to_paths();
> >>> 	 * add the eval cost now.
> >>> 	 */
> >>> 	if (fpextra&&   !IS_UPPER_REL(foreignrel))
> >>> 	{
> >>> 		/* The costs should have been obtained from the cache. */
> >>> 		Assert(fpinfo->rel_startup_cost>=3D 0&&
> >>> 			fpinfo->rel_total_cost>=3D 0);
> >>>
> >>> 		startup_cost +=3D foreignrel->reltarget->cost.startup;
> >>> 		run_cost +=3D foreignrel->reltarget->cost.per_tuple * rows;
> >>> 	}
> >>>
> >>> I think it should not, whether "foreignrel" means "input_rel" or "out=
put_rel"
> >>> from the perspective of postgresGetForeignUpperPaths().
> >>
> >> I added this before costing the sort operation below, because 1) the c=
ost of
> >> evaluating the scan/join target might not be zero (consider the case w=
here
> >> sort columns are not simple Vars, for example) and 2) the cost of sort=
ing
> >> takes into account the underlying path's startup/total costs.  Maybe I=
'm
> >> missing something, though.
> >
> > My understanding is that the "input_rel" argument of
> > postgresGetForeignUpperPaths() should already have been processed by
> > postgresGetForeignPaths() or postgresGetForeignJoinPaths() (or actually=
 by
> > postgresGetForeignUpperPaths() called with different "stage" too). Ther=
efore
> > it should already have the "fdw_private" field initialized. The estimat=
es in
> > the corresponding PgFdwRelationInfo should already include the reltarget
> > costs, as set previously by estimate_path_cost_size().
>=20
> Right, but what I'm saying here is: the costs of evaluating the final
> scan/join target updated by apply_scanjoin_target_to_paths() wouldn't be =
yet
> included in the costs we calculated using local statistics when called fr=
om
> postgresGetForeignPaths() or postgresGetForeignJoinPaths(). Let me explain
> using an example:
>=20
>     SELECT a+b FROM foreign_table LIMIT 10;
>=20
> For this query, the reltarget for the foreign_table would be {a, b} when
> called from postgresGetForeignPaths() (see build_base_rel_tlists()). The =
costs
> of evaluating simple Vars are zeroes, so we wouldn't charge any costs for
> tlist evaluation when estimating the costs of a basic foreign table scan =
in
> that callback routine, but the final scan target, with which the reltarget
> will be replaced later by apply_scanjoin_target_to_paths(), would be {a+b=
}, so
> we need to adjust the costs of the basic foreign table scan, cached in the
> PgFdwRelationInfo for the input_rel (ie, the foreign_table), so that eval
> costs for the replaced tlist are included when called from
> postgresGetForeignUpperPaths() with the UPPERREL_FINAL stage, as the cost=
s of
> evaluating the expression 'a+b' wouldn't be zeroes.

ok, I understand now. I assume that the patch

https://www.postgresql.org/message-id/5C66A056.60007%40lab.ntt.co.jp

obsoletes the code snippet we discussed above.

--=20
Antonin Houska
Cybertec Sch=C3=B6nig & Sch=C3=B6nig GmbH
Gr=C3=B6hrm=C3=BChlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com