On Fri, 16 May 2008, Scott Marlowe wrote:
Just for giggles, try running the query like so: set enable_nestloop = off; explain analyze ... and see what happens. I'm guessing that the nested loops are bad choices here.
You guess correctly, sir! Doing so shaves 3 orders of magnitude off the runtime. That's nice. :) But that brings up the question of why postgres thinks nested loops are the way to go? It would be handy if I could make it guess correctly to begin with and didn't have to turn nested loops off each time I run this.
Table "public.event" Column | Type | Modifiers ----------------+-----------------------------+------------------------ clientkey | character(30) | not null premiseskey | character(30) | not null eventkey | character(30) | not null severitykey | character(30) |Do these really need to be character and not varchar? varchar / text are better optimized in pgsql, and character often need to be cast anyway, so you might as well start with varchar. Unless you REALLY need padding in your db, avoid char(x).
Unfortuantely, the people who created this database made all keys 30 character strings, and we're not near a place in our release cycle where we can fix that.
