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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
