7

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4.

My data is completely static, so I am able to insert all data, then create all indexes.

In this 1.2TB table, I have a column named run_id that cleanly divides the data. We've gotten great performance by creating indexes that cover a range of run_ids. Here's an example:

CREATE INDEX perception_run_frame_idx_run_266_thru_270
ON run.perception
(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

These partial indexes give us the desired query speed. Unfortunately, the creation of each partial index takes about 70 minutes.

It looks like we are CPU limited (top is showing 100% for the process).
Is there anything I can do to speed up the creation of our partial indexes?

System specs:

  • 18 core Xeon
  • 192GB RAM
  • 12 SSDs in RAID
  • Autovacuums are turned OFF
  • maintenance_work_mem: 64GB (Too high?)

Table specs:

  • Size: 1.26 TB
  • Number of rows: 10.537 Billion
  • Typical index size: 3.2GB (there is a ~.5GB variance)

Table definition:

CREATE TABLE run.perception(
id bigint NOT NULL,
run_id bigint NOT NULL,
frame bigint NOT NULL,
by character varying(45) NOT NULL,
by_anyone bigint NOT NULL,
by_me bigint NOT NULL,
by_s_id integer,
owning_p_id bigint NOT NULL,
obj_type_set bigint,
seq integer,
subj_id bigint NOT NULL,
subj_state_frame bigint NOT NULL,
CONSTRAINT perception_pkey PRIMARY KEY (id))

(Don't read too much into the column names -- I've obfuscated them somewhat.)

Background info:

  • We have a separate team onsite that consumes this data, but really there are only one or two users. (This data is all generated via a simulation.) Users only start analyzing the data once inserts are finished and indexes are completely built. Our main concern is reducing the time required to generate usable data, and right now the bottleneck is index creation time.
  • Query speed has been completely adequate when using partials. In fact, I think we could increase the number of runs that each index covers, and still maintain good-enough query performance.
  • My guess is that we will have to partition the table. We are trying to exhaust all other options before taking that route.
  • This additional information would be instrumental: data types of involved columns, typical query, cardinality (count of rows), how many different run_id? Evenly distributed? Size of resulting index on disk? Data is static, ok. But are you the only user? – Erwin Brandstetter May 26 '15 at 22:04
  • Updated with more info. – burnsy May 27 '15 at 15:03
  • 1
    "Autovacuums are turned OFF" - why? That is a really bad idea. This prevents the collecting of statistics and thus will yield bad query plans – a_horse_with_no_name Jun 8 '15 at 21:32
  • @a_horse_with_no_name We manually kick of an analyze after all data is inserted – burnsy Jun 10 '15 at 16:16
  • Your situation is still unclear to me. What do your queries look like? If your table is completely static, then what do you mean by We have a separate team onsite that consumes this data? Do you just index the range run_id >= 266 AND run_id <= 270 or the whole table? What's the life expectancy of each index / how many queries will use it? How many different values for run_id? Sound like ~ 15 Mio. rows per run_id, which would make it around 800 different values for run_id? Why are obj_type_set, by_s_id, seq not defined NOT NULL? What rough percentage of NULL values for each? – Erwin Brandstetter Aug 25 '15 at 2:54
6

I think your best bet is a BRIN index in Postgres 9.5. See below.


Table layout

Whatever else you do, you can save 8 bytes lost to padding due to alignment requirements per row by ording columns like this:

CREATE TABLE run.perception(
  id               bigint NOT NULL PRIMARY KEY
, run_id           bigint NOT NULL
, frame            bigint NOT NULL
, by_anyone        bigint NOT NULL
, by_me            bigint NOT NULL
, owning_p_id      bigint NOT NULL
, subj_id          bigint NOT NULL
, subj_state_frame bigint NOT NULL
, obj_type_set     bigint
, by_s_id          integer
, seq              integer
, by               varchar(45) NOT NULL -- or just use type text
);

Makes your table 79 GB smaller if none of the columns has NULL values. Details:

Also, you only have three columns that can be NULL. The NULL bitmap occupies 8 bytes for 9 - 72 columns. If only one integer column is NULL, there is a corner case for a storage paradox: it would be cheaper to use a dummy value instead: 4 bytes wasted but 8 bytes saved by not needing a NULL bitmap for the row. More details here:

Partial indexes

Depending on your actual queries it might be more efficient to have these five partial indices instead of the one above:

CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 266;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 267;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 268;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 269;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 270;

Run one transaction for each.

Removing run_id as index column this way saves 8 bytes per index entry - 32 instead of 40 bytes per row. Each index is also be cheaper to create, but creating five instead of just one takes substantially longer for a table that's too big to stay in cache (like @Jürgen and @Chris commented). So that may or may not be useful for you.

Partitioning

The documentation:

All constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.

Bold emphasis mine. Consequently, estimating 1000 different values for run_id, you would make partitions spanning around 10 values each.

BRIN index

This new feature of the upcoming Postgres 9.5 is just what you are looking for. Much faster index creation, much smaller index, but queries are not as fast. The documentation for pg 9.5:

BRIN stands for Block Range Index. BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table. A block range is a group of pages that are physically adjacent in the table; for each block range, some summary info is stored by the index.

Read on, there is more.
Depesz ran a preliminiary test.

The optimum for your case: If you can write rows clustered on run_id, your index becomes very small and creation much cheaper.

CREATE INDEX foo ON run.perception USING brin (run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

You might even just index the whole table.


maintenance_work_mem

I missed that you are already adjusting for maintenance_work_mem in my first read. I'll leave quote and advice in my answer for reference. Per documentation:

maintenance_work_mem (integer)

Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 megabytes (64MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.

Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high. It may be useful to control for this by separately setting autovacuum_work_mem.

I would only set it as high as needed - which depends on the unknown (to us) index size. And only locally for the executing session. As the quote explains, a too-high general setting can starve the server otherwise, because autovacuum may claim more RAM, too. Also, don't set it much higher than needed, even in the executing session, free RAM might be put to good use in caching data.

It could look like this:

BEGIN;

SET LOCAL maintenance_work_mem = 10GB;  -- depends on resulting index size

CREATE INDEX perception_run_frame_idx_run_266_thru_270 ON run.perception(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

COMMIT;

About SET LOCAL:

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not.

To measure object sizes:

The server should generally be configured reasonably otherwise, obviously.

  • I bet his work is IO bound since the table is much larger than RAM. Reading the table even more often will make the problem worse, regardless if there's enough memory to sort each created index in memory or not. – Jürgen Strobel Aug 24 '15 at 18:25
  • I'm with Jurgen on this one. I believe that due to the size of the table, in essence you have to perform a full sequential scan on the table per index created. Plus, I'm not sure you'll see all that much performance increase from creating the separate partial indexes (I'm 90% sure you won't see any increase, but on this I could be off.) I believe a better solution for index creation would involve creating one index over the full range that you wish to query as a "single partial index" to keep the overall build time down. – Chris Aug 24 '15 at 19:26
  • @Chris: I agree, 5 indexes will take longer to create than just one (even if all of them together are smaller, creating each index is cheaper and queries may be faster). Thinking about it some more, this should be a perfect use case for a BRIN index in Postgres 9.5. – Erwin Brandstetter Aug 25 '15 at 14:31
2

Maybe this is just over-engineered. Have you actually tried using a single full index? Partial indices covering the whole table together do not provide much gain, if any, for index lookups, and from your text I infer that you have indices for all run_ids? There may be some advantages to index scans with partial indices, still I would benchmark the simple one-index solution first.

For each index creation you need a full IO bound scan through the table. So creating several partial indices requires far more IO reading the table than for a single index, although the sort will spill to disk for the single large index. If you insist on partial indices you might try building all (or several) indices at the same time in parallel (memory permitting).

For a rough estimate on maintenance_work_mem required to sort all run_ids, which are 8-byte bigints, in memory you'd need 10.5 * 8 GB + some overhead.

0

You could also create the indexes on other tablespaces than the default. These tablespaces could point to disks that are not redundant (just recreate the indexes if they fail), or are on faster arrays.

You might also consider partitioning the table using the same criteria as your partial indexes. This would allow for the same speed as the index when querying, without actually creating any index at all.

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.