The index on a date column makes sense when you are going to search the table for a given date(s), e.g.:

select * from test
where the_date = '2016-01-01';
-- or

select * from test
where the_date between '2016-01-01' and '2016-01-31';
-- etc

In these queries there is no matter whether the sort order of primary key and the date column are the same or not. Hence rewriting the data to the new table will be useless. Just create an index.

However, if you are going to use the index only in ORDER BY:

select * from test
order by the_date;

then a primary key integer index may be significantly (2-4 times) faster then an index on a date column.

You speak of "date", but original_created_at is type timestamptz. I assume you do not actually want "values ​​from that date", but just for that timestamp.

A single multicolumn index can cover searches for either: original_created_at or for original_created_at + user_id. Just make sure to put original_created_at first:

CREATE INDEX ON example_table (original_created_at, user_id);

See:

• Is a composite index also good for queries on the first field?

Also, I would rearrange table columns to save a couple of bytes per row and make your searches a tiny bit faster yet, with the two filter columns first:

CREATE TABLE IF NOT EXISTS example_table (
  original_created_at timestamptz NOT NULL DEFAULT now()
, user_id             uuid NOT NULL
, id                  serial  PRIMARY KEY  -- do you actually have use for this?
, external_id         text NOT NULL
, category            text NOT NULL
, data                jsonb NOT NULL DEFAULT '{}'::jsonb
, CONSTRAINT external_id_uq UNIQUE (external_id)
);

See:

• Calculating and saving space in PostgreSQL

Since external_id_uq is UNIQUE NOT NULL anyway, you may be able to drop the surrogate PK column id.

bigint is more efficient for user_id than uuid. Not least, it makes above index smaller. Only use the type uuid if you actually need it. See:

• PostgreSQL using UUID vs Text as primary key

If there are lots of rows for the same timestamp (or timestamp + id combo), and rows are physically sorted, a BRIN index may be an alternative due its small size.

First off, can it be? You write:

I want to fetch all data with the updated_at field with a date of a few days ago.

But your WHERE condition is:

(date(updated_at)) < (date(now())-7)

Should be >?

Indexes

For optimal performance, you could ...

• partition your indexes
• exclude irrelevant rows from the indexes
• automatically recreate indexes at off-hours with updated predicate.

Your indexes could look like:

CREATE INDEX objects_id_updated_at_idx ON objects ((updated_at::date) DESC NULLS LAST)
WHERE  id BETWEEN 0 AND 999999
AND    updated_at > '2012-10-01 0:0'::timestamp;  -- some minimum date

CREATE INDEX objects_id_updated_at_idx ON objects ((updated_at::date) DESC NULLS LAST)
WHERE  id BETWEEN 1000000 AND 1999999
AND    updated_at > '2012-10-01 0:0'::timestamp;  -- some minimum date

-- etc.

(Assuming updated_at is type timestamp. With timestamptz, the cast to date is not IMMUTABLE and you need to define the time zone first ...)

The second condition excludes irrelevant rows from the index right away, which should make it smaller and faster - depending on your actual data distribution. In accordance with my preliminary comment, I am assuming you want newer rows.

The condition also automatically excludes NULL values in updated_at - which you seem to allow in the table and obviously want to exclude in the query. The usefulness of the index deteriorates over time. The query always retrieves the latest entries. Recreate the index with an updated WHERE clause periodically. This requires an exclusive lock on the table, so do it at off hours. There is also CREATE INDEX CONCURRENTLY to minimize the duration of locks:

CREATE INDEX CONCURRENTLY objects_id_up_201211_idx ...; -- create new idx
DROP INDEX CONCURRENTLY objects_id_up_201210_idx;       -- then drop old

DROP INDEX allows CONCURRENTLY since Postgres 9.2.

Related answer on SO:

• Postgres returns records in wrong order

To further optimize, you could use CLUSTER like we mentioned in the comments. But you need a full index for that. Doesn't work with a partial index. You would create temporarily:

CREATE INDEX objects_full_idx ON objects (id/1000000, (updated_at::date) DESC NULLS LAST);

This form of the full index matches the sort order of above partial indexes.

CLUSTER objects USING objects_full_idx;
ANALYZE objects;

This will take a while, since the table is rewritten physically. It is also effectively a VACUUM FULL. It needs an exclusive write lock on the table, so do it at off-hours - provided you can afford that at all. Again, there are less invasive alternatives: pg_repack or pg_squeeze.

You can then drop the index again (if it's unused). It's a one-time effect. I would at least try this once to see how much your queries benefit from it. The effect deteriorates with subsequent write operations. You could repeat this procedure at off hours if you see a substantial effect.

If your table receives a lot of write operations, you have to weigh cost and benefit for this step. For many UPDATEs consider setting a FILLFACTOR lower than 100. Do that before you CLUSTER.

Query

SELECT count(*)
FROM   objects
WHERE  id BETWEEN 0 AND 999999  -- match conditions of partial index!
AND    updated_at > '2012-10-01 0:0'::timestamp
AND    updated_at::date > (now()::date - 7);

More

A more advanced technique for index partitioning:

• Can spatial index help a "range - order by - limit" query

Among other things it provides example code for automatic index (re-)creation.

Make sure that autovacuum is running properly. The huge gain by CLUSTER you have reported may be due in part to the implicit vacuuming that you get from CLUSTER. Maybe this is set up by Heroku automatically, not sure.
The settings in your question look good. So that's probably not an issue here and CLUSTER really was that effective.

Declarative partitioning

has finally matured in Postgres 12. I would consider using that now instead of manual index partitioning (or at least additionally). Range partitioning with updated_at as the partition key. There are also multiple improvements to general performance, big data and B-tree index performance in particular.

Don’t worry, be happy

how does postgres perform the index on the timestamp - in other words will it index the timestamp based on the date/ time, or will it go into the seconds/ milliseconds, etc?

The internals of the indexing scheme used by Postgres should generally be transparent to you, of no regard. And keep in mind that the implementation you study today may change in a future version of Postgres.

You are likely falling into the trap of premature optimization. Trust Postgres and its default behaviors until you know you have a demonstrable performance problem.

Moments

Date-time handling is more complicated than you might understand.

Firstly, you are using TIMESTAMP which is actually the abbreviated name for TIMESTAMP WITHOUT TIME ZONE. This type cannot represent a moment. This type stores only a date and a time-of-day. For example, January 23 2020 at 12:00 noon. But does that mean noon in Tokyo Japan? Or noon in Paris France, several hours later? Or noon in Toledo Ohio US, several more hours later?

I suggest always expanding the type name out fully to be very clear in your SQL. Use TIMESTAMP WITHOUT TIME ZONE rather than TIMESTAMP.

But if you are actually trying to represent moments, a specific point on the timeline, you must use the TIMESTAMP WITH TIME ZONE. This name comes from the SQL Standard. But in Postgres, and some other databases, it is a bit of a misnomer. Postgres does not actually store the time zone. Instead, Postgres uses any time zone or offset-from-UTC information submitted along with an input to adjust into UTC. The value written to storage is always in UTC. If you care about the original zone name or offset numbers (hours-minutes-seconds), then you need to store that in a second column.

When retrieved from the database, the value comes out in UTC as well. But be aware that some middleware tools insist on applying a default time zone the value after retrieval. While well-intentioned, this anti-feature can cause much confusion. You will have no such confusion when using java.time objects as shown below.

Span-of-time queries

Postgres is storing a moment in UTC, likely as a count from an epoch-reference date-time given that the data type is documented as being an integer of 64 bits (8 octets). According to Wikipedia, Postgres uses an epoch reference of 2000-01-01, presumably the first moment of that date in UTC, 2000-01-01T00:00:00.0Z. We do not have any reason to care about what epoch references is used, but there you go.

The real point is that a date-time value in Postgres is stored simply as a number, a count of microseconds. The timestamp types are not a specific date and a time-of-day as you may be thinking. Your queries certainly may benefit from an index on the timestamp column, but date-oriented (without time-of-day) queries will not benefit specifically. The index is not date-oriented, nor can it be as I will explain next.

Determining a date from a moment requires a time zone. For any given moment, the date varies around the globe by time zone. A few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

To query moments by date, you need to determine the first moment of the day, and the first moment of the following day. Then we use the Half-Open approach to defining a span-of-time where the beginning is inclusive while the ending is exclusive. We search for moments that are equal to or later than the beginning while also being before the ending. Tip: another way of saying "equal to or later than the beginning" is "not before".

You are using Java, so you can make use of the industry-leading java.time classes there.

The java.time classes use a resolution of nanoseconds, finer than the microseconds used in Postgres. So you will have no problem loading Postgres values into Java. However, beware of data loss when going the other direction as nanoseconds will be silently truncated to store only microseconds.

When determining the first moment of the day, do not assume the day starts at 00:00:00.0. Some dates in some zones start at another time such as 01:00:00.0. Always let java.time determine the first moment of the day.

ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;                          // Or `Africa/Tunis`, `America/Montreal`, etc.
LocalDate today = LocalDate.now( z ) ;
ZonedDateTime zdtStart = today.atStartOfDay( z ) ;              // First moment of the day.
ZonedDateTime zdtStop = today.plusDays( 1 ).atStartOfDay( z ) ; // First moment of the following day.

Write your Half-Open SQL statement. Do not use the SQL command BETWEEN because it is not Half-Open.

String sql = "SELECT * FROM tbl WHERE event !< ? && event < ? ;" ;  // Half-Open query in SQL.

Pass your beginning and ending values to a prepared statement.

Your JDBC driver supporting JDBC 4.2 and later can work with most java.time by using PreparedStatement::setObject & ResultSet::getObject. Oddly, the JDBC spec does not require support for the two most commonly used types: Instant (always in UTC) and ZonedDateTime. These may or may not work your specific driver. The standard does require support for OffsetDateTime, so let's convert to that.

preparedStatement.setObject( 1 , zdtStart.toOffsetDateTime() ) ;
preparedStatement.setObject( 2 , zdtStop.toOffsetDateTime() ) ;

The resulting OffsetDateTime objects passed to the PreparedStatement will carry the offset used by that time zone at that date-time. For debugging, or curiosity, you may want to see those values in UTC. So let's adjust to UTC by extracting a Instant, and then applying an offset of zero hours-minutes-seconds to get a OffsetDateTime carrying an offset of UTC itself.

OffsetDateTime start = zdtStart.toInstant().atOffset( ZoneOffset.UTC ) ;
OffsetDateTime stop = zdtStop.toInstant().atOffset( ZoneOffset.UTC ) ;

Pass to the prepared statement.

preparedStatement.setObject( 1 , start ) ;
preparedStatement.setObject( 2 , stop ) ;

Once these start and stop values arrive at the database server, they will be converted to a number representing a count-from-epoch, a simple integer. Then Postgres performs a simple number comparison. If an index exists on those integer numbers, that index may or may not be utilized as the Postgres query planner sees fit.

If you have relatively small number of rows, and much RAM to cache them, you may not need an index. Perform tests, and use EXPLAIN/ANALYZE to see the real-world performance.

Date column via Java

If you have done the work to prove a performance problem with date-oriented queries, you could add a second column of type DATE. Then index that column, and refer to it explicitly in your date-oriented queries.

When inserting your moment, also include a calculated value for the date as perceived in whatever time zone makes sense to your app. Just be sure to clearly document your intentions, and the specifics of the time zone used in determining the date. Tip: Postgres offers a feature to include a blurb of text as part of your column's definition alongside the column name and its data type.

As a second DATE column is derived from another column, it is by definition redundant and there de-normalized. As a rule, you should consider de-normalizing only as a last resort.

Java code when inserting a value.

String sql = "INSERT INTO tbl ( event , date_tokyo ) VALUES ( ? , ? ) ;" ;

Determine the current moment, and the current moment's date as perceived in time zone Asia/Tokyo.

Instant now = Instant.now() ;  // Always in UTC, no need to specify a time zone here.
OffsetDateTime odt = now.atOffset( ZoneOffset.UTC ) ;  // Convert from `Instant` to `OffsetDateTime` if your JDBC driver does not support `Instant`.
ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = now.atZone( z ) ;
LocalDate localDate = zdt.toLocalDate() ; // Extract the date as seen at this moment by people in the Tokyo time zone.

Pass to your prepared statement.

preparedStatement.setObject( 1 , odt ) ;
preparedStatement.setObject( 2 , localDate ) ;

Now you can make date-oriented queries on the date_tokyo column. Index if need be.

Date column via SQL

Alternatively, you could populate that date_tokyo column automatically within Postgres.

Trigger

You could write a trigger that uses date-time functions built into Postgres to determine the date of that moment as seen in the time zone Asia/Tokyo. The trigger could then write the resulting date value into that second column.

Generated value column

Or, with Postgres 12, you can more simply use the new generated columns feature. This new feature does the same work, but without the bother of defining and attaching a trigger. For discussion of this new feature, see:

• New In PostgreSQL 12: Generated Columns
• Generated columns in PostgreSQL 12 
by Kirk Roybal
• PostgreSQL 12: generated columns 
by Daniel Westermann

In Postgres 12, a column with GENERATED ALWAYS AS (…) STORED has its value physically stored, and can be indexed.

Caveat

Crucial to such date-time work is correct information about the current definitions of time zones. Usually this information comes via the tz data maintained by ICANN/IANA.

Both Java and Postgres contain their own copy of tz data.

Politicians around the world have shown a penchant for redefining their time zones, often with little or no warning. So be sure to keep track of changes to time zones you care about. When you update Java or Postgres, you will likely be getting fresh copy of the tz data. But in some cases you may need to manually update either or both environments (Java & Postgres). And your host OS has a tz data copy as well, fyi.

PostgreSQL could use the index in your second query, but it correctly chooses not to do that because it would be inefficient.

For a query that selects half the rows in the table, a sequential scan is more efficient than an index scan: The index scan would have to visit most of the table blocks anyway, so why bother with random I/O or building a bitmap?

WITH cte AS (
    SELECT
        date1::date,
        rank() OVER (ORDER BY date1)
    FROM generate_series(date '2014-03-01', CURRENT_DATE + interval '1' month, interval '6 month') g (date1)
),
cteall AS (
    SELECT
        all_date::date
    FROM
        generate_series(date '2014-03-01', CURRENT_DATE + interval '1' month, interval ' 1 day') s (all_date)
),
cte3 AS (
    SELECT
        *
    FROM
        cteall c1
        LEFT JOIN cte c2 ON date1 = all_date
),
cte4 AS (
    SELECT
        *,
        count(rank) OVER w AS ct_str
        FROM
            cte3
WINDOW w AS (ORDER BY all_date))
SELECT
    *,
    rank() OVER (PARTITION BY ct_str ORDER BY all_date) AS rank1,
        dense_rank() OVER (ORDER BY all_date) AS dense_rank1
        FROM
            cte4;

Hope it's not intimidating. personally I found cte is a good tool, since it make logic more clearly.
demo
useful link: How to do forward fill as a PL/PGSQL function

If some column don't need, you can simple replace * with the columns you want.

An expression in an index declaration should be enclosed in additional brackets, try:

CREATE INDEX event_creation_time_date_idx ON event ((creation_time::DATE));

You won't be able to create an index for that query, and indeed the query is not very reliable, because it depends on the current setting of the parameter timezone.

You shouldn't be using AST, but your IANA time zone name America/Santo_Domingo and change the query as follows:

WHERE CAST(t.td_date AT TIME ZONE 'America/Santo_Domingo' AS date) =
      CAST(current_timestamp AT TIME ZONE 'America/Santo_Domingo' AS date)

That can be supported by the following index:

CREATE INDEX ON sl01.tickets_details (CAST(td_date AT TIME ZONE 'America/Santo_Domingo' AS date));

Alternatively, you could change the query to

WHERE t.td_date BETWEEN (date_trunc(
                             'day',
                             current_timestamp AT TIME ZONE 'America/Santo_Domingo'
                         ) AT TIME ZONE 'America/Santo_Domingo')
                AND (date_trunc(
                        'day',
                        current_timestamp AT TIME ZONE 'America/Santo_Domingo'
                     ) AT TIME ZONE 'America/Santo_Domingo')
                    + INTERVAL '1 day'

That could be supported by a simple index on sl01.tickets_details(td_date).

Usually database optimizers won't chose to use indexes for open-ended ranges, such a updated_at > somedate.

But, in many cases the datatime column won't exceed "now", so you can preserve the semantic of > somedate by converting the condition to a range by using between like this:

where updated_at between somedate and current_timestamp

A between predicate is much more likely to cause the optimizer to chose to use an index.

Please post if this approach improved your query’s performance.