Re: Postgres Specific issue

Hi
Yes we engaged a consultancy - but they are not too sure either.
Does anyone know if the recursive $1 parameters from the postgres engine
query against hint_plan table can be traced/somehow ? to see values.

Already have all of these set

SET client_min_messages TO log;
LOAD 'auto_explain';
SET pg_hint_plan.enable_hint_table = on;
SET auto_explain.log_nested_statements = ON; -- statements inside functions
SET auto_explain.log_min_duration = 10;
SET auto_explain.log_analyze =True ;
SET auto_explain.log_buffers = True;
SET auto_explain.log_timing = True;
set pg_hint_plan.debug_print='on';
set pg_hint_plan.debug_print=verbose;
set client_min_messages = log;
set pg_hint_plan.message_level='debug';

On Thu, Apr 25, 2024 at 9:47 PM Jeremy Schneider <schneider(at)ardentperf(dot)com>
wrote:

> Hi John,
>
> My first guess would be around hidden whitespace or something like that,
> and maybe the hint table needs an exact match or something? I haven't done
> much with hint tables myself, and that's a pretty long SQL text, so if
> exact match is needed then it seems like lots of places where there could
> be mixups with CRLF or tabs or who knows what else. Unfortunately I didn't
> have a chance in the past few weeks to try to help (kids on spring break
> and other stuff; it's been very busy).
>
> If you're still trying to figure this out, a few ideas:
> 1) worth at least reading the issues and see if anything helps with your
> question https://github.com/ossc-db/pg_hint_plan/issues
> 2) the PG community slack & discords are fairly active and helpful
> 3) note that pg_hint_plan is not from postgresql.org so I'm not sure that
> pgsql-users list is the right place, but maybe someone would be generous
> and help? this is a pretty common extension...
>
> i'm curious to hear the outcome, if you have figured it out in the meantime
>
> -Jeremy
>
>
>
> On Wed, Apr 3, 2024 at 3:24 PM John Fak <johnfak75(at)gmail(dot)com> wrote:
>
>> No ..... sorry ... live in sequim.
>>
>> I *think* if I can get the value of the recursive call $1 parameter - I
>> can reverse engineer it. I set log_statement_duration=0 - but still doesnt
>> capture in logs the value for $1. Captures statement but not val.
>> Any ideas on what to set to get that.
>>
>> [image: image.png]
>>
>>
>>
>>
>> On Wed, Apr 3, 2024 at 3:18 PM Jeremy Schneider <schneider(at)ardentperf(dot)com>
>> wrote:
>>
>>> Are you going to be at the meetup tomorrow night? I don't think I'll
>>> have a quick answer by tomorrow but maybe we can make some progress on it.
>>>
>>> -Jeremy
>>>
>>>
>>> On Tue, Apr 2, 2024 at 9:58 AM John Fak <johnfak75(at)gmail(dot)com> wrote:
>>>
>>>> Hi all.
>>>> Oracle DBA here - learning postgres.
>>>> I have a specific postgres issue with pg plan hints not working - and
>>>> unsure why (well its because the normalzied form isnt correct but not sure
>>>> what correct forms should be exactly).
>>>>
>>>> Is there someone that would be interested in discussing this with me ?
>>>>
>>>> This is what the raw SQL looks like from pg_stats_staments - I've
>>>> tried 4-5 different versions of normalized text into the pg_plan_hint table
>>>> ... with tracing I can see the parse time is trying to find it in table but
>>>> 0 rows.
>>>>
>>>>
>>>> "(
>>>> select
>>>> registration_date,
>>>> to_char(registration_date, $17)::VARCHAR as x_axis_date,
>>>> to_char(registration_date, upper(adminDateFormat))::VARCHAR as
>>>> formatted_registration_date,
>>>> count(distinct lead_email) as registered_leads
>>>> from (
>>>> select
>>>> DATE_TRUNC($18,min(eu.create_timestamp) AT TIME ZONE $19 AT TIME
>>>> ZONE adminTZ) as registration_date,
>>>> -- DATE_TRUNC('DAY', TIMEZONE(adminTZ, TIMEZONE('PST',
>>>> min(eu.create_timestamp)))) as registration_date,
>>>> dl.email as lead_email
>>>> from
>>>> client_hierarchy ch
>>>> join dw_lead dl on dl.client_id = ch.sub_client_id
>>>> join dw_lead_user dlu on dlu.lead_id = dl.lead_id
>>>> join event e on dlu.event_id = e.event_id
>>>> join event_user eu on eu.event_user_id = dlu.event_user_id and
>>>> eu.event_id = dlu.event_id
>>>> where
>>>> ch.client_id = clientId::BIGINT
>>>> and dl.lead_update_timestamp between startDate and endDate -- this will
>>>> discard leads out of the selected time range
>>>> -- and dl.email not like '%deleted' -- this should not be needed,
>>>> deleted users are removed
>>>> and eu.create_timestamp between startDate and endDate
>>>> /*and e.event_profile_id <> experienceProfileId*/
>>>> and e.is_active = $20
>>>> and eu.is_deleted = $21
>>>> -- Exclude Golive Data
>>>> and e.event_profile_id not in (lighthouseProfileId,
>>>> lighthouseSessionProfileId)
>>>> -- Excluding Top-Level Overall Recurring Webcasts
>>>> AND NOT EXISTS (
>>>> SELECT $22
>>>> FROM recurring_webcast_metadata rwm
>>>> WHERE rwm.event_id = e.event_id
>>>> )
>>>> group by
>>>> dl.email
>>>> ) as leads_trend
>>>> group by
>>>> registration_date
>>>> order by
>>>> registration_date asc
>>>> )"
>>>>
>>>>
>>>> Values above like adminTZ and
>>>> lighthouseProfileId/lighthouseSessionProfileId are also runtime parameters
>>>> passed to the function.
>>>>
>>>>
>>>>
>>>>
>>>
>>> --
>>> http://about.me/jeremy_schneider
>>>
>>
>
> --
> http://about.me/jeremy_schneider
>