From: Josh Berkus <josh@agliodbs.com>
To: Dirk Lutzebaeck <lutzeb@aeccom.com>
To: pgsql-sql@postgresql.org
Subject: Re: Can somebody help me to optimize this huge query?
Date: Mon, 24 Jun 2002 13:30:59 -0700
Message-ID: <200206241330.59012.josh@agliodbs.com> (raw)
In-Reply-To: <15635.36218.13754.670171@cayambe.core.aeccom.com>
References: <15635.36218.13754.670171@cayambe.core.aeccom.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=124999684 and
> t_pgr.docspec=124999684 and
> t_sol.docspec=124999684 and
> t_sta.docspec=124999684 and
<etc ...>
Well, from the look of things, you have no problem with indexing ... the
planner is using your indexes for everything. How long is it taking to
return a response?
All of those nested loops do give me the impression that you *might* be able
to improve performance by forcing the planner using explicit joins and even
subselects. This is quite an art form; I can't really give you specifics on
it, but the idea is to use your knowledge of the database to reduce the size
of each hash join before it's formed. The basic approach is to join tables
small to large order.
However, with an average 3-column primary key, I'm not certain that this is
practical. I'm also not certain that the query planner is your bottleneck;
that EXPLAIN plan looks pretty good to me.
Also, have a look at your computer's memory, disk i/o and swap memory
activity. If your machine is being forced to use the swap for query
storage, that's going to slow you down a lot. A good utility for this on
linux is memstat. Then you can play with postgres' sort_mem and buffer
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
using IDE drives, you'll need a hardware upgrade -- like a UW SCSI RAID
array. DIsk I/O has a *big* impact on database efficiency.
--
-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
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-sql@postgresql.org
Cc: josh@agliodbs.com, lutzeb@aeccom.com
Subject: Re: Can somebody help me to optimize this huge query?
In-Reply-To: <200206241330.59012.josh@agliodbs.com>
* 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