How can I optimize a SELECT DISTINCT on a subselect?

Question

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                                                  
----------------+--------------------------+-------------------------------------------------------------+----------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------
id              | bigint                   | not null default "nextval"('"note_id_seq"'::"regclass")     | plain    |              | Primary key
jobid           | bigint                   | not null default 0                                          | plain    |
description     | "text"                   | default ''::"text"                                          | extended |
modified_date   | timestamp with time zone | default "statement_timestamp"()                             | plain    |              | Modified date
n_type          | "n_type"                 |                                                             | plain    |

Indexes:
"note_pkey" PRIMARY KEY, "btree" ("id")
"ix_note_gsdi_pk" "btree" (("id"::"text"))
"ix_job_fk" "btree" ("jobid")
"ix_job_n_type" "btree" ("n_type")
"ix_note_jobid_type" "btree" ("jobid", "n_type")
"ix_note_jobid_type_2" "btree" ("jobid", "n_type", "timezone"('Etc/UTC'::"text", "modified_date"))

View note on schema note_user - This view is in a different schema than the table note:

SELECT r_30.id, r_30.jobid, 
   r_30.description,  
   timezone('Etc/UTC'::text, r_30.modified_date),  
   cj.clientid
  FROM public.note r_30
  JOIN public.jobs cj ON cj.id = r_30.jobid
  ;

Table note:

CREATE TABLE public.note
(
  id bigint NOT NULL DEFAULT nextval('note_id_seq'::regclass), -- Primary key
  jobid bigint NOT NULL DEFAULT 0,
  description text DEFAULT ''::text,
n_type n_type,
  modified_date timestamp with time zone DEFAULT statement_timestamp(),
  CONSTRAINT note_pkey PRIMARY KEY (id)
  );

Table jobs:

CREATE TABLE public.jobs
(
  id bigint NOT NULL DEFAULT nextval('jobs_id_seq'::regclass),
  clientid bigint NOT NULL DEFAULT 0,
  description text NOT NULL DEFAULT ''::text,
  modified_date timestamp without time zone DEFAULT statement_timestamp(),
  CONSTRAINT jobs_pkey PRIMARY KEY (id)
);

Custom n_type data type:

live_database=> \dT+ n_type
                                                     List of data types
 Schema |      Name       |  Internal name  | Size |      Elements       | Access privileges |         Description          
--------+-----------------+-----------------+------+---------------------+-------------------+------------------------------
 public | n_type          | n_type          | 4    | n_1                +|                   |
        |                 |                 |      | n_custom           +|                   | 
        |                 |                 |      | n_standard         +|                   | 
        |                 |                 |      | n_status           +|                   | 
(1 row)

Answer 1

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;

Answer 2

You've got several options for indexes, and much depends on how selective each of your WHERE conditions is. You mentioned trying to add an index on n_type, but if this matches the majority (or a large minority) of records then it's not likely to improve things. If you have a large number of clients then you could try an index on clientid instead, or try a combination of (clientid, modified_date) - remember that the range term must come last to be useful here.

Answer 3

I would expect to need an index starting with clientid.

I suggest adding both of these

(clientid, modified_date, type, id)
(clientid, type, modified_date, id)

The Optimizer can pick between them.

In either case, much of the effort is avoided by first filtering on clientid=16040. After that, it is unclear which column is best to test next.

Both of the index examples are "covering", so the query can be done via a range scan in the index.

Also, if id in unique, Evan's answer can be further improved by removing DISTINCT.