CODEWITHSUNDEEP
indexingpostgresql-9.5
Marcelo Flores
Marcelo Flores

Reputation: 112

Why my timestamp index is not being used by a query that returns 200 of records of 300 million

I have a table.

It is a simple table, called events with some timestamp fields.

CREATE TABLE public.event (
id int8 NOT NULL DEFAULT nextval('event_id_seq'::regclass),
email_id bpchar(36) NOT NULL,
sending timestamp NULL,
CONSTRAINT event_pkey PRIMARY KEY (id)
)
WITH (
   OIDS=FALSE
);

CREATE INDEX email_idx ON public.event (email_id);

CREATE INDEX sending_idx ON public.event ((sending::date));

One of these timestamp fields is called sending, that has a date index.

The problem is that postgres is using seqscan to retrieve the results for the following query:

select email_id from event where sending between  '2018-01-07 00:33:00'  and '2018-01-07 00:33:20'

I performed a explain analyze with the following results: 

Seq Scan on event  (cost=0.00..11391171.08 rows=139 width=37) (actual time=265885.080..503636.060 rows=257 loops=1)
Filter: ((sending >= '2018-01-07 00:33:00'::timestamp without time zone) AND (sending <= '2018-01-07 00:33:20'::timestamp without time zone))
 Rows Removed by Filter: 317633116
Planning time: 0.066 ms
Execution time: 503650.634 ms

why is postgres performing a seqscan to retrieve a couple hundred records from several millions over an indexed field?

Thanks!

Upvotes: 0

Views: 51

Answers (1)

user330315
user330315

Reputation:

You didn't index a timestamp you indexed a date. In your where clause you also need to compare the column to a date to make the index usable.

Either compare it to a date:

where sending between date '2018-01-07'  and date '2018-01-07'

or create the index on the timestamp value: 

CREATE INDEX sending_idx ON public.event (sending);

then your original query should use the index.

Don't forget to analyze your table to bring your statistics up-to-date;

Upvotes: 1

Related Questions