From: Tomasz Myrta <jasiek@klaster.net>
To: pgsql-performance@postgresql.org
Subject: Re: Slow SQL query (14-15 seconds)
Date: Thu, 13 Nov 2008 14:16:09 +0100
Message-ID: <gfh9am$2as3$1@news.hub.org> (raw)
In-Reply-To: <491C0962.9060405@gmail.com>
References: <491C0962.9060405@gmail.com>
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
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-performance@postgresql.org
Cc: jasiek@klaster.net
Subject: Re: Slow SQL query (14-15 seconds)
In-Reply-To: <gfh9am$2as3$1@news.hub.org>
* 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