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.