From: | Jian Guo <gjian(at)vmware(dot)com> |
---|---|
To: | Euler Taveira <euler(at)timbira(dot)com(dot)br>, Zhenghua Lyu <zlyu(at)vmware(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: On disable_cost |
Date: | 2023-08-03 09:21:39 |
Message-ID: | BL0PR05MB51884B4328F8F92A7830D955C408A@BL0PR05MB5188.namprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi hackers,
I have write an initial patch to retire the disable_cost GUC, which labeled a flag on the Path struct instead of adding up a big cost which is hard to estimate. Though it involved in tons of plan changes in regression tests, I have tested on some simple test cases such as eagerly generate a two-stage agg plans and it worked. Could someone help to review?
regards,
Jian
________________________________
From: Euler Taveira <euler(at)timbira(dot)com(dot)br>
Sent: Friday, November 1, 2019 22:48
To: Zhenghua Lyu <zlyu(at)vmware(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: On disable_cost
!! External Email
Em sex, 1 de nov de 2019 às 03:42, Zhenghua Lyu <zlv(at)pivotal(dot)io> escreveu:
>
> My issue: I did some spikes and tests on TPCDS 1TB Bytes data. For query 104, it generates
> nestloop join even with enable_nestloop set off. And the final plan's total cost is very huge (about 1e24). But If I enlarge the disable_cost to 1e30, then, planner will generate hash join.
>
> So I guess that disable_cost is not large enough for huge amount of data.
>
> It is tricky to set disable_cost a huge number. Can we come up with better solution?
>
Isn't it a case for a GUC disable_cost? As Thomas suggested, DBL_MAX
upper limit should be sufficient.
> The following thoughts are from Heikki:
>>
>> Aside from not having a large enough disable cost, there's also the fact that the high cost might affect the rest of the plan, if we have to use a plan type that's disabled. For example, if a table doesn't have any indexes, but enable_seqscan is off, we might put the unavoidable Seq Scan on different side of a join than we we would with enable_seqscan=on, because of the high cost estimate.
>
>
>>
>> I think a more robust way to disable forbidden plan types would be to handle the disabling in add_path(). Instead of having a high disable cost on the Path itself, the comparison add_path() would always consider disabled paths as more expensive than others, regardless of the cost.
>
I'm afraid it is not as cheap as using diable_cost as a node cost. Are
you proposing to add a new boolean variable in Path struct to handle
those cases in compare_path_costs_fuzzily?
--
Euler Taveira Timbira -
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.timbira.com.br%2F&data=05%7C01%7Cgjian%40vmware.com%7C12a30b2852dd4651667608db9401d056%7Cb39138ca3cee4b4aa4d6cd83d9dd62f0%7C0%7C0%7C638266507757076648%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v54JhsW8FX4mSmjgt2yP59t7xtv1mZvC%2BBhtKrfp%2FBY%3D&reserved=0<http://www.timbira.com.br/>
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
!! External Email: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
Attachment | Content-Type | Size |
---|---|---|
0001-Retire-disable_cost-introduce-new-flag-is_disabled.patch | text/x-patch | 20.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Hayato Kuroda (Fujitsu) | 2023-08-03 09:28:33 | RE: [PoC] pg_upgrade: allow to upgrade publisher node |
Previous Message | Peter Smith | 2023-08-03 09:06:16 | Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication |