Why no index on timestamp column

Question

I've read a lot of articles about date indexing but I can't use index on timestamp properly.

My table:

CREATE TABLE registration
(
  id_reg serial NOT NULL,
  pass_uid character varying(12) NOT NULL,
  direction smallint NOT NULL,
  country character varying(255) NOT NULL,
  fly_number character varying(10) NOT NULL,
  the_date timestamp without time zone NOT NULL DEFAULT now(),
  user_id integer NOT NULL,
  station_number integer NOT NULL,
  fir bytea,
  CONSTRAINT registration_pkey PRIMARY KEY (id_reg)
)

I created index on the_date column:

CREATE INDEX ON registration ((the_date::timestamp));

But my query isn't using index:

 explain select * from registration where the_date between '2001-01-01 00:00:00' and '2015-12-31 00:00:00';

"Seq Scan on registration  (cost=0.00..49481.00 rows=932106 width=232)  (actual time=0.401..1028.491 rows=932466 loops=1)"
"  Filter: ((the_date >= '2001-01-01 00:00:00'::timestamp without time zone) AND (the_date <= '2015-12-31 00:00:00'::timestamp without time zone))"
"  Rows Removed by Filter: 67534"
"Planning time: 0.538 ms"
"Execution time: 1078.553 ms"

Then I deleted old index and created new one:

CREATE INDEX ON registration ((the_date::date));

explain analyze select * from registration where the_date between '2001-01-01' and '2015-12-31';

"Seq Scan on registration  (cost=0.00..49481.00 rows=932010 width=232) (actual time=0.016..738.757 rows=932466 loops=1)"
"  Filter: ((the_date >= '2001-01-01 00:00:00'::timestamp without time zone) AND (the_date <= '2015-12-31 00:00:00'::timestamp without time zone))"
"  Rows Removed by Filter: 67534"
"Planning time: 0.207 ms"
"Execution time: 786.233 ms"

I use PC with Intel Celeron 2.16 GHz Dual Core with RAM 2 GB and Postgres config:

 shared_buffers=256MB
 effective_cache_size=1GB,
 work_mem=64MB
 synchronize_commit=off.

Could you help me with that problem. Thanks in advance

Your `where` condition does not exclude enough rows, so the seq scan is more efficient than an index scan. See e.g. here: http://stackoverflow.com/q/5203755/330315 or: http://stackoverflow.com/q/21187865/330315 or http://www.postgresonline.com/journal/archives/78-Why-is-my-index-not-being-used.html — , Dec 01 '15 at 06:47
I am not sure, but your useless cast in the first index might also prevent the index usage. What happens if you use `CREATE INDEX ON registration the_date)`. For the second index to be _considered_ at all try an explicit cast to a `date` in the query — , Dec 01 '15 at 06:50
CREATE INDEX ON registration (cast(the_date as date)); I did as you said, but still seq_scan is done — Shohruh Raxmatov, Dec 01 '15 at 06:59
You are _still_ casting the value in the index, use: `CREATE INDEX ON registration (the_date)`. And I recommended to use a cast in the **query** not the index (but only _if_ you keep the index on the cast, not on the "raw" value as I have suggested). Either never cast, or cast consistently. — , Dec 01 '15 at 07:00
On a side-note, please don't call a column `*_date` when it is a timestamp. That just confuses everyone. And likewise, don't use a timestamp datatype when you are only interested in the date portion. — Turophile, Dec 01 '15 at 07:19

Why no index on timestamp column

0 Answers0