12

I'm trying to optimize a database table to speed up queries with date restrictions. Currently using Postgres 9.2.

I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp to date parsing:

CREATE INDEX foo_my_timestamp_idx ON foo
USING btree ((my_timestamp::date) DESC);

Now, to increase performance I CLUSTER the table using above index:

CLUSTER foo USING foo_my_timestamp_idx;

According to the manual on CLUSTER, the table ...

is physically reordered based on the index information

I wonder whether there is an impact on performance for other queries using a PK of table (let say id_foo). Are there any downsides?

1 Answer 1

17

Yes, there can be downsides. If other queries look at a different data segments not determined by the date, those may take a performance hit if rows are spread out over more data pages now. Just like your first query profits.

impact on performance for other queries using a PK of table (let say id_foo).

Querying a single row is not affected either way, but multiple rows might be.

CLUSTER rewrites the table in pristine condition, just like VACUUM FULL does: removes dead tuples, compacts the physical size of the table, rewrites indexes. So you might see an immediate positive effect on read performance independent of sort order. (Like you might with VACUUM FULL.)
After CLUSTER, you may still want to run a plain VACUUM on the table to update the visibility map, too - which may allow index-only scans.

CLUSTER locks the table exclusively for the duration of the rewrite, which can take a long time for big tables. If that's not an option, there are community tools that can do the same without exclusive lock - if you are at liberty to install one. See:

All benefits of CLUSTER (and alternatives) shrink with the write frequency on your table.

Also, CLUSTER can actually hurt UPDATE performance by removing "wiggle room" for HOT updates on the same data page. You might be able to counter that effect with a FILLFACTOR setting below 100. Again, depends on locality of updated rows, etc.

Related:

Choice of index

I would probably index and cluster on my_timestamp directly, instead of my_timestamp::date. Nothing lost, something gained. The cast is very cheap, but it's still cheaper not to cast at all. Mainly because some query plans may be able to use the column value from the index directly, and the index can support additional queries.

CREATE INDEX foo_my_timestamp_idx ON foo (my_timestamp);

Even though a date occupies only 4 bytes on disk and a timestamp occupies 8 bytes, the difference is typically lost to alignment padding for your case, and both indexes have exactly the same size.

Update: this changes with index deduplication in Postgres 13. Now, duplicate index entries can be compressed, so an index on just the date may end up being (much) smaller. The overall benefit depends on many factors. See:

The order for multiple rows of the same day resulting from your date index is arbitrary. There can still be two identical timestamps, but with 6 fractional digits that's typically unlikely. A deterministic order of rows has advantages.

I also dropped the DESC key word since Postgres can read indexes backwards virtually as fast a forwards. Sort order matters for multicolumn indexes, though! See:

Instead of:

SELECT * FROM foo
WHERE my_timestamp::date = '2016-07-25';

You would now use:

SELECT * FROM foo
WHERE  my_timestamp >= '2016-07-25'  -- this is a timestamp literal now
WHERE  my_timestamp <  '2016-07-26';

Same performance.

If you don't need the time component of the column at all, convert the column to date ...

How to roll back CLUSTER?

CLUSTER on a single table can be rolled back with ROLLBACK like any other regular command as long as the transaction has not been committed.

However, I quote the manual:

CLUSTER without any parameter reclusters all the previously-clustered tables in the current database that the calling user owns, or all such tables if called by a superuser. This form of CLUSTER cannot be executed inside a transaction block.

You can always run CLUSTER with a different index to change the physical order of rows once more.

2
  • Awsome answer, I need to ask then, how to 'rollback' CLUSTER? Do I need to CLUSTER using a PK now? Jul 28, 2016 at 9:34
  • @ilovkatie: I added a bit how to roll back. Aug 4, 2016 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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