CODEWITHSUNDEEP
postgresqlindexingdatabase-performanceunique-constraint
FireLeopard
FireLeopard

Reputation: 317

Best indexes for table in Postgres

Let's say I have the following table

CREATE TABLE tbl
(
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  col_a uuid NOT NULL,
  col_b character varying NOT NULL,
  PRIMARY KEY (id)
);

And I would like it to be unique over (col_a, col_b), so I would also create the following index

CREATE UNIQUE INDEX tbl_unique_idx ON tbl USING btree (col_a, col_b);

So far so good, my question is, given that I want the following two queries to take full advantage of indexes, would I need additional indexes?

SELECT * FROM tbl WHERE col_a=$1 ORDER BY col_b;

SELECT * FROM tbl WHERE col_a=$1 AND col_b=$2;

Basically we will always filter on col_a, can Postges leverage the tbl_unique_idx here? We also always either sort or filter on col_b (in addition to filtering on col_a), so can it leverage the same index or would it require its own index?

The table might reach many millions of records so I'm looking to not have redundant indexes. I'm currently using PostgreSQL 10 but could upgrade to 11 if there's a clear benefit.

Upvotes: 0

Views: 51

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

The index you created will serve your two queries perfectly. It cannot be improved.

I'd create a unique constraint rather that a unique index, though. That way you can also use INSERT ... ON CONFLICT if you need it.

You probably don't want SELECT * in your code. For one, it means that your code might break if you add an additional column, but it also is a performance gain if you only fetch the columns you really need, particularly if some of the data is in the TOAST table.

Upvotes: 1

Related Questions