Mailbox-Line: From josh@agliodbs.com  Mon Jun 24 19:04:13 2002
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Dirk Lutzebaeck <lutzeb@aeccom.com>, pgsql-sql@postgresql.org
Subject: Re: Can somebody help me to optimize this huge query?
Date: Mon, 24 Jun 2002 13:30:59 -0700
References: <15635.36218.13754.670171@cayambe.core.aeccom.com>
In-Reply-To: <15635.36218.13754.670171@cayambe.core.aeccom.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200206241330.59012.josh@agliodbs.com>


Dirk,

> SELECT DISTINCT
>   t_sek.docindex,
>   t_sek.envelope,
>   bt.oid,
>   bt.time
> FROM
>   boxinfo bt, boxinfo bd, boxinfo bo,
>   docobj t_sek, docobj t_pgr, docobj t_sta, docobj t_sol,
>   docobj d_pnr, docobj d_sta,
>   docobj o_sek, docobj o_pgr, docobj o_pnr
> WHERE
>   t_sek.docspec=3D124999684 and
>   t_pgr.docspec=3D124999684 and
>   t_sol.docspec=3D124999684 and
>   t_sta.docspec=3D124999684 and
<etc ...>

Well, from the look of things, you have no problem with indexing ... the=20
planner is using your indexes for everything.   How long is it taking to=20
return a response?

All of those nested loops do give me the impression that you *might* be abl=
e=20
to improve performance by forcing the planner using explicit joins and even=
=20
subselects.  This is quite an art form; I can't really give you specifics o=
n=20
it, but the idea is to use your knowledge of the database to reduce the siz=
e=20
of each hash join before it's formed.  The basic approach is to join tables=
=20
small to large order.=20

However, with an average 3-column primary key, I'm not certain that this is=
=20
practical.  I'm also not certain that the query planner is your bottleneck;=
=20
that EXPLAIN plan looks pretty good to me.

Also, have a look at your computer's memory, disk i/o and swap memory=20
activity.   If your machine is being forced to use the swap for query=20
storage, that's going to slow you down a lot.  A good utility for this on=
=20
linux is memstat.   Then you can play with postgres' sort_mem and buffer=20
settings to try to make the whole thing happen in RAM.

FInally, how big are these tables?  If we're talking 200mb of data, and you=
're=20
using IDE drives, you'll need a hardware upgrade -- like a UW SCSI RAID=20
array.  DIsk I/O has a *big* impact on database efficiency.

--=20
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology 	josh@agliodbs.com
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco