Postgres Query Tuning

Question

I have a table that holds historical records. Whenever a count gets updated, a record is added specifying that a new value was fetched at that time. The table schema looks like this:

    Column     |           Type           |                             Modifiers
---------------+--------------------------+--------------------------------------------------------------------
 id            | integer                  | not null default nextval('project_accountrecord_id_seq'::regclass)
 user_id       | integer                  | not null
 created       | timestamp with time zone | not null
 service       | character varying(200)   | not null
 metric        | character varying(200)   | not null
 value         | integer                  | not null

Now I'd like to get the total number of records updated each day, for the last seven days. Here's what I came up with:

SELECT
    created::timestamp::date as created_date,
    count(created)
FROM
    project_accountrecord
GROUP BY
    created::timestamp::date
ORDER BY
    created_date DESC
LIMIT 7;

This runs slowly (11406.347ms). EXPLAIN ANALYZE gives:

Limit  (cost=440939.66..440939.70 rows=7 width=8) (actual time=24184.547..24370.715 rows=7 loops=1)
   ->  GroupAggregate  (cost=440939.66..477990.56 rows=6711746 width=8) (actual time=24184.544..24370.699 rows=7 loops=1)
         ->  Sort  (cost=440939.66..444340.97 rows=6802607 width=8) (actual time=24161.120..24276.205 rows=92413 loops=1)
               Sort Key: (((created)::timestamp without time zone)::date)
               Sort Method: external merge  Disk: 146328kB
               ->  Seq Scan on project_accountrecord  (cost=0.00..153671.43 rows=6802607 width=8) (actual time=0.017..10132.970 rows=6802607 loops=1)
 Total runtime: 24420.988 ms

There are a little over 6.8 million rows in this table. What can I do to increase performance of this query? Ideally I'd like it to run in under a second so I can cache it and update it in the background a couple of times a day.

Try to create an index on created::date and add a where clause where created::date between current_date - 7 and current_date. Btw: what is the purpose of casting created to a timestamp? It is already a timestamp. — user330315
– user330315, Commented Oct 1, 2013 at 10:19
@a_horse_with_no_name Interesting I didn't notice I was doing that. I'm adding the index to created::date and WHERE clause now to check that — Josh K
– Josh K, Commented Oct 1, 2013 at 10:22
Hit me in the head here and tell me why create index on project_accountrecord (created::date DESC); returns ERROR: syntax error at or near "::". — Josh K
– Josh K, Commented Oct 1, 2013 at 10:31
You would need to put that in paranthesis: create index on project_accountrecord ( (created::date) DESC); but unfortuantely that still wouldn't work. I overlooked your with time zone definition. And the cast from timestamp with time zone to date isn't "stable", so you cannot index that expression. But an index on created itself might already help. — user330315
– user330315, Commented Oct 1, 2013 at 10:42

Tomasz Myrta · Accepted Answer · 2013-10-01 10:27:51Z

2

Now, your query must scan whole table, calculate result and limit to 7 recent days. You can speedup query by scanning only last 7 days (or more if you don't update records every day):

where created_date>now()::date-'7 days'::interval

Another aproach is to cache historical results in extra table and count only current day.

answered Oct 1, 2013 at 10:27

Tomasz Myrta

1,1449 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Query Tuning

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related