3

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)
  • 1
    Discussion on this question has been moved to chat. – Paul White Jan 18 '17 at 5:32
  • Postgresql has problems using indexes on timestamps. The only solution I found was replacing the timestamp by an integer containing unix_time. – Gerard H. Pille Oct 19 '18 at 19:11
3

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;
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.