How to use ANALYZE EXPLAIN in a with_something query

I’m trying to find out how to use ANALYZE EXPLAIN on my PSQL query. If I add ANALYZE EXPLAIN SELECT, I get a syntax error. I have no clue how to use it.

I added the ANALYZE before the first and second SELECT, but both throw a syntax error.

I was unable to find different examples but on documentation, I should be able to do it on select or not.

The query

WITH statuses_flow AS (
    SELECT
        c.id,
        c.study_id,
        c.site_id,
        s.type AS status_type,
        s.timestamp AS status_from,
        sa.type,
        row_number() OVER (ORDER BY s.candidate_id,
            s.timestamp) AS row_no
    FROM
        public.candidates c
        JOIN public.statuses s ON s.candidate_id = c.id
        JOIN public.statuses sa ON sa.candidate_id = c.id
            AND sa.id in(
            SELECT
                max(id)
            FROM public.statuses
        GROUP BY
            candidate_id)
    WHERE
        c.study_id in('RECOV')
        AND c.site_id in('PLACEHOLDER')
        --and c.id in ('TBX1-001600')
        AND sa.type != 'ANONYMISED' ORDER BY
            row_no ASC
)
SELECT
    statuses_flow.id, statuses_flow.study_id AS "studyId", statuses_flow.site_id AS "siteId", statuses_flow.status_type AS "statusType", statuses_flow.status_from AS "statusFrom", next_status.status_from AS "statusTo", CASE WHEN next_status.status_from IS NULL THEN
        NULL
    ELSE
        (
        SELECT
            created_at AS first_contact
        FROM
            public.activities
        WHERE
            candidate_id = statuses_flow.id
            AND TYPE in('PHONE', 'SMS', 'EMAIL')
            AND created_at BETWEEN statuses_flow.status_from
            AND next_status.status_from ORDER BY
                created_at FETCH FIRST 1 ROWS ONLY)
    END AS "first_contact"
FROM
    statuses_flow
    LEFT JOIN statuses_flow next_status ON statuses_flow.id = next_status.id
        AND statuses_flow.row_no + 1 = next_status.row_no
WHERE
    statuses_flow.status_type in('PENDING_SITE', 'PENDING_CALLCENTER', 'INCOMPLETE', 'REJECTED_CALLCENTER', 'REJECTED_SITE', 'CONSENTED')
ORDER BY
    statuses_flow.id,
    statuses_flow.status_from

Answer

It’s explain (analyze) (or explain analyze) not “analyze explain”.

And this needs to go before the entire query, so before the WITH clause:

explain (analyze)
WITH statuses_flow AS (
 ....
)
SELECT ...

Advertisement

Answer