Content-Type: text/plain; charset="utf-8"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
From: "Mindaugas" <ml@kilimas.com>
To: pgsql-performance@postgresql.org
Subject: Dealing with big tables
Date: Sun, 02 Dec 2007 12:26:17 +0200 (EET)
Message-Id: <E1Iym1h-0004Tg-VQ@garm.runbox.com>

  Hello,

  Started to work with big tables (like 300GB) and performance problems sta=
rted to appear. :(

  To simplify things - table has an index on From an index on To columns. A=
nd it also have several other not indexed columns. There are 100000+ of dif=
ferent values for From and the same for To.

  I execute simple query "select * from bigtable where From=3D'something'".=
 Query returns like 1000 rows and takes 5++ seconds to complete. As far as =
I understand the query is slow because:
  - first it has to retrieve pointers to rows with data from index. That go=
es fast.
  - retrieve all the rows one by one. There we have 100% random read becaus=
e rows with the same From is distributed evenly through all the 300GB and m=
ost probably nothing is cached. So there we are limited by _one_ disk perfo=
rmance independently of how many disks we have in storage? And in case stor=
age consists of 15k rpm Cheetahs with 3.5ms average read seek time we shoul=
d expect not more than ~285 rows per second?

  I feel that I'm overlooking something here. But I'm new into data warehou=
sing. :)

  Also this query should greatly benefit from parallel execution or async I=
O. Storage (seeks/second) scales almost linearly when it has a lot of disks=
. And query is completely IO bound so it should scale well on single server.

  And I cannot use some index organized table or table partitioned by From =
:) because there are at least 2 similar indexes by which queries can be exe=
cuted - From and To.

  Ideas for improvement? Greenplum or EnterpriseDB? Or I forgot something f=
rom PostgreSQL features.

  Thanks,

  Mindaugas