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:
Also, I would rearrange table columns to save a couple of bytebytes 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:
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:
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.