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
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco