Re: Disabling options lowers the estimated cost of a query - Mailing list pgsql-performance

From Tomas Vondra
Subject Re: Disabling options lowers the estimated cost of a query
Date
Msg-id 011937a3-7427-b99f-13f1-c07a127cf94c@enterprisedb.com
Whole thread Raw
In response to Re: Disabling options lowers the estimated cost of a query  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Hi,

On 4/16/21 3:09 PM, Tom Lane wrote:
> I wrote:
>> ... The code to select the
>> right child path would be approximately like get_cheapest_fractional_path,
>> except that you need to restrict it to paths with the right sort order.
> 
> Duh, I forgot about get_cheapest_fractional_path_for_pathkeys().
> 
>             regards, tom lane
> 

The attached patch does fix the issue for me, producing the same plans
with and without partition-wise joins.

It probably needs a bit more work, though:

1) If get_cheapest_fractional_path_for_pathkeys returns NULL, it's not
clear whether to use cheapest_startup or cheapest_total with Sort on
top. Or maybe consider an incremental sort?

2) Same for the cheapest_total - maybe there's a partially sorted path,
and using it with incremental sort on top would be better than using
cheapest_total_path + sort.

3) Not sure if get_cheapest_fractional_path_for_pathkeys should worry
about require_parallel_safe too.


Doesn't seem like an urgent issue (has been there for a while, not sure
we even want to backpatch it). I'll add this to the next CF.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Attachment

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Why is there a tenfold difference between Postgres's alleged query execution time and packet transmission time?
Next
From: Il Mimo di Creta
Date:
Subject: Most proper partitioning form on an integer column