Table query uncharacteristically slow. Any ideas?

[Mewk]

Hello all out there,

I have 2 tables in a postgresQL database running on an apache server. One table is about 30 columns, mostly ints with a few varchars and one date field. The other table is 3 columns, all text fields. Both tables have about 3500 rows of data.

A query on both tables on an indexed id field is taking, on average, between half a second and 2 seconds on the table with the integer fields, and, on average, about 25ms on the table with text fields (both have the same index).

Can anyone out there explain this? I thought it was text fields that took the time to query, not ints. Why is my predominantly int table taking so much longer to query?

Any help would be greatly appreciated!

-Ben Mattes

 

[SteveBrett]

have you run explain <your query> to see if the indexes are being used ?

what version of postgresql are u using ? select version(); will tell you ...

have you vacuumed the database ?

Steve

 

[rycamor]

What OS are you using?

Apparently there are some settings for shared memory and semaphores that really affect PostgreSQL's performance.

For example, this following post at PHPBuilder:

http://www.phpbuilder.com/annotate/message.php3?id=1007276

explains what to do to get better performance under FreeBSD. I'm sure it is similar for Linux.

You can see the article about PostgreSQL (where the above comment was written) at

http://www.phpbuilder.com/columns/kevin20010314.php3

-------------------------------------------

&quot;Calculus is just the meaningless manipulation of higher symbols&quot;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-unknown F student