6 ms·

Worth reading. I had no idea you can do conditional indexes. Is that something other dbs support? (looks like mysql doesn't)

• fdr 14y ago
  I believe support for this is fairly common among the usual suspects but is by no means universal, as you point out.
• rosser 14y ago
  You can also do "functional" indexes, where the index predicate is the result of running a function against the indexed column (e.g., UPPER(text_field), or the like). One important caveat: the function must be defined as "immutable" — that is, it returns the same value for a given input whenever it's invoked. Even better: you can combine functional and conditional indexes.
  • meaty 14y ago
    Idempotent, not immutable.
    • rosser 14y ago
      Yes, we call that idempotent, but in PostgreSQL, such functions are called (and must be declared) "immutable".
      • aidos 14y ago
        Actually, the docs explain better, no modification + same result, so it's actually immutable and idempotent. IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values.
        • sirclueless 14y ago
          Idempotent is still the wrong word. Idempotent means that the function can be composed multiple times with no effect. The term I think you are looking for is that it must be a pure function.
          • martinced 14y ago
            eh!? A pure function is an idempotent function which has no side-effects. For example no logging. You can have an impure idempotent function: a function always producing the same output for given input but that is impure because it has side-effects, like logging or sending a heartbeat on the network or whatever. But you cannot have a pure non-idempotent function. Any idempotent function --and hence any pure function-- shall always be giving the same output given some input (and hence can be cached etc.) but the idempotent function can have side-effects (but not side-effects that would modify the "idempotentness" of the function itself.
            • sirclueless 14y ago
              A pure function does not need to be idempotent. For example f(x) = x * 2 is a pure function, but applying it twice multiplies by four instead of two. In computer science, another sense of the word idempotent has arisen, which relates to operations on a mutable identity. In this sense, you might say "UPDATE table SET col1=0 WHERE id=25" is an idempotent operation on databases: applying it to a database in some state results in a new database state, but applying it again to that new database state results in that same database state again (this is the sense in which HTTP GET requests are idempotent, for example). An IMMUTABLE function is a pure function, at least with respect to the database, since it cannot mutate the database which is the side effect we care about. It is not necessarily idempotent: given that f is an IMMUTABLE function, and f(2) == 4, I can't say for sure that f(4) == 4.
          • jpitz 14y ago
            "Idempotent means that the function can be composed multiple times with no effect." Close, but not quite. The first application may have an effect. The second application and so on may not. For example, the HTTP spec defines GET as idempotent. An initial GET request for a URI may have a side-effect, but, per the spec, subsequent requests return the same content and have no further side-effects. Edit - reading the GP, I want to clarify that an immutable function in PostgreSQL is idempotent, since the N+1 applications have no side effect and return the same result as the 1st. Also, the 1st has no side effect.
    • moomin 14y ago
      A function f is idempotent if f(f(x)) = f(x) Which is not what's needed here.
  • dbaupp 14y ago
    (Just for reference/googleability, some other conventional names for such functions are pure or referentially transparent.)
    • saurik 14y ago
      (In this case, "immutable" is not just a property of the function, but the exact keyword you must use while defining the function.)
  • ZoFreX 14y ago
    I learned yesterday that Oracle also supports this. It's a VERY useful feature!
• craigkerstiens 14y ago
  There's a variety of great features similar to this detailed in a couple of Why Postgres posts. The conditional index is one which is mentioned in here - http://www.craigkerstiens.com/2012/05/07/why-postgres-part-2/ http://www.craigkerstiens.com/2012/05/07/why-postgres-part-2...
  • aidos 14y ago
    That's a good run down that I had skimmed through before. I must have missed the conditional index section.
• CoffeeDregs 14y ago
  I think postgres calls them "partial indexes". See http://www.postgresql.org/docs/9.2/static/indexes-partial.html http://www.postgresql.org/docs/9.2/static/indexes-partial.ht... ... and they're great! Postgres is very smart about using them, too, so they can be a lifesaver when you have a large table of which you only [typically] use a small bit. I've created partial indexes over geospatial data. There are 1M doctors in the US, so answering the question "how many doctors are in San Francisco" requires a join between indexes of all doctors in the US and all businesses in SF. Using partial indexes, we carved up the country into a bunch of polygons and were able to frame the question as "how many doctors are in San Francisco in polygon 34?". Much faster.
  • shawn-butler 14y ago
    I wonder how the performance of your partial index would compare to a pgsql materialized view implementation? [0] Did you by chance explore such an option? [0]: http://www.pgcon.org/2008/schedule/attachments/63_BSDCan2008-MaterializedViews.pdf http://www.pgcon.org/2008/schedule/attachments/63_BSDCan2008...
    • DomreiRoam 14y ago
      One stuff to keep in mind is that Partial index, index are internal implementation of the database. If you start using materialized view it is visible to the application. I think you are adding another entity that you must now support and that the application must specifically call. If your application supports multiple databases, i don't think you want to have specific code to support optimization. Of course you need to do this :)
      • shawn-butler 14y ago
        Materialized views are inherent to oracle. They might refer to them as snapshots. In mssql they are known as indexed views. There is no implementation in pgsql but it is often discussed [0]. I gave up on data portability for nontrivial applications a long time ago. If you mean instead a view across multiple databases, it might be possible using pgsql foreign data wrappers but definitely not performant. [0]: http://wiki.postgresql.org/wiki/Materialized_Views http://wiki.postgresql.org/wiki/Materialized_Views
• pjungwir 14y ago
  Getting support for partial indexes from within Rails is one of the reasons I wrote db_leftovers, which lets you manage indexes, foreign keys, and CHECK constraints from a simple DSL: https://github.com/pjungwir/db_leftovers https://github.com/pjungwir/db_leftovers Although db_leftovers works with Postgres and MySQL, partial indexes only work with Postgres, because MySQL doesn't have them.
  • josephlord 14y ago
    Looks very cool. Is it stable and ready for wide use or still a bit experimental? p.s. Definitely agree about not changing migrations after 'git push'. Better to make a new reversing migration to change things back if needed and yet another to get it right.
    • pjungwir 14y ago
      I've used it regularly on a few projects for the last few months or so, but the Postgres code is definitely better exercised than the MySQL code. There are pretty good tests for both though.
• jeltz 14y ago
  Oracle does not support partial indexes but since Oracle does not index NULL values you can emulate it with a functional index. (Only really useful if used for unique indexes, otherwise to kludgey.) I do not think DB2 supports partial indexes either, and I have no idea if they can be emulated. The only two I know support them are PostgreSQL and MSSQL.
  • ZoFreX 14y ago
    Oracle has index sharding though - would that help in the use case of a very large table where typically only a specific, small chunk of it is accessed?
• daigoba66 14y ago
  MSSQL calls them filtered indexes. Best thing since sliced bread.