Re: Performance question (PostgreSQL 7.1.3)

Gabor,

> I have no experiences with big databases. Now I have a database
> that's size
> is appr. 7,6GB (HW/OS: Intel Pentium III 600MHz, 512MB RAM, GNU/Linux
> 2.2.19
> kernel). There is a big table in it with 25318267 tuples today. If I
> make a
> simple query (SELECT x,y,z FROM table WHERE x = something) then it
> takes
> 2m42s to get the results. Is it a good value?
> I've read the documentation and I've checked the kernel parameters
> (sem.h
> and shmparam.h) and the parameter values in postgresql.conf and I
> have no
> idea what could I do to get the results in shorter time period.
> Thanks for any kindness in advance.

Hmmm.. for 25 million records on a single-processor machine, 2 minutes
isn't unreasonable, especially if you have IDE rather than SCSI drives.
Drive access times are your big bottleneck with really large databases.

Things to check:
1. Indexes ... use EXPLAIN to find out how Postgres is finding the data.
2. VACUUM and VACUUM ANALYZE frequency
3. Postgresql.conf parameters: sort_mem, buffers, wal_files (make tem
high!)
4. Use multiple drives or RAID ... having the log files on a seperate
drive from the database makes a huge difference in really large queries,
although mostly for UPDATE and INSERT queries. Also, putting the OS on
a seperate drive doesn't hurt.
5. Postgres verison ... an upgrade always improves the query optimizer.

Also, how large is each tuple? It makes a difference in retrieval.

Finally, I suggest that you read through the PGSQL-SQL list archives.
We discuss performance issues all the time on that list.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco