I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table
time_limits has two timestamp fields and one property integer. Indeed there are other info columns but not used for any queries, only for storing information, I won't speak about these ones.
create table ( start_date_time timestamp, end_date_time timestamp, id_phi integer, primary key(start_date_time,end_date_time,id_phi);
this table now contains something like 2.000.000 records.
but the time spent were so enormous for a query like:
select * from time_limits as t where t.id_phi=0 and t.start_date_time <= timestamp'2010-08-08 00:00:00' and t.end_date_time >= timestamp'2010-08-08 00:05:00'
that I tried creating the inversed index:
create index idx_inversed on time_limits(id_phi,start_date_time,end_date_time);
I have the impression that the times spent are better: The time needed for accessing the records in the middle of the table seems to be more reasonable. ( somwhere between 40 and 90 seconds)
But's its several tens of seconds for values in the middle of the time range. And twice more when targeting the end of the table (chronologically speaking).
So I have tried explain/analyze for the first time. You can see the result here on depeszcom.
QUERY PLAN Bitmap Heap Scan on time_limits (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1) Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone)) -> Bitmap Index Scan on idx_time_limits_phi_start_end (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1) Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone)) Total runtime: 44.507 ms (5 rows)
What could I do to optimize the search ? You can see all the time is spent scanning the two timestamps columns once the id_phi is set to
0. And I don't understand the big scan (60K rows!) on the timestamps, aren't they indexed ? ( both the primary key and the idx_inversed I created)
Should I change from timestamp types to something else ?
I have read (really little) about GIST and GIN indexes, I just know they can be more efficient on certain conditions for custom types. Should I go this way to have a efficient time index ?