New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support partial indexing #883

Closed
ricn opened this Issue Aug 15, 2015 · 6 comments

Comments

Projects
None yet
3 participants
@ricn

ricn commented Aug 15, 2015

In ActiveRecord you can do this:

add_index :orders, :billed, where: "billed = false"

It would be nice if we could do it like this in Ecto:

create index(:orders, [:billed], where: "billed = false")

The only way I have figured out to do this in Ecto is to use the execute function:

execute("CREATE INDEX index_orders_on_billed_idx ON orders(billed) WHERE billed = false")
@josevalim

This comment has been minimized.

Show comment
Hide comment
@josevalim

josevalim Aug 15, 2015

Member

We are feature frozen for Ecto 1.0 but we could add this after v1.0 is out. The main question is: how do these options work between databases? It would be nice if we could use Ecto's query syntax here. But we probably won't be able to.

Member

josevalim commented Aug 15, 2015

We are feature frozen for Ecto 1.0 but we could add this after v1.0 is out. The main question is: how do these options work between databases? It would be nice if we could use Ecto's query syntax here. But we probably won't be able to.

@ricn

This comment has been minimized.

Show comment
Hide comment
@ricn

ricn Aug 15, 2015

Sqlite seems to support the same syntax that PG uses. Not sure about the other ones but I can look it up.

ricn commented Aug 15, 2015

Sqlite seems to support the same syntax that PG uses. Not sure about the other ones but I can look it up.

@ricn

This comment has been minimized.

Show comment
Hide comment
@ricn

ricn Aug 17, 2015

Postgres, Sqlite & MSSQL have support for partial indexes (sometimes referred to as filtered indexes) & they all support it by appending a WHERE clause to the CREATE INDEX statement.

MySQL does not seem to support partial indexes.

ricn commented Aug 17, 2015

Postgres, Sqlite & MSSQL have support for partial indexes (sometimes referred to as filtered indexes) & they all support it by appending a WHERE clause to the CREATE INDEX statement.

MySQL does not seem to support partial indexes.

@matthewrudy

This comment has been minimized.

Show comment
Hide comment
@matthewrudy

matthewrudy Nov 13, 2015

Contributor

I had a go at this.
master...matthewrudy:create-partial-indexes

Currently just send a string into the WHERE clause

def change do
  create index(:posts, [:permalink], unique: true, where: "public IS TRUE")
end

I haven't tried using ecto query syntax yet, but the Postgres implementation of partial indexes supports full query syntax, so it should be useful.
http://www.postgresql.org/docs/8.0/static/indexes-partial.html

Any suggestions for how to try to use the query syntax would be welcome
otherwise I'll write some documentation for this current implementation

Contributor

matthewrudy commented Nov 13, 2015

I had a go at this.
master...matthewrudy:create-partial-indexes

Currently just send a string into the WHERE clause

def change do
  create index(:posts, [:permalink], unique: true, where: "public IS TRUE")
end

I haven't tried using ecto query syntax yet, but the Postgres implementation of partial indexes supports full query syntax, so it should be useful.
http://www.postgresql.org/docs/8.0/static/indexes-partial.html

Any suggestions for how to try to use the query syntax would be welcome
otherwise I'll write some documentation for this current implementation

@josevalim

This comment has been minimized.

Show comment
Hide comment
@josevalim

josevalim Dec 15, 2015

Member

A PR is welcome. Let's just support passing a string for now.

Member

josevalim commented Dec 15, 2015

A PR is welcome. Let's just support passing a string for now.

@matthewrudy

This comment has been minimized.

Show comment
Hide comment
Contributor

matthewrudy commented Dec 18, 2015

@josevalim josevalim closed this Dec 18, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment