Re: Postgres Specific issue

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