Date: Thu, 27 Mar 2008 17:45:08 +0100
To: "Jesper Krogh" <jesper@krogh.cc>, pgsql-performance@postgresql.org
Subject: Re: "Slow" query or just "Bad hardware"?
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t8ootiakcigqcu@apollo13.peufeu.com>
In-Reply-To: <17799.195.41.66.226.1206632068.squirrel@mail.jabbernet.dk>
User-Agent: Opera Mail/9.24 (Linux)

Hm, so this table has 10 million entries and it does not fit in 32GB of =
 =

RAM ?
Could you investigate :
- average size of rows in both tables
- a quick description of your table columns especially the average size =
of  =

your TEXT fields, especially the large one(s) like comments etc (don't  =

bother about INTs unless you have like 50 int columns)
- which fields get toasted, which don't, number of accesses to TOASTed  =

fields in this query, could add 1 seek per field per fetched row if  =

they're not cached
- other stuff in your database that is using those gigabytes of RAM ?  =

(indexes which are used often do count)

I would tend to think that you are not going to display 200 kilobytes of=
  =

text on your listing webpage, most likely something like 100 or 200 byte=
s  =

of text from each row, right ? If that is the case, 10M rows * 200 bytes=
 =3D  =

2G to keep cached in RAM, plus overhead, so it should work fast.

You may want to partition your table in two, one which holds the fields =
 =

which are often used in bulk, search, and listings, especially when you =
 =

list 200 rows, and the other table holding the large fields which are on=
ly  =

displayed on the "show details" page.

Note that one (or several) large text field will not kill your  =

performance, postgres will store that offline (TOAST) for you without yo=
u  =

needing to ask, so your main table stays small and well cached. Of cours=
e  =

if you grab that large 10 kB text field 200 times to display the first 8=
0  =

charachers of it followed by "..." in your listing page, then, you're  =

screwed ;) that's one of the things to avoid.

However, if your "comments" field is small enough that PG doesn't want t=
o  =

TOAST it offline (say, 500 bytes), but still represents the bulk of your=
  =

table size (for instance you have just a few INTs beside that that you  =

want to quickly search on) then you may tell postgres to store the large=
  =

fields offline (EXTERNAL, check the docs), and also please enable  =

automatic compression.

If however, you have something like 200 INT columns, or a few dozens of =
 =

small TEXTs, or just way lots of columns, TOAST is no help and in this  =

case you you must fight bloat by identifying which columns of your table=
  =

need to be accessed often (for searches, listing, reporting, etc), and  =

which are not accessed often (ie. details page only, monthly reports,  =

etc). If you are lucky the column in the first group will form a much  =

smaller subset of your gigabytes of data. Then, you partition your table=
  =

in two (vertically), so the small table stays small.

EXAMPLE on a community site :

- members table, huge, search is slow, join to forum tables to get user'=
s  =

name horribly slow because cache is full and it seeks
- push members' profiles and other data that only shows up in the detail=
s  =

page to a second table : main members table much smaller, fits in RAM no=
w,  =

search is fast, joins to members are also fast.

Word to remember : working set ;)