CODEWITHSUNDEEP
postgresqlindexing
Paul
Paul

Reputation: 26640

PostgreSQL query does not use index

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?

Upvotes: 0

Views: 48

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246063

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.

Upvotes: 1

Related Questions