Re: Postgres Specific issue

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