0

I have a query that is stored in a function. It is called via a prepared statement jdbc app.

I have a fix for the query to force an index - but I cant get the "normalized form" required for it to match. Any suggestions ?

Query AS extracted from pg_stat_statements

"(
    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
)"

What is the normalized form that should work here - everything I have tried doesnt work. Obviously the $1/$2 etc are prepared statemnte parameters ..... but there are others also like ....

adminDateFormat adminTZ clientId

Unclear on why they are not passed as a prepared stmt.

Anyway - not sure what the "normalized" form of this should look like. I have left all the $1, $2 as is .... and changed the other parameters to ? ..... but the query plan doesnt match when I trace it - returns 0 rows from hint_plan.hints

Current version of hint attempt with normalized code

 
insert into hint_plan.hints values (1,'(
        select
            registration_date,
            to_char(registration_date, $17)::VARCHAR as x_axis_date,
            to_char(registration_date, upper(?))::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 ?) 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 ? and ? -- 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 ? and ?
                /*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 (?, ?)
                -- 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
    )', 'myapp','IndexScan(rwm)'
    );
2
  • Can you add the version of Postgres and version of pg_hint_plan?
    – xzilla
    Apr 3 at 15:42
  • Sure - Postgres 12 (GCP cloud). 1.3.8 for pg_plan_hint Apr 3 at 16:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.