CODEWITHSUNDEEP
sqlperformancepostgresql
countryroadscat
countryroadscat

Reputation: 1750

Most efficient way to retrieve data by timestamps

I'm using PostgreSQL 9.2.8.

I have table like:

CREATE TABLE foo
(
    foo_date timestamp without time zone NOT NULL,
    -- other columns, constraints
)

This table contains about 4.000.000 rows. One day data is about 50.000 rows.

My goal is to retrieve one day data as fast as possible.

I have created an index like:

CREATE INDEX foo_foo_date_idx
ON foo
USING btree
      (date_trunc('day'::text, foo_date));

And now I'm selecting data like this (now() is just an example, i need data from ANY day):

select * 
from process 
where date_trunc('day'::text, now()) = date_trunc('day'::text, foo_date)

This query lasts about 20 s.

Is there any possiblity to obtain same data in shorter time?

Upvotes: 0

Views: 165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

It takes time to retrieve 50,000 rows. 20 seconds seems like a long time, but if the rows are wide, then that might be an issue.

You can directly index foo_date and use inequalities. So, you might try this version:

create index foo_foo_date_idx2 on foo(foo_date);

select p
from process p
where p.foo_date >= date_trunc('day', now()) and
      p.foo_date < date_trunc('day', now() + interval '1 day');

Upvotes: 1

Related Questions