Re: Slow SQL query (14-15 seconds)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow SQL query (14-15 seconds)
Date: 2008-11-13 13:31:50
Message-ID: 5163.1226583110@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com> writes:
> I'm having a problem with this query (below) that takes betweend 14 and
> 15 seconds to run, which is too long for the end-user.
> I've done a EXPLAIN ANALYZE (below below) but I'm having difficulties to
> see which part of that query is taking so many times.

It's the repeatedly executed EXISTS subplan that's hurting you:

> SubPlan
> -> Nested Loop (cost=35.56..378.16
> rows=2 width=0) (actual time=16.511..16.511 rows=0 loops=818)

16.511 * 818 = 13505.998, so this is all but about 100 msec of the
runtime. Can't tell if there's any easy way to improve it. In
pre-8.4 releases trying to convert the EXISTS into an IN might help.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Bruno Baguette 2008-11-13 14:19:42 Re: Slow SQL query (14-15 seconds)
Previous Message Matthew Wakeling 2008-11-13 13:28:13 Re: Slow SQL query (14-15 seconds)