snippetsqlMinor
How can I optimize a SELECT DISTINCT on a subselect?
Viewed 0 times
distinctcansubselectoptimizehowselect
Problem
I've got a very slow query, running over 30 seconds:
Explain Analyze: https://explain.depesz.com/s/DU4
Is there a way I can improve this query?
Created a new index on
New Explain analyze: https://explain.depesz.com/s/RsTr
Query still taking >5 seconds.
New explain analyze using the new query provided by Evan Carroll: https://explain.depesz.com/s/yP4S
Query:
Query is taking even longer now.
I can't do any other
Table "public.note"
Column | Type | Modifiers | Storage | Stats target | Description
----------------+--------------------------+-------------------------------------------------------------+----------+--------------+--
SELECT DISTINCT id10
FROM
(SELECT j.id AS id10,
j.modified,
j.n_type AS n_type5
FROM note j
WHERE j.modified_date >= '2016-10-01 23:12:34.000000'
AND j.clientid = 16049
AND j.n_type IN ('n',
'n_1',
'n_custom',
'n_standard',
'n_status')
ORDER BY j.id ASC) t2
ORDER BY id10 ASC LIMIT 20;Explain Analyze: https://explain.depesz.com/s/DU4
Is there a way I can improve this query?
Created a new index on
modified_date and n_type columns:CREATE INDEX ix_n_type_modified
ON notes (n_type, timezone('Etc/UTC'::text, modified_date))
WHERE n_type IN ('n_1','n_custom','n_standard','n_status');New Explain analyze: https://explain.depesz.com/s/RsTr
Query still taking >5 seconds.
New explain analyze using the new query provided by Evan Carroll: https://explain.depesz.com/s/yP4S
Query:
SELECT id AS id10
FROM
FROM note j
WHERE j.modified_date >= '2015-12-07 23:12:34.000000'
AND j.clientid = 16049
AND j.n_type IN ('n',
'n_1',
'n_custom',
'n_standard',
'n_status')
ORDER BY id10 ASC
LIMIT 20;Query is taking even longer now.
note is a view. It has a join on the jobs table (note.jobid -> jobs.id)I can't do any other
join between those tables, as there is no "linked" columns between them.\d+ note;:Table "public.note"
Column | Type | Modifiers | Storage | Stats target | Description
----------------+--------------------------+-------------------------------------------------------------+----------+--------------+--
Solution
Instead of what you've got, remove the virtual table and try to query directly.
SELECT DISTINCT id AS id10
FROM note j
WHERE j.modified_date >= '2016-10-01 23:12:34.000000'
AND j.clientid = 16049
AND j.n_type IN ('n',
'n_1',
'n_custom',
'n_standard',
'n_status')
ORDER BY id10 ASC
LIMIT 20;Code Snippets
SELECT DISTINCT id AS id10
FROM note j
WHERE j.modified_date >= '2016-10-01 23:12:34.000000'
AND j.clientid = 16049
AND j.n_type IN ('n',
'n_1',
'n_custom',
'n_standard',
'n_status')
ORDER BY id10 ASC
LIMIT 20;Context
StackExchange Database Administrators Q#161158, answer score: 4
Revisions (0)
No revisions yet.