Speeding Up PostgreSQL with Partial Indexes

[infogulch]

What about one multiple-column index? Using three separate indexes on three columns is almost never ideal so I don't know why it was considered in the first place (unless to make a contrived "normal" to compare partial indexes against).

My guess would be: nearly the same read performance as the partial index, a bit less than half the storage of three separate indexes. The write performance isn't as "free" as the partial index, but it's a third of three separate indexes and still allows for many more types of queries than the partial index.

[drob]

Separate single-column indexes can be reused more readily. In this scenario, you might have a few different types of "high value" events, and you'd want your indexes to be applicable to more than one of them.

A multi-column index on (a, b, c) can't be used for a query that filters on b and c but not a. More detailed documentation here: http://www.postgresql.org/docs/9.3/static/indexes-multicolum...

[jasonl99]

How about a multiple column AND partial index? (that may have been what you are suggesting). Best of both worlds? I just tried it, and it does work (at least on 9.4). Here's an extremely contrived example with a rails 'users' table

create index test_index on users (name, email) where name > 'A' and email > 'B'

[chdir]

In case of Django, the RunSQL command [1] in migrations makes it very easy to use partial indexes. There are a couple of examples on Stackoverflow. Definitely a low hanging fruit and worth those 10-20 minutes to setup.

[1] https://docs.djangoproject.com/en/dev/ref/migration-operatio...

[dsugarman]

this is a really cool feature I did not know about. just curious, why do you use such crazy json in postgres? why not store the data with relational database standards?

[mcorrand]

DISCLAIMER: I'm not a strong dba, but I'll try my hand at an answer. Please let me know if I'm talking nonsense.

It would be useful if the data meets two requirements:

-you know that it won't be queried in a relational way, but

-you don't know how the schema might evolve (beyond the fields you use in your partial index, that is) and want to save yourself the hassle of frequent schema migrations.

[ikawe]

I've found it handy for storing configuration data.

E.g. user preferences

    preferences = { 
      "notifications": {
        "email": true, 
        "sms": false 
      },
      "stay_signed_in": true,
      "editor_plugin": {
        "indent_level": 2,
        "autosave": false
      }
    }

It's structured, adhoc, and "less likely" to be queried against. Of course you could implement this in normalized tables, but it's a pain, and I don't see any real value in this case.

[drob]

In this example, a relational schema would work fine, and might have been easier to read. We use HSTORE at heap (until JSONB ships!) because we're processing event blobs with thousands of different fields, of which most are irrelevant for most events.

The added flexibility benefit is also nice. The ability to add a new property without migrating a schema has saved a lot of work.

[arthursilva]

type is probably an excellent candidate to get into the schema

[losvedir]

This is a cool feature.

Unfortunately, when I looked into this for our codebase, I found that it doesn't have full support in Rails 3. You can make the index, via a migration, but rails won't include it properly in your `schema.rb` file. So restoring via schema (as opposed to running through all your migrations from scratch) or just trying to understand your database by looking at schema.rb won't give you a full picture.

However, looking at `add_index` in rails 4, it seems to support it!

edit: one thing I did try out was you can change your schema file from `schema.rb` to `schema.sql`, which supposedly fixes it, but I had some issues with that which I don't remember at the moment.

[sanderjd]

There's a simple solution to this:

    config.active_record.schema_format = :sql

In my opinion, this should be the default. I'm really not sure what advantage the schema DSL in schema.rb has over the ability to express the full schema in its native tongue.

Edit: Just saw your edit. This[0] may have been the problem you had with using :sql as the schema format. The counter-intuitive workaround is to output ruby in production. It seems weird, but doesn't hurt anything.

[0]: http://stackoverflow.com/questions/17300341/migrate-not-work...

[mcorrand]

The problem of syncing the schema to the orm is a tough one to solve indeed. Good to see that rails will get it right.

Django with South has the same issue, so much so that South preventatively overrides the native manage.py syncdb command with its own migrate command which replays all the migrations.

[pjungwir]

Here is a gem I wrote that uses a separate DSL to track indexes (including partial indexes), check constraints, and foreign keys. Rails 4 is starting to make this gem obsolete, but it still offers more than you get from vanilla Rails.

https://github.com/pjungwir/db_leftovers

[drob]

I wonder if this has something to do with differential support from different DBs. I don't think MySQL has an equivalent feature, though I think SQLite does.