PostgreSQL query does not use index

Question

Table definition is as follows:

CREATE TABLE public.the_table
(
  id integer NOT NULL DEFAULT nextval('the_table_id_seq'::regclass),
  report_timestamp timestamp without time zone NOT NULL,
  value_id integer NOT NULL,
  text_value character varying(255),
  numeric_value double precision,
  bool_value boolean,
  dt_value timestamp with time zone,
  exported boolean NOT NULL DEFAULT false,
  CONSTRAINT the_table_fkey_valdef FOREIGN KEY (value_id)
      REFERENCES public.value_defs (value_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.the_table
  OWNER TO postgres;

Indices:

CREATE INDEX the_table_idx_id ON public.the_table USING brin (id);
CREATE INDEX the_table_idx_timestamp ON public.the_table USING btree (report_timestamp);
CREATE INDEX the_table_idx_tsvid ON public.the_table USING brin (report_timestamp, value_id);
CREATE INDEX the_table_idx_valueid ON public.the_table USING btree (value_id);

The query is:

SELECT * FROM the_table r WHERE r.value_id = 1064 ORDER BY r.report_timestamp desc LIMIT 1;

While running the query PostgreSQL does not use the_table_idx_valueid index.

Why?

Actually the the_table_idx_valueid index is the one I would have expected as the most likely to be used. Keep in mind that you are doing a select *, and none of your four indices are covering this, so Postgres might choose to just scan the table. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 5, 2019 at 14:16
@Tim Biegeleisen: I thought my entire life, that indices cover WHERE, but not the fields to be outputted. — Paul
– Paul, Commented Sep 5, 2019 at 14:18
The thing is, if you only cover WHERE then when Postgres reaches the leaf node for that record, it then has to do a disk seek to the actual table to retrieve the values for select. Checkout the answer below as it might be better than what you have tried. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 5, 2019 at 14:20

Laurenz Albe · Accepted Answer · 2019-09-05 14:19:05Z

1

If anything, this index will help:

CREATE INDEX ON the_table (value_id, report_timestamp);

Depending on the selectivity of the condition and the number of rows in the table, PostgreSQL may correctly deduce that a sequential scan and a sort is faster than an index scan.

answered Sep 5, 2019 at 14:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Over a year ago

I understand! When I omit ORDER BY r.report_timestamp desc the index the_table_idx_valueid is used. Thank you.

Collectives™ on Stack Overflow

PostgreSQL query does not use index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related