Skip to main content
added 1 character in body
Source Link
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

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.

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 byte 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.

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 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:

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.

added 328 characters in body
Source Link
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

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 byte 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.

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 byte 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:

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.

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 byte 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.

Source Link
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

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 byte 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:

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.