From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: Slow response in select
Date: Tue, 21 Sep 2010 10:52:11 -0400
Message-ID: <2533.1285080731@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, gary.stainburn@ringways.co.uk
Subject: Re: Slow response in select
In-Reply-To: <2533.1285080731@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox