From: Michael Hostbaek <mich@the-lab.org>
To: pgsql-sql@postgresql.org
Subject: optimal sql
Date: Wed, 22 Jan 2003 09:54:54 +0100
Message-ID: <20030122085454.GA55124@mich2.itxmarket.com> (raw)
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 /*
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: mich@the-lab.org
Subject: Re: optimal sql
In-Reply-To: <20030122085454.GA55124@mich2.itxmarket.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