Configuring PostgreSQL for read performance

Question

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow.

The primary key (constraint) structure is similar for all our tables: timestamp(Timestamp) ; index(smallint) ; key(integer)

A table can have millions of row, even billion of rows, and a read request is usually for a specific period (timestamp / index) and tag. It's common to have a query that return around 200k lines. Currently, we can read about 15k lines per second but we need to be 10 times faster. Is this possible and if so, how?

Note: PostgreSQL is packaged with our software, so the hardware is different from one client to another.

[Edit] Added details below, performance was better for this test because I don't have access to the real setup right now. I will update as soon as I can access the setup.

[Edit2] Applied "dezso" suggestions, see configuration changes below and the specs of the server used for testing. Yes it's a VM used for testing, the VMs host is a Server 2008 R2 x64 with 24.0 GB of ram.

Server Spec (Virtual Machine VMWare)

Server 2008 R2 x64
2.00 GB of memory
Intel Xeon W3520 @ 2.67GHz (2 cores)

postgresql.conf optimisations

shared_buffers = 512MB (default: 32MB)
effective_cache_size = 1024MB (default: 128MB)
checkpoint_segment = 32 (default: 3)
checkpoint_completion_target = 0.9 (default: 0.5)
default_statistics_target = 1000 (default: 100)
work_mem = 100MB (default: 1MB)
maintainance_work_mem = 256MB (default: 16MB)

Table Definition

CREATE TABLE "AnalogTransition"
(
  "KeyTag" integer NOT NULL,
  "Timestamp" timestamp with time zone NOT NULL,
  "TimestampQuality" smallint,
  "TimestampIndex" smallint NOT NULL,
  "Value" numeric,
  "Quality" boolean,
  "QualityFlags" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag" ),
  CONSTRAINT "FK_AnalogTransition_Tag" FOREIGN KEY ("KeyTag")
      REFERENCES "Tag" ("Key") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE,
  autovacuum_enabled=true
);

Query

The query take about 30 seconds to execute in pgAdmin3, but we would like to have the same result under 5 seconds if possible.

SELECT 
    "AnalogTransition"."KeyTag", 
    "AnalogTransition"."Timestamp" AT TIME ZONE 'UTC', 
    "AnalogTransition"."TimestampQuality", 
    "AnalogTransition"."TimestampIndex", 
    "AnalogTransition"."Value", 
    "AnalogTransition"."Quality", 
    "AnalogTransition"."QualityFlags", 
    "AnalogTransition"."UpdateTimestamp"
FROM "AnalogTransition"
WHERE "AnalogTransition"."Timestamp" >= '2013-05-16 00:00:00.000' AND "AnalogTransition"."Timestamp" <= '2013-05-17 00:00:00.00' AND ("AnalogTransition"."KeyTag" = 56 OR "AnalogTransition"."KeyTag" = 57 OR "AnalogTransition"."KeyTag" = 58 OR "AnalogTransition"."KeyTag" = 59 OR "AnalogTransition"."KeyTag" = 60)
ORDER BY "AnalogTransition"."Timestamp" DESC, "AnalogTransition"."TimestampIndex" DESC
LIMIT 500000;

Explain (Edit2: Updated)

"Limit  (cost=0.00..125668.31 rows=500000 width=33) (actual time=2.193..3241.319 rows=500000 loops=1)"
"  Buffers: shared hit=190147"
"  ->  Index Scan Backward using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..389244.53 rows=1548698 width=33) (actual time=2.187..1893.283 rows=500000 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-16 01:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-16 15:00:00-04'::timestamp with time zone))"
"        Filter: (("KeyTag" = 56) OR ("KeyTag" = 57) OR ("KeyTag" = 58) OR ("KeyTag" = 59) OR ("KeyTag" = 60))"
"        Buffers: shared hit=190147"
"Total runtime: 3863.028 ms"

In my latest test, It took 7 minutes to select my data!!! See below

Explain (Edit3)

"Limit  (cost=0.00..313554.08 rows=250001 width=35) (actual time=0.040..410721.033 rows=250001 loops=1)"
"  ->  Index Scan using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..971400.46 rows=774511 width=35) (actual time=0.037..410088.960 rows=250001 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-22 20:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-24 20:00:00-04'::timestamp with time zone) AND ("KeyTag" = 16))"
"Total runtime: 411044.175 ms"

Thanks a lot for help!!

Please show us the full table definition and all indexes together with the output of an explain analyze. More on posting this kind of questions: wiki.postgresql.org/wiki/Slow_Query_Questions
Agree with horse, there's a notable lack of detail. Table and index sizes, bloat estimates, table and index definitions, info on the host hardware, exact PostgreSQL version, explain (buffers, analyze) output, etc.
Sort Method: external merge Disk: 9896kB - this is not very good. What happens when you try to raise work_mem to over 10 MBs?
As you see from the plan, the query runs in about 3 seconds. The data is already cached and this may not be the case in a real usage scenario, however. But the 30 second you mention comes mainly from transporting (and probably displaying) all the data.
Dual core machine and 2gb RAM and a database that should be read optimized? Sorry, is the res of the machine identical - then maybe get a mobile phone, will have more cache. Table with millions of rows asks for (a) a fast disc subsystem and (b) cache available memory. Many times of that - I think 2gb may even be quite close to the minimum memory for Server 2012, or?

Erwin Brandstetter · Accepted Answer · 2013-06-02 19:52:15Z

Data alignment and storage size

Actually, the overhead per tuple is 24 byte for the tuple header plus 4 byte for the item pointer.
More details in the calculation in this related answer: Use GIN to index bit strings

Also read about the basics of data alignment in this related answer on SO.

We have three columns for the primary key:

PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag" )

"Timestamp"      timestamp (8 bytes)
"TimestampIndex" smallint  (2 bytes)
"KeyTag"         integer   (4 bytes)

Results in:

 4 bytes item pointer (separate from actual row, not counting towards multiple of 8 bytes)
23 bytes for the tuple header
 1 byte  padding for data alignment (or NULL bitmask)
 8 bytes "Timestamp"
 2 bytes "TimestampIndex"
 2 bytes padding for data alignment
 4 bytes "KeyTag" 
 0 padding to the nearest multiple of 8 bytes
-----
44 bytes per tuple

More about measuring object size in this related answer:
Measure the size of a PostgreSQL table row

Order of columns in a multi-column indexes

Read these two questions and answers to understand:
Is a composite index also good for queries on the first field?
Working of indexes in PostgreSQL

The way you have your index (primary key), you can retrieve rows without a sorting step, that's appealing, especially with LIMIT. But retrieving the rows seems extremely expensive.

Generally, in a multi-column index, "equality" columns should go first and "range" columns last:
Multicolumn index and performance

Therefore, try an additional index with reversed column order:

CREATE INDEX analogransition_mult_idx1
    ON "AnalogTransition" ("KeyTag","TimestampIndex", "Timestamp");

It depends on data distribution. But with millions of row, even billion of rows this might be substantially faster.

Tuple size is 8 bytes bigger, due to data alignment & padding. If you are using this as plain index, you might try to drop the third column "Timestamp". May be a bit faster or not (since it might help with sorting).

You might want to keep both indexes. Depending on a number of factors, your original index may be preferable - in particular with a small LIMIT.

autovaccum and table statistics

Your table statistics need to be up to date. I am sure you have autovacuum running.

Since your table seems to be huge and statistics important for the right query plan, I would substantially increase the statistics target for relevant columns:

ALTER  "AnalogTransition" ALTER "Timestamp" column_name SET STATISTICS 1000;

... or even higher with billions of rows. Maximum is 10000, default is 100.

Do that for all columns involved in WHERE or ORDER BY clauses. Then run ANALYZE.

Table layout

While being at it, if you apply what you have learned here about data alignment and padding, this optimized table layout should save some disk space and help performance a little (ignoring pk & fk):

CREATE TABLE "AnalogTransition"(
  "Timestamp" timestamp with time zone NOT NULL,
  "KeyTag" integer NOT NULL,
  "TimestampIndex" smallint NOT NULL,
  "TimestampQuality" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  "QualityFlags" smallint,
  "Quality" boolean,
  "Value" numeric
);

CLUSTER / pg_repack

To optimize read performance for queries that use a certain index (be it your original one or my suggested alternative), you can rewrite the table in the physical order of the index. CLUSTER does that, but it's rather invasive and requires an exclusive lock for the duration of the operation. pg_repack is a more sophisticated alternative that can do the same without an exclusive lock on the table.
This can help substantially with huge tables, since much fewer blocks of the table have to be read.

RAM

Generally, 2GB of physical RAM is just not enough to deal with billions of rows quickly. More RAM might go a long way - accompanied by adapted setting: obviously a bigger effective_cache_size to begin with.

Thanks for the detailed explanations. I tried to reorder my index like you said but it's not faster because of the "Bitmap Heap Scan" step. Also, there are usually several successive requests for the same time period. With the old index, the first request was long but the others were very fast because the time period is the same. With this index change, each query is longer unfortunatly
Thanks, I will do some testing in the following days to validate your answer.
+1. I deal with double digit gigabyte stuff in SQL Server - compressed, uncompressed tables in 200gb range. 32gb are taxed with that - you do not want to use disc too often. It is SLOW.
I added a simple index on KeyTag only and it seems to be pretty fast now. I will also apply your recommendations about data alignment. Thanks a lot!

dezso · Answer 2 · 2013-05-27 12:28:53Z

So, from the plans I see one thing: you index is either bloated (then alongside with the underlying table) or simply isn't really good for this sort of query (I tried to address this in my latest comment above).

One row of the index contains 14 bytes of data (and some for the header). Now, calculating from the numbers given in the plan: you got 500,000 rows from 190147 pages - that means, on average, less than 3 useful rows per page, that is, around 37 bytes per a 8 kb page. This is a very bad ratio, isn't it? Since the first column of the index is the Timestamp field and it is used in the query as a range, the planner can - and does - choose the index to find matching rows. But there is no TimestampIndex mentioned in the WHERE conditions, so filtering on KeyTag isn't very effective as those values supposedly appear randomly in the index pages.

So, one possibility is changing the index definition to

CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp", "KeyTag", "TimestampIndex")

(or, given the load of your system, create this index as a new one:

CREATE INDEX CONCURRENTLY "idx_AnalogTransition" 
    ON "AnalogTransition" ("Timestamp", "KeyTag", "TimestampIndex");

this will take a while for sure but you can still work in the meantime.)

The other possibility that a big proportion of the index pages is occupied by dead rows, which could be removed by vacuuming. You created the table with setting autovacuum_enabled=true - but have you ever started autovacuuming? Or run VACUUM manually?

You may be interested in my answer concerning the calculation of the actual row size.

asked	1 month ago
viewed	205 times
active	21 days ago

Configuring PostgreSQL for read performance

Server Spec (Virtual Machine VMWare)

postgresql.conf optimisations

Table Definition

Query

Explain (Edit2: Updated)

Explain (Edit3)

2 Answers

Data alignment and storage size

Order of columns in a multi-column indexes

autovaccum and table statistics

Table layout

CLUSTER / pg_repack

RAM

Your Answer

Not the answer you're looking for? Browse other questions tagged postgresql performance postgresql-9.1 database-tuning performance-tuning or ask your own question.

Linked

Configuring PostgreSQL for read performance

Server Spec (Virtual Machine VMWare)

postgresql.conf optimisations

Table Definition

Query

Explain (Edit2: Updated)

Explain (Edit3)

2 Answers

Data alignment and storage size

Order of columns in a multi-column indexes

autovaccum and table statistics

Table layout

CLUSTER / pg_repack

RAM

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgresql performance postgresql-9.1 database-tuning performance-tuning or ask your own question.

Linked

Related