Re: Performace question - Mailing list pgsql-general

>Firstly, have you run ANALYZE across the database?
ANALYZE not, only EXPLAIN. And it looks pretty normal :( There is basically
nothing interested in soo simple SQL.

>Secondly, the queries that are varying so much, can you post the EXPLAIN
>ANALYZE output so we can see what is actually going on.
Thx, ANALYZE was good idea. Here it comes - right now, there is index on
'cluster' (BTREE) @ dtditems. But it was not used - I guess because reading
& seraching will cost more than pure seq scan on 'few' items...

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

QUERY PLAN Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84)
Filter: ("cluster" = 42)

QUERY PLAN Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84)
(actual time=440.10..472.00 rows=113 loops=1)    Filter: ("cluster" = 42)
Total runtime: 519.86 msec

Current 'top' output (while running test)

load averages:  1.31,  1.11,  0.78
50 processes:  1 running, 47 idle, 2 stopped
CPU states:  1.4% user,  0.0% nice,  2.2% system,  0.2% interrupt, 96.3%
idle

There also more than 200M free memory.

Just to compare - the same SQL executed by Ms Access database (uch, it hurt
to type that name !) takes ~12 ms. Also MySql takes similar time...

There is no differences between variations on WHERE - like using = instead
of IN etc.

>Note also that the query log can be very helpful in finding out if the
delay
>is in the database or not.
I added to email log from PgSql (hope it arrives well last time, coz I am
sitting @ outlook@nt4 :) and there you can see, that it really takes 500 ms
to select 100 records from ~500 rows table... Let me know, if log was
damaged. But the time coresponds....

What can I do (or where is some document regarding this topic ?) speed up
PgSql ? I really think, half second for selecting ~100 rows from ~600 rows
table it pretty slow. Commodore 64 (1 mHz 6510) will do it faster :)

Any hints ?
Thanks,
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.