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
CodePudding user response:
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 ...