4 ms·

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/ind

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
aidos 14y ago
That's a really nice use-case, thanks for detailing it.
rosser 14y ago
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.
- thrownaway2424 14y ago
  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.
  - knightni 14y ago
    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.
- aidos 14y ago
  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)
- dragonwriter 14y ago
nierman 14y ago
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 http://postgis.refractions.net/support/rtree.pdf

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

aidos 14y ago

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

rosser 14y ago

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.

thrownaway2424 14y ago
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.
- knightni 14y ago
  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.
aidos 14y ago
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)
dragonwriter 14y ago

nierman 14y ago

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 http://postgis.refractions.net/support/rtree.pdf