Hi guys,
I am not really new to Postgres, but to be honest I am not an expert
on the internal configuration of Postgres. I run a couple of
PostgreSQL/PostGIS databases on several servers, but two of them are not
performing very well. Those two databases are just read-only databases
and they get dropped and recreated every week.
I have a table like this (incl. a PostGIS geometry column):
col01_id integer
col02 character varying(10)
col03 character varying(100)
col04 double precision
col05 double precision
col06 character varying(80)
col07 character varying(80)
col07 character varying(40)
col08 character varying(6)
col09 character varying(100)
col10 date
col11 date
col12 character varying(30)
col13 character varying(30)
col14 character varying(40)
col15 character varying(10)
col16 character varying(100)
the_geom geometry
About 2400000 rows are in the table, and the size of the table is about
2200MB. Three indexes are created on col01_id, col02 and on the geometry
(clustered).
When performing a 'select col01_id from table limit 1 offset 100000;',
the query takes up to 20sec. Monitoring the dstats on the server, I see
that the box is reading approx. 1GB from the disks.
I ran VACUUM FULL on the table, reindex, analyze and cluster, all sorts
of desperate tries to get a better performance out of this table without
any success.
I copied this table with less columns into a second table and run the
same query on that table which performs much better.
The server is a 2-dual-core cpu server with 2GB ram, running Fedora 5,
Postgres 8.1.9 and PostGIS on top of it. Shared memory settings have
been increased to:
kernel.shmmax=1073741824
kernel.shmall=2097152
and I changed following settings in the postgres.conf:
max_connections = 1000
shared_buffers = 65536
max_fsm_pages = 104000
My co-workes are getting to the point to move back to mySQL. But I like
to stick to PostgreSQL.
Looking forward for any comments or suggestions.
Cheers,
Rainer
