HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How can I optimize a SELECT DISTINCT on a subselect?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
distinctcansubselectoptimizehowselect

Problem

I've got a very slow query, running over 30 seconds:

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.