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 /*
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
