Обсуждение: Slow response in select
Hi folks,I have a view that seems to be very slow compared to similar views on the same server. I've had a look at running explain but I don't really understand it. I've posted the view and the output from explain at http://www1.ringways.co.uk/explain.txt This is an old view on a database that has been live for some time but I've never managed to get to the bottom of why it's much slower than the other views. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On 09/21/2010 08:25 PM, Gary Stainburn wrote: > Hi folks,I have a view that seems to be very slow compared to similar views on > the same server. > > I've had a look at running explain but I don't really understand it. > > I've posted the view and the output from explain at > > http://www1.ringways.co.uk/explain.txt Woah. That's an interesting plan. When concerned about execution time, it's probably best to post EXPLAIN ANALYZE rather than plain EXPLAIN results. EXPLAIN ANALYZE provides more timing information and information about how rowcount estimates differed from reality. -- Craig Ringer
On Tuesday 21 September 2010 15:11:09 Craig Ringer wrote: > On 09/21/2010 08:25 PM, Gary Stainburn wrote: > > Hi folks,I have a view that seems to be very slow compared to similar > > views on the same server. > > > > I've had a look at running explain but I don't really understand it. > > > > I've posted the view and the output from explain at > > > > http://www1.ringways.co.uk/explain.txt > > Woah. That's an interesting plan. > > When concerned about execution time, it's probably best to post EXPLAIN > ANALYZE rather than plain EXPLAIN results. EXPLAIN ANALYZE provides more > timing information and information about how rowcount estimates differed > from reality. > > -- > Craig Ringer 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? Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
On Tuesday 21 September 2010 15:20:15 Gary Stainburn wrote: > > 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? > I've had a look at this, and I can't even see why the sort is happeing. There are no order by clauses and only one group by clause in the sub-select. This group by only works on a very small dataset though. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
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