To: Gary Stainburn <gary.stainburn@ringways.co.uk>
cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: Slow response in select 
In-reply-to: <201009211520.15903.gary.stainburn@ringways.co.uk> 
References: <201009211325.55493.gary.stainburn@ringways.co.uk>
	<4C98BCFD.10202@postnewspapers.com.au>
	<201009211520.15903.gary.stainburn@ringways.co.uk>
Comments: In-reply-to Gary Stainburn <gary.stainburn@ringways.co.uk>
	message dated "Tue, 21 Sep 2010 15:20:15 +0100"
Date: Tue, 21 Sep 2010 10:52:11 -0400
Message-ID: <2533.1285080731@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> I've posted th explain analyze at
> http://www1.ringways.co.uk/explain_analyse.txt

> I've marked a line with a sort in that appears to be the bit that's taking the 
> time.  Am I right?

Well, it's not really that big a part of the whole cost: only 150ms
out of the total.  You could improve the speed of the sort by increasing
work_mem enough to let it be done in-memory; but I'm not sure it's worth
bothering with.  If you knocked 100ms off the runtime that way you'd be
doing well.

One thing to realize about this kind of query is that the planner gets
stupid when there are more than join_collapse_limit relations being
JOINed.  I'm not sure that it matters much in this example: it looks
like it's a star schema and pretty much any join order is as good as any
other.  But you might want to try raising join_collapse_limit just to
see whether the plan changes and whether it gets materially better.
There's a definite planning-time penalty to raising that value, though,
so I'd not recommend changing it in production unless you see big wins
on a lot of queries.  Better to reorder the JOINs manually if it turns
out that join order does matter.

Basically, if you're gonna join that many relations, it's gonna cost ya
:-(.  Star schemas are overrated IMO.

			regards, tom lane