I'm aware that this question might be under the guidelines of being closed, but I'm out of ideas at the moment on how to optemize this table any further.
Given a have a few billion records with roughly 150 million a day, I assume search times will be high, but the following takes almost an hour to complete on descent hardware:
SELECT * FROM table WHERE time BETWEEN '2014-06-16 00:00:00'::timestamp AND '2014-06-17 00:00:00'::timestamp;
The table consists of the following:
CREATE TABLE messages (id BIGSERIAL, time TIMESTAMP WITH TIMEZONE, message varchar(120)); CREATE INDEX idx_time ON table(time ASC); GRANT SELECT,INSERT,UPDATE ON table TO user; GRANT UPDATE,USAGE,SELECT ON table_id_seq TO user;
Doing a direct query with
psql -d database and selecting a range where i know there's no data it returns
0 rows instantly.. so the index is fine, i think..
So.. I've created a procedural function to return values one by one, but according to sources these functions doesn't utelize indexes at all(?), which might be true because the process to deliver records are quite slow and I think this is where my issue is, hughe bulk of data being returns to slow with a indexation not working on functions.
The function looks like:
DROP FUNCTION IF EXISTS myfunc(refcursor); CREATE FUNCTION myfunc(refcursor) RETURNS refcursor AS $$ BEGIN OPEN $1 FOR SELECT * FROM table WHERE time BETWEEN '2014-06-16 00:00:00'::timestamp AND '2014-06-17 00:00:00'::timestamp; RETURN $1; END; $$ LANGUAGE plpgsql;
Any ideas why the massive bottlenecks? except the aparent that there's many records to deal with? The only reason for not reconstructing a better table structure where data is separated per day for instance is convenience right now.. So that's a planed change, but for the time being is there any way to make the search times go faster?