pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow response in select
5+ messages / 3 participants
[nested] [flat]
* Slow response in select
@ 2010-09-21 12:25 Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:11 ` Re: Slow response in select Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2010-09-21 12:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow response in select
2010-09-21 12:25 Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2010-09-21 14:11 ` Craig Ringer <craig@postnewspapers.com.au>
2010-09-21 14:20 ` Re: Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Craig Ringer @ 2010-09-21 14:11 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow response in select
2010-09-21 12:25 Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:11 ` Re: Slow response in select Craig Ringer <craig@postnewspapers.com.au>
@ 2010-09-21 14:20 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:35 ` Re: Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:52 ` Re: Slow response in select Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Gary Stainburn @ 2010-09-21 14:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow response in select
2010-09-21 12:25 Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:11 ` Re: Slow response in select Craig Ringer <craig@postnewspapers.com.au>
2010-09-21 14:20 ` Re: Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2010-09-21 14:35 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Gary Stainburn @ 2010-09-21 14:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Slow response in select
2010-09-21 12:25 Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:11 ` Re: Slow response in select Craig Ringer <craig@postnewspapers.com.au>
2010-09-21 14:20 ` Re: Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2010-09-21 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-09-21 14:52 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-09-21 14:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-21 12:25 Slow response in select Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:11 ` Craig Ringer <craig@postnewspapers.com.au>
2010-09-21 14:20 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:35 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-09-21 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
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