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_searchFROM partno_lookup where
partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and mfg ilike ? limit 1) as key_search, (SELECT
text_descFROM 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 /*