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 foo
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?
EDIT:
EXPLAIN ANALYZE
of select above:
"Index Scan using foo_foo_date_idx on foo (cost=0.01..20762.83 rows=10421 width=1534) (actual time=0.180..249.126 rows=19773 loops=1)"
" Index Cond: (date_trunc('day'::text, now()) = date_trunc('day'::text, foo_date))"
"Total runtime: 251.883 ms"
I have changed a day for 15-12-2015. What is more, I have replace *
with only specific columns. Explain plan:
"Index Scan using foo_foo_date_idx on foo (cost=0.00..57876.52 rows=29184 width=62) (actual time=22.384..28243.356 rows=33153 loops=1)"
" Index Cond: ('2015-12-14 00:00:00'::timestamp without time zone = date_trunc('day'::text, foo_date))"
"Total runtime: 28251.876 ms"
Last query could cached i think then... Any other explaination?
EXPLAIN (ANALYZE, BUFFERS)
on a 'slow date'? Also, what are the index and table sizes you have?