Thread: PG does not support one function of its extension pg_hint_plan
HI, all hackers:
In the GitHub repository for PostgreSQL’s pg_hint_plan extension, there is an issue where the generated join order does not match the assigned join order. After reviewing the source code, I found that this inconsistency with input hints is due to PostgreSQL’s implementation and is not a bug in pg_hint_plan.
PostgreSQL with pg_hint_plan supports disabling certain operators (e.g., hash join, seq scan) by setting pg parameters like “set enable_hashjoin = false”. This setting causes PostgreSQL to add a high disable_cost (e.g., 1e10) to the estimated cost of the hash join operator, effectively preventing the planner from selecting hash joins due to the inflated cost. Additionally, pg_hint_plan supports enforcing specific join orders. To do this, pg_hint_plan disables all join algorithms when it encounters inconsistent join orders, by adding the disable_cost to each join operator. As a result, only the assigned join order will be selected. This is the mechanism behind pg_hint_plan.
Then, we take an example of the GitHub issue to demonstrate this problem:
Here is a query with pg_hint:
/*+
Leading((rt (it ((n (chn (mc (mi (t (ci an)))))) cn))))
HashJoin(ci an)
NestLoop(ci an t)
NestLoop(ci an t mi)
NestLoop(ci an t mi mc)
NestLoop(ci an t mi mc chn)
NestLoop(ci an t mi mc chn n)
NestLoop(ci an t mi mc chn n cn)
NestLoop(ci an t mi mc chn n cn it)
NestLoop(ci an t mi mc chn n cn it rt)
*/
EXPLAIN (FORMAT TEXT)
SELECT MIN(n.name) AS voicing_actress,
MIN(t.title) AS voiced_movie
FROM aka_name AS an,
char_name AS chn,
cast_info AS ci,
company_name AS cn,
info_type AS it,
movie_companies AS mc,
movie_info AS mi,
name AS n,
role_type AS rt,
title AS t
WHERE ci.note IN ('(voice)',
'(voice: Japanese version)',
'(voice) (uncredited)',
'(voice: English version)')
AND cn.country_code ='[us]'
AND it.info = 'release dates'
AND mc.note IS NOT NULL
AND (mc.note LIKE '%(USA)%'
OR mc.note LIKE '%(worldwide)%')
AND mi.info IS NOT NULL
AND (mi.info LIKE 'Japan:%200%'
OR mi.info LIKE 'USA:%200%')
AND n.gender ='f'
AND n.name LIKE '%Ang%'
AND rt.role ='actress'
AND t.production_year BETWEEN 2005 AND 2009
AND t.id = mi.movie_id
AND t.id = mc.movie_id
AND t.id = ci.movie_id
AND mc.movie_id = ci.movie_id
AND mc.movie_id = mi.movie_id
AND mi.movie_id = ci.movie_id
AND cn.id = mc.company_id
AND it.id = mi.info_type_id
AND n.id = ci.person_id
AND rt.id = ci.role_id
AND n.id = an.person_id
AND ci.person_id = an.person_id
AND chn.id = ci.person_role_id;
The hint specifies a join order (rt (it ((n (chn (mc (mi (t (ci an)))))) cn))), but the generated join order is (rt (it ((n ((mc (mi ((ci an) t))) chn)) cn))). Here, PostgreSQL generates sub-join order ((ci an) t) instead of the assigned sub-join order (t (ci an)), and ((mc (mi ((ci an) t))) chn) instead of (chn (mc (mi ((ci an) t)))). This discrepancy arises because PostgreSQL estimates operator costs in two phases. In the first phase, it filters out paths that are obviously suboptimal based on estimated costs; however, it does not factor in disable_cost for disabled operators in this phase, only doing so in the second phase. As a result, while (t (ci an)) would use a regular nested loop join with a sequential scan on t, ((ci an) t) uses an index-based nested loop join with an index scan on t, which is significantly faster. Consequently, (t (ci an)) is filtered out after the first phase of cost estimation. The same reasoning applies to (chn (mc (mi ((ci an) t)))).
In the following example, by forcing PostgreSQL to access relations t and chn with a sequential scan, PostgreSQL generates the assigned join order. This is because forcing a sequential scan for t and chn prevents PostgreSQL from considering index-based nested loop joins for them.
/*+
SeqScan(t) SeqScan(chn)
Leading((rt (it ((n (chn (mc (mi (t (ci an)))))) cn))))
HashJoin(ci an)
NestLoop(ci an t)
NestLoop(ci an t mi)
NestLoop(ci an t mi mc)
NestLoop(ci an t mi mc chn)
NestLoop(ci an t mi mc chn n)
NestLoop(ci an t mi mc chn n cn)
NestLoop(ci an t mi mc chn n cn it)
NestLoop(ci an t mi mc chn n cn it rt)
*/
EXPLAIN (FORMAT TEXT)
SELECT MIN(n.name) AS voicing_actress,
MIN(t.title) AS voiced_movie
FROM aka_name AS an,
char_name AS chn,
cast_info AS ci,
company_name AS cn,
info_type AS it,
movie_companies AS mc,
movie_info AS mi,
name AS n,
role_type AS rt,
title AS t
WHERE ci.note IN ('(voice)',
'(voice: Japanese version)',
'(voice) (uncredited)',
'(voice: English version)')
AND cn.country_code ='[us]'
AND it.info = 'release dates'
AND mc.note IS NOT NULL
AND (mc.note LIKE '%(USA)%'
OR mc.note LIKE '%(worldwide)%')
AND mi.info IS NOT NULL
AND (mi.info LIKE 'Japan:%200%'
OR mi.info LIKE 'USA:%200%')
AND n.gender ='f'
AND n.name LIKE '%Ang%'
AND rt.role ='actress'
AND t.production_year BETWEEN 2005 AND 2009
AND t.id = mi.movie_id
AND t.id = mc.movie_id
AND t.id = ci.movie_id
AND mc.movie_id = ci.movie_id
AND mc.movie_id = mi.movie_id
AND mi.movie_id = ci.movie_id
AND cn.id = mc.company_id
AND it.id = mi.info_type_id
AND n.id = ci.person_id
AND rt.id = ci.role_id
AND n.id = an.person_id
AND ci.person_id = an.person_id
AND chn.id = ci.person_role_id;
As I explained, the issue arises because PG does not account for the disable_cost of disabled operators in the initial phase of cost estimation. To address this, I modified the process to include disable_cost for disabled operators in the first phase. As a result, the initial query generated the desired join order.
I want to submit a patch to PG to solve this issue. How do you like this solution?
Kindly regards,
Qilong.
On Fri, Nov 1, 2024 at 11:13:09AM +0800, 李奇隆 wrote: > HI, all hackers: > > > In the GitHub repository for PostgreSQL’s pg_hint_plan extension, there is an > issue where the generated join order does not match the assigned join order. > After reviewing the source code, I found that this inconsistency with input > hints is due to PostgreSQL’s implementation and is not a bug in pg_hint_plan. Just to clarify, the bug is not in pg_hint_plan but in the fact that the Postgres server ignores "disable_cost of disabled operators in the initial phase of cost estimation," right? -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com When a patient asks the doctor, "Am I going to die?", he means "Am I going to die soon?"
Bruce Momjian <bruce@momjian.us> writes: > On Fri, Nov 1, 2024 at 11:13:09AM +0800, 李奇隆 wrote: >> In the GitHub repository for PostgreSQL’s pg_hint_plan extension, there is an >> issue where the generated join order does not match the assigned join order. >> After reviewing the source code, I found that this inconsistency with input >> hints is due to PostgreSQL’s implementation and is not a bug in pg_hint_plan. > Just to clarify, the bug is not in pg_hint_plan but in the fact that the > Postgres server ignores "disable_cost of disabled operators in the > initial phase of cost estimation," right? We have never promised anything about supporting pg_hint_plan. Having said that, this analysis is all obsolete in the wake of commit e22253467. Somebody (not me) would need to look into whether a similar effect still exists with the new model for disabling plan types. Also, there's a highly relevant thread over at https://www.postgresql.org/message-id/flat/CA%2BTgmoZY%2BbaV-T-5ifDn6P%3DL%3DaV-VkVBrPmi0TQkcEq-5Finww%40mail.gmail.com It would probably be better to bring any conclusions to that thread instead of starting a new one. regards, tom lane