performance on selecting a row in large tables

Поиск
Список
Период
Сортировка
От Rainer Spittel
Тема performance on selecting a row in large tables
Дата
Msg-id 12B8F225666F99489CEFA8B3F261540CFAAE10@geneva.local.terralink
обсуждение исходный текст
Ответы Re: performance on selecting a row in large tables  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: performance on selecting a row in large tables  (Tino Schwarze <postgresql@tisc.de>)
Список pgsql-admin
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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: "create implicit sequence" crashes postgres
Следующее
От: Tom Lane
Дата:
Сообщение: Re: performance on selecting a row in large tables