Hacker News Reader

Comment by CoffeeDregs | Hacker News Reader

I think postgres calls them "partial indexes". See 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.

View on HN

I've actually seen cases where a partial index can cause query performance to fall on its face.

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.

Because of irritating query planner outcomes like these, I've always wished postgresql just offered an API where you hand it the query plan. It seems like the bulk of questions from experienced users of postgresql (and all other SQL databases, really) relate to how to express the query to the planner does what you wanted it to do in the first place. Seems easier to just hand it the plan when you know what you want.

Oracle and DB2 both offer ways to provide hints to the database about how you want the query processed, and the ability to store those hints independently of the query - Stored Outlines and Plan Stability being the features in question. This is particularly useful when you can't risk the plan changing in production and screwing you over - in that situation it's preferable to experience a gradual degradation->optimisation cycle personally.

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.

I've seen that on non-partial indexes when doing and order by query that has an index. The query planner sometimes preferentially chooses the ordering index to do an index scan, then filters out all the items that don't matche the query predicate. This is great when you're likely to take the last 10 timestamps, but falls flat when you have to check 100k items.

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)

Interesting. Did it choose a better suited index? If so, could you have created a combination of the two to get optimal results? (I guess at .7ms you wouldn't even bother looking)

Was it "several orders of magnitude", or just the factor of ~3.5 represented by the 2.5s -> 0.7s change; it can't be both.

0.7 milliseconds.

I'm impressed at my ability to misread that. My bad.

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...

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 :)

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

you could try PostGIS's spatial indices for a less manual/static approach.

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

That's a really nice use-case, thanks for detailing it.

Reply on news.ycombinator.com