Re: [PATCH] Add a guc parameter to control limit clause adjust path cost.
| От | ocean_li_996 |
|---|---|
| Тема | Re: [PATCH] Add a guc parameter to control limit clause adjust path cost. |
| Дата | |
| Msg-id | 4e5931dd.594.19a491f3c44.Coremail.ocean_li_996@163.com обсуждение исходный текст |
| Ответ на | Re: [PATCH] Add a guc parameter to control limit clause adjust path cost. (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-hackers |
There were some formatting display issues with my previous email reply,
so I’m using another email account to send this message.
"Tom Lane" <tgl@sss.pgh.pa.us> Sun, 02 Nov 2025 12:44:13 -0500 write:
> I think this is a pretty bad solution as given. A global GUC switch
> is an extremely blunt instrument and hard to use in production without
> breaking cases you didn't want to break.
Yeah. Disabling this globally in a production environment is not advisable.
A more acceptable approach would be to use the pg_hint_plan extension
and apply hints only to the specific SQL statements that exhibit issues
for example:
```
/*+set(enable_limit_adjust_cost off) */ SELECT xxx;
```
> The proposed patch seems to
> have that problem in spades, as it looks like you've turned off
> *every* place that has any consideration of LIMIT effects without
> concern for whether that place is known to have problems, and
> furthermore done so at the greatest possible distance from where the
> estimates actually get made.
> We have discussed fixes with a bit more finesse, such as adjusting
> LIMIT cost estimates with the understanding that the tuples being
> sought may not be uniformly distributed in the input data (which is
> usually the ultimate cause of such plans performing badly). Nobody's
> carried such ideas through to a complete proposal as yet, though.
> have that problem in spades, as it looks like you've turned off
> *every* place that has any consideration of LIMIT effects without
> concern for whether that place is known to have problems, and
> furthermore done so at the greatest possible distance from where the
> estimates actually get made.
The patch works by making the cost estimator behave as if there is no LIMIT
clause inside the subquery, except for the rows estimation performed at the
limit node itself.
> We have discussed fixes with a bit more finesse, such as adjusting
> LIMIT cost estimates with the understanding that the tuples being
> sought may not be uniformly distributed in the input data (which is
> usually the ultimate cause of such plans performing badly). Nobody's
> carried such ideas through to a complete proposal as yet, though.
I have also considered more finesse solutions, but haven't yet found one that
is appropriate. It seems that this issue has been discussed for quite some time.
Thus, I proposed adding a dedicated GUC. This solution may not be elegant, but
it is straightforward and working.
regards,
Haiyang Li
В списке pgsql-hackers по дате отправления: