0

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?

  • Do you actually have a reason for using a cursor? Why not simply return the results from RETURN QUERY (or a SQL function)? Google for RBAR. – dezso Jun 25 '14 at 7:46
  • Mainly because the memory on the machine crunching the data does not have enough RAM to store the entire data returned from one query. Paged results is something being used but is not overly appritiated, that also takes time because LIMIT takes a good long time to process aparently. – Torxed Jun 25 '14 at 8:31
  • Correction, LIMIT appears to be working. Not sure why I got the impression it didn't. – Torxed Jun 25 '14 at 9:02
  • Given a have a few billion records with roughly 150 million a day: consider partitioning by day or even smaller time segments. – Daniel Vérité Jun 25 '14 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.