8

I am using Postgres 9.3 through Heroku.

I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ranges and those calls can take up to 40 seconds and would love to hear suggestions on how to improve that.

I have the following index in place on this table:

CREATE INDEX idx_traffic_partner_only ON traffic (dt_created) WHERE campaign_id IS NULL AND uuid_self <> uuid_partner;

Here is an example SELECT statement:

SELECT SUM("clicks") AS clicks, SUM("impressions") AS impressions
FROM "traffic"
WHERE "uuid_self" != "uuid_partner"
AND "campaign_id" is NULL
AND "dt_created" >= 'Sun, 29 Mar 2015 00:00:00 +0000'
AND "dt_created" <= 'Mon, 27 Apr 2015 23:59:59 +0000' 

And this is the EXPLAIN ANALYZE:

Aggregate  (cost=21625.91..21625.92 rows=1 width=16) (actual time=41804.754..41804.754 rows=1 loops=1)
  ->  Index Scan using idx_traffic_partner_only on traffic  (cost=0.09..20085.11 rows=308159 width=16) (actual time=1.409..41617.976 rows=302392 loops=1)
      Index Cond: ((dt_created >= '2015-03-29'::date) AND (dt_created <= '2015-04-27'::date))
Total runtime: 41804.893 ms

http://explain.depesz.com/s/gGA

This question is very similar to another on SE, but that one used an index across two column timestamp ranges and the index planner for that query had estimates that were way off. The main suggestion there was to create a sorted multi-column index, but for single column indexes that doesn't have much of an effect. The other suggestions were to use CLUSTER / pg_repack and GIST indexes, but I haven't tried them yet, since I'd like to see if there is a better solution using regular indexes.

Optimizing queries on a range of timestamps (two columns)

For reference, I tried the following indexes, which were not used by the DB:

INDEX idx_traffic_2 ON traffic (campaign_id, uuid_self, uuid_partner, dt_created);
INDEX idx_traffic_3 ON traffic (dt_created);
INDEX idx_traffic_4 ON traffic (uuid_self);
INDEX idx_traffic_5 ON traffic (uuid_partner);

EDIT: Ran EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) and these were results:

Aggregate  (cost=20538.62..20538.62 rows=1 width=8) (actual time=526.778..526.778 rows=1 loops=1)
  Output: sum(clicks), sum(impressions)
  Buffers: shared hit=47783 read=29803 dirtied=4
  I/O Timings: read=184.936
  ->  Index Scan using idx_traffic_partner_only on public.traffic  (cost=0.09..20224.74 rows=313881 width=8) (actual time=0.049..431.501 rows=302405 loops=1)
      Output: id, uuid_self, uuid_partner, impressions, clicks, dt_created... (other fields redacted)
      Index Cond: ((traffic.dt_created >= '2015-03-29'::date) AND (traffic.dt_created <= '2015-04-27'::date))
      Buffers: shared hit=47783 read=29803 dirtied=4
      I/O Timings: read=184.936
Total runtime: 526.881 ms

http://explain.depesz.com/s/7Gu6

Table definition:

CREATE TABLE traffic (
    id              serial,
    uuid_self       uuid not null,
    uuid_partner    uuid not null,
    impressions     integer NOT NULL DEFAULT 1,
    clicks          integer NOT NULL DEFAULT 0,
    campaign_id     integer,
    dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
    dt_updated      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
)

id is the primary key and uuid_self, uuid_partner, and campaign_id are all foreign keys. The dt_updated field is updated with a postgres function.

  • explain (buffers, analyze, verbose) ... might shed more light. – Craig Ringer Apr 28 '15 at 1:13
  • One essential piece of information is missing here: the exact table definition of traffic. Also: why does the second EXPLAIN show a drop from 42 sec to 0.5 sec? Was the first run with cold cache? – Erwin Brandstetter Apr 28 '15 at 5:25
  • Just added the table definition to the question. Yes, the 42 to 0.5 sec was probably due to a cold cache, but since there are so many updates, this would probably be a fairly common occurrence. I just ran the EXPLAIN ANALYZE again and this time it took 56s. I ran it once more and it went down to .4s. – Evan Appleby Apr 28 '15 at 21:41
  • It's safe to assume there is a PK constraint on id? Any other constraints? I see two columns that can be NULL. What's the percentage of NULL values in each? What do you get for this? SELECT count(*) AS ct, count(campaign_id)/ count(*) AS camp_pct, count(dt_updated)/count(*) AS upd_pct FROM traffic; – Erwin Brandstetter Apr 29 '15 at 0:39
  • Yeah, ID has a PK constraint and uuid_self, uuid_partner, and campaign_id have FK constraints. Campaign_id is 99%+ NULL and dt_updated is 0% NULL. – Evan Appleby Apr 29 '15 at 1:10
3

Two things that are very odd here:

  1. The query select 300k rows from a table with 1M+ rows. For 30 % (or anything over 5 % - depends on row size and other factors) it doesn't typically pay to use an index at all. We should see a sequential scan.

    The exception would be index-only scans, which I don't see here. The multicolumn index @Craig suggested would be the best option if you get index-only scans out of it. With lots of updates like you mentioned, this may not work out, in which case you are better off without the additional columns - and just the index you already have. You might be able to make it work for you with more aggressive autovacuum settings for the table. You can adjust parameters for individual tables.

  2. While Postgres is going to use the index I would certainly expect to see a bitmap index scan for that many rows, not a plain index scan, which is typically the better choice for a low percentage of rows. As soon as Postgres expects multiple hits per data page (judging from its statistics on the table) it will typically switch to a bitmap index scan.

Judging from that I would suspect that your cost settings are inadequate (and possibly the table statistics, too). You may have set random_page_cost and / or cpu_index_tuple_cost too low, relative to seq_page_cost. Follow the links and read the manual.

Would also fit with the observation that cold cache is large factor, as we worked out in comments. Either you are accessing (parts of) tables that nobody has touched in a long time or you are running on a test system where the cache isn't populated (yet)?
Else you just don't have enough RAM available to cache most of the relevant data in your DB. Consequently, random access is much more expensive than sequential access when data resides in cache. Depending on the actual situation you may have to adjust to get better query plans.

One other factor has to be mentioned for slow response on the first read only: Hint bits. Read details in the Postgres Wiki and this related question:

Or the table is extremely bloated, in which case an index scan would make sense and I would refer back to CLUSTER / pg_repack in my previous answer that you quoted. (Or just VACUUM FULL) And investigate your VACUUM settings. Those are important with many inserts and updates every day.

Depending on UPDATE patterns also consider a FILLFACTOR below 100. If you mostly update only newly added rows, set the lower FILLFACTER after compacting your table, so that only new pages keep some wiggle room for updates.

Schema

campaign_id is 99%+ NULL and dt_updated is 0% NULL.

Adjust the sequence of columns slightly, to save 8 bytes per row (in the 99% of cases where campaign_id is NULL):

CREATE TABLE traffic (
    uuid_self       uuid not null REFERENCES ... ,
    uuid_partner    uuid not null REFERENCES ... ,
    id              serial PRIMARY KEY,
    impressions     integer NOT NULL DEFAULT 1,
    clicks          integer NOT NULL DEFAULT 0,
    campaign_id     integer,
    dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
    dt_updated      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
);

Detailed explanation and links to more:

To measure:

  • Thanks for the suggestion. I currently rely on the built-in auto-vacuuming set up through Heroku and the traffic table gets vacuumed just about every day. I'll look more into changing the table statistics and Fill Factor and using pg_repack and report back. – Evan Appleby Apr 28 '15 at 21:51
2

It looks to me like you're querying lots of data in a big index, so it's slow. Nothing notably wrong there.

If you're on PostgreSQL 9.3 or 9.4, you could try to see if you can get an index-only scan by making this into a covering index of sorts.

CREATE INDEX idx_traffic_partner_only 
ON traffic (dt_created, clicks, impressions)
WHERE campaign_id IS NULL 
  AND uuid_self <> uuid_partner;

PostgreSQL doesn't have true covering indexes or support for index terms that are just values, not part of the b-tree, so this is slower and more expensive than it might be with those features. It might still be a win over a plain index scan if vacuum runs often enough to keep the visibility map up to date.


Ideally PostgreSQL would support auxillary data fields in an index like in MS-SQL Server (this syntax will NOT WORK in PostgreSQL):

-- This will not work in PostgreSQL (at least 9.5)
-- it's an example of what I wish did work. Don't
-- comment to say it doesn't work.
--
CREATE INDEX idx_traffic_partner_only 
ON traffic (dt_created)
INCLUDING (clicks, impressions) -- auxillary data columns
WHERE campaign_id IS NULL 
  AND uuid_self <> uuid_partner;
  • Thanks for the suggestion. I tried the covering index and the DB disregarded it and still used the other index. Would you suggest removing the other index and only using the covering index (or alternatively, only using multiple covering indexes for each situation that requires it)? I also added the EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) in the original question. – Evan Appleby Apr 28 '15 at 1:40
  • Odd. Maybe the planner isn't smart enough to pick an index-only scan if it sees more than one aggregate, but I would've thought it could. Try playing with the cost parameters (random_page_cost etc). Also, for testing purposes only see if set enable_indexscan = off and set enable_seqscan = off then re-running forces an index-only scan, and if so, what its cost estimates from explain analyze are. – Craig Ringer Apr 28 '15 at 1:43

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.