Date: Wed, 22 Jan 2003 09:54:54 +0100
From: Michael Hostbaek <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: optimal sql
Message-ID: <20030122085454.GA55124@mich2.itxmarket.com>
Mail-Followup-To: Michael Hostbaek <mich@the-lab.org>,
 pgsql-sql@postgresql.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.3i

Hi,

I am running postgresql 7.2.3 on a test server (with potential of
becoming my production server).

On the server I have a perl script, that is grabbing some data from a
inventory database (local) - with some subselects.
The query is like this:

<query>
my $sth = $ppdb->prepare("
    select partno, create_date, mfg, condition, gescode, qty,
cmup,(SELECT partno_main FROM partno_lookup where 
    partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and mfg
ilike ? limit 1) 
    as partno_main, (SELECT subcat FROM partno_lookup where
partno_lookup.partno_alias ilike 
    (?|| inventory.partno ||?) and mfg ilike ? limit 1) as subcat, 
    (SELECT key_search FROM partno_lookup where
partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and 
     mfg ilike ? limit 1) as key_search,
    (SELECT text_desc FROM descriptions where
descriptions.partno=(SELECT partno_main FROM partno_lookup 
    where partno_lookup.partno_alias ilike (?|| inventory.partno ||?)
and mfg ilike ? limit 1) 
     limit 1) as descri from inventory where mfg ilike ? and ? <
create_date $refurbed order by key_search,
    subcat, partno_main, status DESC ");
</query>

It takes quite a while for the query to get processed - and the script
to return my values.
The inventory table has approx. 23000 records - and the partno_lookup
has approx. 1100.

Is there anyway I can optimize the sql - og perhaps optimize my
postgresql db settings ? ( I am running my postgresql on FreeBSD, on a
fairly adequite machine with 1GB RAM)

I look forward to any pointers or hints you might have.

Thanks.

/mich

-- 
Best Regards,
	Michael Landin Hostbaek 
	FreeBSDCluster.org - an International Community

	*/ PGP-key available upon request /*