Owens
Ecto Postgres Index vs Unique Index
Hello all,
Does anyone know if you create a unique_index in postgres for two fields on a table, does that mean you DON’T need to create an additional index on those two tables?
e.g.
create unique_index(:projects, [:organization_id, :name])
means you DON’T need to do create index(:projects, [:organization_id])
create unique_index(:team_users, [:team_id, :user_id])
means you DON’T need to do
create index(:teams_users, [:team_id])
create index(:teams_users, [:user_id])
AND if you already setup those indexes before the unique_indexes, should you drop/delete those indexes before migrating the unique_indexes?
Marked As Solved
amnu3387
From what I understood from this (Multicolumn indexes pg 9.6 (but same up to 12) the indexes can be used when the query involves columns from the index, going from the left to right.
In this case the second index on organization_id doesn’t give you anything extra since the leftmost column of the unique index you created before, is that same column.
Here you wouldn’t need a team_id index, but you would need a user_id if you are planning on querying the teams_users table solely by user_id (without team_id that is).
The team_id being the leftmost column declared on the unique_index would be used when a query containing a team_id column is made.
Theoretically this applies to indexes with more than 2 columns too.
E.g.
unique index on a, b, c
Means that pg can use the index on a query containing:
a
a,b
and a,b,c
(though it still goes through the planner ofc, so it might not use it, but that wouldn’t be because it can’t, at least this is my reading from the docs, it also means that the order in which you declare the columns for the index plays a role too)
Also Liked
benwilson512
Unique constraints will create a unique index in the background anyway, it’s the only way to enforce the constraint in a performant manner.
Basically, create unique indices to enforce uniqueness rules. Also, create indices to reflect query patterns. If those two reasons to create indices happen to overlap, great! But it isn’t a big deal if they don’t, and they’re both perfectly valid reasons to create an index.
benwilson512
It does not mean that. The index will only be used if you query both of those fields. If you query only one of them, it cannot use that index. If you plan to query the table by those fields individually then you also want indices on those fields.
benwilson512
As far as I can tell there isn’t any practical difference, however I think from a clarity perspective it is better to use constraints to enforce uniqueness. Just know that you don’t need to make an index for it, one will be created already.
kip
With some material limitations, Postgres can use a leading subset of multi-column indexes. From the documentation:
A multicolumn B-tree index can be used with query conditions that involve any subset of the index’s columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on
(a, b, c)and a query conditionWHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry witha= 5 andb= 42 up through the last entry witha= 5. Index entries withc>= 77 would be skipped, but they’d still have to be scanned through. This index could in principle be used for queries that have constraints onband/orcwith no constraint ona— but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.
But as Ben says, not appropriate to apply unique constraints.
benwilson512
Yup so was I, in fact ecto unique constraints rely on postgres constraints, they don’t replace them.
Popular in Questions
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"