Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question.
other queries using a PK of table (let say id_foo)
That could be anything. It depends on what you have and what you query exactly. Querying a single row is not affected either way, but multiple rows might be.
Be aware that
CLUSTER rewrites the table in pristine condition 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 the sort order. (Much like you would get with
CLUSTER, you may want to run a plain
VACUUM on the table to update the visibility map, too - which may allow index-only scans.
All benefits of
CLUSTER shrink with the write frequency.
Also, if you have many updates to the table,
CLUSTER can actually hurt write 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.
Either way, I would probably not index and cluster on
my_timestamp::date, but on
my_timestamp directly. Nothing lost, something gained. The cast is very cheap, but it's still cheaper not to cast at all. And the index can support more 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.
The order of multiple rows on the same day resulting from your expression index is arbitrary. There can still be two identical timestamps, but with 6 fractional digits that's normally very unlikely. Aside from that you get a deterministic order of rows, which can have various 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!) More:
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';
If you don't need the time component of the column at all, convert the column to
How to roll back
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.