
More on Postgres Performance - icey
http://craigkerstiens.com/2013/01/10/more-on-postgres-performance/
======
aidos
Worth reading. I had no idea you can do conditional indexes. Is that something
other dbs support? (looks like mysql doesn't)

~~~
CoffeeDregs
I think postgres calls them "partial indexes". See
[http://www.postgresql.org/docs/9.2/static/indexes-
partial.ht...](http://www.postgresql.org/docs/9.2/static/indexes-partial.html)

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

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

------
CyrusL
Tangent: I met Craig yesterday randomly in a coffee shop. He seemed really
smart and gave me a few Postgres performance tips. I didn't know he was a big
deal. I guess those are the cool twists of fate people talk about happening in
the Bay Area.

~~~
robotmay
I met him a few months ago in Oxford, UK when he was speaking. Really enjoyed
his talk and he was very approachable in the bar afterwards.

------
iracic
I thought ratio based tuning is old-fashioned.

------
astro1138
tl;dr

Use EXPLAIN ANALYZE and read the manual.

