Re: optimal sql

On Wed, 22 Jan 2003, Michael Hostbaek wrote:

> Hi,

I would suggest looking at the problem in three directions:

a) PostgreSQL system wise
b) PostgreSQL sql wise
c) FreeBSD wise.

For a) do all the necessary tuning on PostgreSQL.
With 1GB of Mem, you could set a value of shared_buffers to 100000.
Also check the fsync setting.
Minimising logging may be a good idea.
Read the docs on the site.

For b) do explain analyze to be sure you have the right index
usage, or create indexes where appropriate.
Check the statitistics of your tables, distributions,
counts etc...

For c) check all kern.ipc.shm* and kern.ipc.sem* kernel variables.
(you will need to set some of those in order to get the desired
shared_buffers in a))
Rebuild a custom kernel fitting your needs. Check
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/config-tuning.html
Also do man 7 tuning.
And, check
http://www.freebsd.org/doc/en_US.ISO8859-1/books/developers-handbook/index.html

(Look at DMA access in your kernel CONFIG, consider turning on IDE write
caching, etc....).

Also during your perl script, a good idea is to have

iostat 3 , vmstat 3, running.
This will give you hints of where your system starves.
If for instance your system cache is small, and CPU usage is small and you
have
a lot of IO, then increase shared_buffers, and tune your disks.
(also do man 8 tunefs)

IF you have nearly ~ 100% CPU usage, then the system may look
healthier but your query not.

>
> 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
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr