Update 2020-08-04:

Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are easily handling a few billion rows in the table(s). The index-only scans are a life saver and it wouldn't be possible without.

Using PostgreSQL 9.2, I have troubles with slow queries on a relatively large table (200+ million rows). I'm not trying anything crazy, just adding historic values. Below is the query and the query plan output.

My table layout:

                                   Table "public.energy_energyentry"
  Column   |           Type           |                            Modifiers
 id        | integer                  | not null default nextval('energy_energyentry_id_seq'::regclass)
 prop_id   | integer                  | not null
 timestamp | timestamp with time zone | not null
 value     | double precision         | not null
    "energy_energyentry_pkey" PRIMARY KEY, btree (id)
    "energy_energyentry_prop_id" btree (prop_id)
    "energy_energyentry_prop_id_timestamp_idx" btree (prop_id, "timestamp")
Foreign-key constraints:
    "energy_energyentry_prop_id_fkey" FOREIGN KEY (prop_id) REFERENCES gateway_peripheralproperty(id) DEFERRABLE INITIALLY DEFERRED

The data ranges from 2012-01-01 till now, with new data constantly being added. There are about 2.2k distinct values in the prop_id foreign key, distributed evenly.

I notice that the row estimates aren't far off, but the cost estimates seem larger by factor 4x. This probably isn't an issue, but is there anything I could do about it?

I expect that disk access might be the issue, since the table isn't in memory all the time.

SELECT SUM("value") 
FROM "energy_energyentry" 
  AND "timestamp">'2014-06-11' 
  AND "timestamp"<'2014-11-11'
 Aggregate  (cost=214481.45..214481.46 rows=1 width=8) (actual time=51504.814..51504.814 rows=1 loops=1)
   ->  Index Scan using energy_energyentry_prop_id_timestamp_idx on  energy_energyentry (cost=0.00..214434.08 rows=18947 width=8) (actual time=136.030..51488.321 rows=13578 loops=1)
         Index Cond: ((prop_id = 82411) AND ("timestamp" > '2014-06-11 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2014-11-11 00:00:00+00'::timestamp with time zone))
 Total runtime: 51504.841 ms

Any suggestions how to make this faster?
I'm also fine with just hearing I didn't do anything weird.

  • 2
    Please tell us what your table looks like, what indexes it has and the spread of data. – Colin 't Hart Oct 30 '14 at 9:47
  • I added the additional informatie you asked. Dunno whether I missed anything. – Exelian Oct 30 '14 at 10:25
  • 3
    Strange: Your explain analyze shows prop_time_idx, yet the table definition shows entry_prop_id_timestamp_idx. Is this the same index? Please fix. – Colin 't Hart Oct 30 '14 at 10:52
  • If you refer by 'the cost estimates seem to be a factor 4x larger' to the fact that the cost numbers are about 4 times those of the actual time, then please notice that the two has nothing to do with each other. The cost is only an estimate, helping the query optimizer to choose the best looking plan. Outside of this context, it is usually a meaningless value. – dezso Oct 30 '14 at 10:53
  • 2
    How many percent of the table does your date range represent (without taking into consideration the values for prop)? If just a small percentage, maybe an index on ("timestamp", prop) would be better. Multiple indexes with the same leading column(s) (prop in your case) is also often redundant. – Colin 't Hart Oct 30 '14 at 10:55

Your table is big, and so is any index spanning the whole table. Assuming that:

  • only new data (with timestamp = now()) is entered
  • existing rows are neither changed nor deleted.
  • you have data since 2012-01-01 but queries are predominantly on the current year (?)

I would suggest a partial, multi-column (covering!) index:

CREATE INDEX ON energy_energyentry (prop_id, "timestamp", value)
WHERE "timestamp" >= '2014-01-01 0:0';  -- adapt to your needs

Only include the time range that is queried regularly. Effectiveness deteriorates over time with new entries. Recreate the index from time to time. (You may need to adapt your queries.) See linked answer below.

The last column value is only included to get index-only scans out of this. Aggressive autovacuum setting may help by keeping the visibility map up to date, like @jjanes already mentioned.

The partial index should fit into RAM more easily and stay there longer.

You may need to include this WHERE condition in queries to make the planner understand the index is applicable to the query, like:

SELECT sum(value) AS sum_value
FROM   energy_energyentry
WHERE  prop_id = 82411 
AND   "timestamp" > '2014-06-11 0:0' 
AND   "timestamp" < '2014-11-11 0:0'
AND   "timestamp" >= '2014-01-01 0:0'; -- seems redundant, but may be needed

Since your query is summing a lot of rows (rows=13578), this is going to take some time, even with an index-only scan. It shouldn't be anywhere near 50 seconds, though. Less than a second on any halfway decent hardware.

Related (but ignore CLUSTER and FILLFACTOR, both are irrelevant if you can get index-only scans out of this):

Since you currently have an index on (prop_id, "timestamp"), the additional index on just (prop_id) may cost more than it's worth:

| improve this answer | |
  • 1
    Now that Postgres supports BRIN indexes, would that be useful here? I plan on storing about 140 million rows on data on postgres, is BRIN the right index to use for table that large? – Arya Apr 13 '19 at 5:27

If you make the index on (prop_id, "timestamp","value"), then it could use an index-only scan to compute the value without ever visiting the table. This could save a lot of random disk access.

To get the most benefit, you need to be aggressive about vacuuming the table. The default autovac settings are not aggressive enough for insert-only tables on which you wish to efficiently support index-only scans.

| improve this answer | |
  • Adding the value might be interesting indeed, I'll take a look whether that'll speed things up. Do you have any suggestions for vacuum settings or documentation I can look at? – Exelian Oct 30 '14 at 16:11

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.