Skip site navigation (1) Skip section navigation (2)

Re: "Slow" query or just "Bad hardware"?

From: PFC <lists(at)peufeu(dot)com>
To: "Jesper Krogh" <jesper(at)krogh(dot)cc>, pgsql-performance(at)postgresql(dot)org
Subject: Re: "Slow" query or just "Bad hardware"?
Date: 2008-03-27 16:45:08
Message-ID: op.t8ootiakcigqcu@apollo13.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 bytes  
of text from each row, right ? If that is the case, 10M rows * 200 bytes =  
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 only  
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 you  
needing to ask, so your main table stays small and well cached. Of course  
if you grab that large 10 kB text field 200 times to display the first 80  
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 to  
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 details  
page to a second table : main members table much smaller, fits in RAM now,  
search is fast, joins to members are also fast.

Word to remember : working set ;)


In response to

pgsql-performance by date

Next:From: PFCDate: 2008-03-27 16:57:06
Subject: Re: "Slow" query or just "Bad hardware"?
Previous:From: MatthewDate: 2008-03-27 16:06:03
Subject: Re: "Slow" query or just "Bad hardware"?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group