Performace question

Dear list,

First of all I want to say sory, if my question was answered somewhere.
Or if it is my fault. If so, please, give me link/hint. My own search fails
:( I have experimence with MySql, MsSql and Oracle (and MS Access huch :) I
am new to PgSql.

We are running server - OpenBSD 2.9, latest apache, latest PHP, latest
PgSql, latest mod_ssl. No other related packages are used. All programs are
compiled 'normal' way, including PgSql - except unicode support. There are
no special runtime conditions. Server is Intel P3 800/intel MB, 512M Ram,
plenty HDD, etc. We have web application, thin client type (mozzila/ie).
Users are connecting throw HTTPS to Apache, where runs PHP scritps talking
by TCP/IP to local Postgres DB (native PgSql support in PHP). PgSql uses
default settings from source package. System is huge, many tables, but
nothing special. I hope it is enough info about this. Postmaster options: -h
127.0.0.1 -i -p 5432, postgresql.conf is 'empty' (no default value override
used)...

Let's say:

SELECT
id,parent,alias,aliasfull,name,comment,type,typeflags,flags,cluster,viewprio
r FROM dtditems WHERE cluster IN (42)

(ohhh, what a complicated example :)

My problem is performance. Sometimes SQL statement takes ~20 ms,
sometimes (the same) takes 200ms, sometimes 2.000 ms (!). I am sure it is
not because of CPU/memory (both plenty avilable at the moment). Average is
~600ms. 'dtditems' is table, without _any_ foreign key/indexes, etc. It have
592 rows. 'cluster' is integer. Don't tell me to create indexes, or foreign
keys. I know they helps, but for table with ~600 rows, where ~15% is
selected, difference will be small, not 10x.

This SQL statements takes 982ms, after few 'refresh' it takes 604ms,
after another few refresh 56, and after another few 12480ms. I didn't find
any conditions why these times are soo different. My experimence says, that
this kind of SQL and row count can be done at given CPU somewhat around
~5-10ms. Above statement hits 113 rows.

When I run the system connected to another DB, this problem doesn't
arise. I think (90% :) the problem is somewhere in PgSql (maybe related to
system).

Initially, _ALL_ SQL's takes hundreds of ms. I created ONE index (for
testing) and the problem was over. But was over for ALL tables. Seems PgSql
have some trouble with indexes.

I allready tried create indexes (for table in above SQL statement), but
the problem wasn't solved. So, I remove indexes again.

Please, anyone can give me hint where I should try to find what causes
the problem ? Why is PgSql (or it is PHP/Apache bug ?) soooo slow ? And
mainly, WHY there is 10x difference between executing the same SQL statement
? Is there any way, how to 100% say if the problem is @ php or pgsql ?

I understand because of multitasking OS we can't measure 'exactly', so,
if one requst will be 10ms, next 12,8,14,20,12,40,34,... it will be fine.
But something like 50, 500, 2000 makes me crazy....

I have added 'log' - part of pgsql log coresponding to above SQL.

Feel free to have any Q about sysconfig/programs config.

And sorry again if I miss something.

Thank you !

Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net

--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.