6 ms·
More on Postgres Performance
- phantomcircuit 14y ago220ms to filter 14 rows? huh
- CyrusL 14y agoTangent: 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 14y agoI 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.
- aidos 14y agoWorth reading. I had no idea you can do conditional indexes. Is that something other dbs support? (looks like mysql doesn't)
- fdr 14y agoI believe support for this is fairly common among the usual suspects but is by no means universal, as you point out.
- rosser 14y agoYou 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 agoIdempotent, not immutable.
- rosser 14y agoYes, we call that idempotent, but in PostgreSQL, such functions are called (and must be declared) "immutable".
- aidos 14y agoActually, 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 agoIdempotent 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 agoeh!? 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
- craigkerstiens 14y agoThere'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 agoThat's a good run down that I had skimmed through before. I must have missed the conditional index section.
- CoffeeDregs 14y agoI 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 agoI 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 agoOne 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 agoMaterialized 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 agoGetting 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 agoLooks 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 agoI'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 agoOracle 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 agoOracle 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 agoMSSQL calls them filtered indexes. Best thing since sliced bread.
- astro1138 14y agotl;dr Use EXPLAIN ANALYZE and read the manual.
- iracic 14y agoI thought ratio based tuning is old-fashioned.