Support descending index

[cp-i-pc]

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

• I have searched the issues of this repository and believe that this is not a duplicate.

Summary 💡

func (MyTable) Indexes() []ent.Index {
	return []ent.Index{
		index.Fields("c1 DESC", "c2 ASC"),
	}
}

OR

func (MyTable) Indexes() []ent.Index {
        directionalIndex1 := map[string]string{ "c1": "DESC", "c2", "ASC" }
	return []ent.Index{
		index.DirectionalFields(directionalIndex1),
	}
}

Motivation 🔦

I have a table which ID type is not int, but UUID. I'll use ORDER BY time DESC to retrieve latest N records. It says my sorting is Backward index scan when I try to optimize my query using EXPLAIN statement. After doing a little research I realized that the index which created by ent is an ascending index, but what I really needed is descending index. It'll be slower than descending index approximately 15%.

[a8m]

Hey @ColetteContreras and thanks for proposing this.
We should add support for descending indexes. I just added an annotation option for indexes (in #1632), and this should make it simple to support this option.

cc @rotemtam

[a8m]

Thanks for proposing this feature @ColetteContreras, it was resolved with #2304.

You can define descending indexes if you're using the Atlas migration. The API is as follows:

index.Fields("a").
	Annotation(entsql.Desc())
// CREATE INDEX ... ON "table" ("a" DESC)

index.Fields("a", "b", "c").
	Annotation(entsql.DescColumns("a", "b"))
// CREATE INDEX ... ON "table" ("a" DESC, "b" DESC, "c")