Worth reading. I had no idea you can do conditional indexes. Is that something other dbs support? (looks like mysql doesn't)
... 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.
Just recently, we had a case with a partial index where the predicate was a boolean field, but the indexed field was a timestamp, sorted descending. The planner chose that index when given a query that had an ORDER BY ... DESC on the timestamp, and query times spiked several orders of magnitude. Dropping the index (which was only used by that query) caused it to drop from 2.5s to .7ms.
Yes, indexes are that powerful.
One sneaky way to deal with this is to order by an expression that can't be indexed, like id+0. I've had 4 order of magnitude speed ups when that sort of query tweaking pointed the planner at the right index. (And in that case, a partial index)
It's one thing that I find it frustrating that Postgres (which I otherwise love) doesn't offer - I understand that they don't like optimiser hints, but at least the ability to say 'keep using the current plan until I give you permission to do otherwise' would be invaluable.
[0]: http://www.pgcon.org/2008/schedule/attachments/63_BSDCan2008...
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 :)
spatial indices of the R-tree variety work quite well as long as the dimensionality isn't too high (i.e., your 2D data would be good).
note: the original 1984 paper on r-trees is a good one (although the performance section is a bit weak): http://postgis.refractions.net/support/rtree.pdf
Even better: you can combine functional and conditional indexes.
IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values.
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.
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.
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.
per the spec, subsequent requests return the same content
You can formally reconcile this definition with the mathematical definition as follows: an HTTP request is expected to cause some operation on the server (an operation is a function with the same domain and range). This operation takes the server from one state X to a new state Y. Since HTTP GET is supposed to be idempotent, if you make a GET request to a server in state X, it is free to transition into some state Y != X, but if you reissue the request to the server in state Y, it should remain in state Y.
So basically, an IMMUTABLE function in PostgreSQL is trivially idempotent in terms of operations on the server, in that it can have no effect at all on the state of the database. But as far as mathematical properties of the function go, it does not need to be idempotent.
f(f(x)) = f(x)
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.
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.
The only two I know support them are PostgreSQL and MSSQL.