Message-ID: <5C4AF3F3.4060409@lab.ntt.co.jp>
Date: Fri, 25 Jan 2019 20:33:07 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Subject: postgres_fdw: estimate_path_cost_size fails to re-use cached costs
Content-Type: multipart/mixed;
 boundary="------------050400070102040602000708"
To: Pg Hackers <pgsql-hackers@postgresql.org>
Precedence: bulk

This is a multi-part message in MIME format.
--------------050400070102040602000708
Content-Type: text/plain; charset=ISO-2022-JP
Content-Transfer-Encoding: 7bit

Hi,

I noticed yet another thing while updating the patch for pushing down
ORDER BY LIMIT.  Let me explain.  When costing foreign paths on the
basis of local statistics, we calculate/cache the costs of an unsorted
foreign path, and re-use them to estimate the costs of presorted foreign
paths, as shown below.  BUT: we fail to re-use them for some typical
queries, such as "select * from ft1 order by a", due to
fpinfo->rel_startup_cost=0, leading to doing the same cost calculation
repeatedly.

        /*
         * We will come here again and again with different set of pathkeys
         * that caller wants to cost. We don't need to calculate the cost of
         * bare scan each time. Instead, use the costs if we have cached
them
         * already.
         */
        if (fpinfo->rel_startup_cost > 0 && fpinfo->rel_total_cost > 0)
        {
            startup_cost = fpinfo->rel_startup_cost;
            run_cost = fpinfo->rel_total_cost - fpinfo->rel_startup_cost;
        }

I think we should use "fpinfo->rel_startup_cost >= 0" here, not
"fpinfo->rel_startup_cost > 0".  Also, it would be possible that the
total cost calculated is zero in corner cases (eg, seq_page_cost=0 and
cpu_tuple_cost=0 for the example), so I think we should change the total
cost part as well.  Attached is a patch for that.

Best regards,
Etsuro Fujita

--------------050400070102040602000708
Content-Type: text/x-patch;
 name="yet-another-fix-for-estimate_path_cost_size.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="yet-another-fix-for-estimate_path_cost_size.patch"

*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 2625,2631 **** estimate_path_cost_size(PlannerInfo *root,
  		 * bare scan each time. Instead, use the costs if we have cached them
  		 * already.
  		 */
! 		if (fpinfo->rel_startup_cost > 0 && fpinfo->rel_total_cost > 0)
  		{
  			startup_cost = fpinfo->rel_startup_cost;
  			run_cost = fpinfo->rel_total_cost - fpinfo->rel_startup_cost;
--- 2625,2631 ----
  		 * bare scan each time. Instead, use the costs if we have cached them
  		 * already.
  		 */
! 		if (fpinfo->rel_startup_cost >= 0 && fpinfo->rel_total_cost >= 0)
  		{
  			startup_cost = fpinfo->rel_startup_cost;
  			run_cost = fpinfo->rel_total_cost - fpinfo->rel_startup_cost;

--------------050400070102040602000708--