Skip site navigation (1) Skip section navigation (2)

Re: Slow SQL query (14-15 seconds)

From: Tomasz Myrta <jasiek(at)klaster(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow SQL query (14-15 seconds)
Date: 2008-11-13 13:16:09
Message-ID: gfh9am$2as3$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
Bruno Baguette napisal 13.11.2008 12:02:
> Hello !
> 
> Sorry for the subject, I didn't found a better one ! :-/
> 
> 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.
> 
> If the lines are too long, your mailreader may cut them and make the SQL 
> query and the query plan unreadable, so I've put a copy of them on 
> pastebin.com : <http://pastebin.com/m53ca365>
> 
> Can you give me some tips to see which part of the query is guilty ?

1. Your explain analyze points to a lot of loops in exists clause:

Filter: ((NOT is_deleted) AND (subplan))
16.5msec * 800loops = ~13sec.

Try to replace exists() with in() or inner joins/distinct.

2. Those 3 left joins can be replaced with subselects:
select (select count(*)... ) as societe_nbre_commandes
from societes ...

-- 
Regards,
Tomasz Myrta

In response to

pgsql-performance by date

Next:From: Matthew WakelingDate: 2008-11-13 13:28:13
Subject: Re: Slow SQL query (14-15 seconds)
Previous:From: Merlin MoncureDate: 2008-11-13 13:05:55
Subject: Re: Performance Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group