Re: Getting sorted data from foreign server - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: Getting sorted data from foreign server
Date
Msg-id CAFjFpRceEXaM9p9byHxJ9893Mb=9bJ6tsARnKPX+ApzmFt0T9Q@mail.gmail.com
Whole thread Raw
In response to Re: Getting sorted data from foreign server  (Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>)
Responses Re: Getting sorted data from foreign server  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
If there is a collate clause in the ORDER BY, the server crashes with assertion
+    Assert(loc_cxt.state == FDW_COLLATE_NONE ||
+            loc_cxt.state == FDW_COLLATE_SAFE);


The assertion is fine as long as is_foreign_expr() tests only boolean expressions (appearing in quals). This patch uses the function to test an expression appearing in ORDER BY clause, which need not be boolean. Attached patch removed the assertion and instead makes the function return false, when the walker deems collation of the expression unsafe. The walker can not return false when it encounter unsafe expression since the subtree it's examining might be part of an expression which does not use the collation ultimately.

On Wed, Oct 28, 2015 at 11:51 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
On Tue, Oct 27, 2015 at 6:44 PM, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:


On Tue, Oct 27, 2015 at 5:26 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
>
>
>
> On Fri, Oct 23, 2015 at 2:43 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>
>> On Wed, Oct 21, 2015 at 5:23 AM, Ashutosh Bapat
>> <ashutosh.bapat@enterprisedb.com> wrote:
>> > Increasing the sorting cost factor (when use_remote_estimates = false) from
>> > 1.1 to 1.2 makes the difference disappear.
>> >
>> > Since the startup costs for postgres_fdw are large portion of total cost,
>> > extra 10% of rest of the cost is comparable to 1% fuzzy limit. IMO, we
>> > shouldn't bother too much about it as the path costs are not much different.
>>
>> My feeling is that cranking the sorting cost factor up to 20-25% would
>> be a good idea, just so we have less unnecessary plan churn.  I dunno
>> if sorting always costs that much, but if a 10% cost overhead is
>> really 1% because it only applies to a fraction of the cost, I don't
>> think that's good.  The whole point was to pick something large enough
>> that we wouldn't take the sorted path unless we will benefit from the
>> sort, and clearly that's not what happened here.
>>
>
> PFA patch with the default multiplication factor for sort bumped up to 1.2.
>  

+/* If no remote estimates, assume a sort costs 10% extra */
+#define DEFAULT_FDW_SORT_MULTIPLIER 1.2

The above comment should not be 20%?

Ah! Here's patch with comment fixed.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company



--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
Attachment

pgsql-hackers by date:

Previous
From: Amit Langote
Date:
Subject: Re: Declarative partitioning
Next
From: Robert Haas
Date:
Subject: ParallelContexts can get confused about which worker is which