pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kari Lavikka <tuner@bdb.fi>
To: pgsql-performance@postgresql.org
Subject: Performance problem with table containing a lot of text (blog)
Date: Tue, 28 Aug 2007 21:53:37 +0300 (EETDST)
Message-ID: <Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi> (raw)

Hello!

Some background info.. We have a blog table that contains about eight 
million blog entries. Average length of an entry is 1200 letters. Because 
each 8k page can accommodate only a few entries, every query that involves 
several entries causes several random seeks to disk.  We are having 
problems with queries like:

1) give me a list of months when I have written someting
2) give me id's of entries I have written on month X year X
3) give me the number of blog entries my friends have written since last
    time

Clustering would probably decrease random seeks but it is not an option. 
It locks the table and operation would take "some" time. It should also be 
done periodically to maintain clustering.

I guess that file system cache gets filled with text contents of blog 
entries although they are totally useless for queries like these. Contents 
of individual blog entries are cached to memcached on application level 
anyway. There's rarely any need to fetch them from database.

It would be nice if I could flag a column to be toasted always, regardless 
of it's length.

Because there isn't such option maybe I should create a separate table for 
blog text content. Does anybody have better ideas for this? :)

Thanks!


P.S. Here's a plan for query #3. Users can have several bookmark groups 
they are following. User can limit visibility of an entry to some of 
his/her bookmark group. Those are not any kind of bottlenecks anyway...

  Sort  (cost=34112.60..34117.94 rows=2138 width=14)
    Sort Key: count(*), upper((u.nick)::text)
    ->  HashAggregate  (cost=33962.28..33994.35 rows=2138 width=14)
          ->  Nested Loop  (cost=8399.95..33946.24 rows=2138 width=14)
                ->  Nested Loop  (cost=8399.95..9133.16 rows=90 width=22)
                      ->  HashAggregate  (cost=8399.95..8402.32 rows=237 width=8)
                            ->  Nested Loop  (cost=0.00..8395.99 rows=792 width=8)
                                  ->  Index Scan using user_bookmark_uid on user_bookmark ub  (cost=0.00..541.39 rows=2368 width=12)
                                        Index Cond: (uid = 256979)
                                  ->  Index Scan using user_bookmark_group_pkey on user_bookmark_group bg  (cost=0.00..3.30 rows=1 width=4)
                                        Index Cond: ("outer".bookmark_group_id = bg.bookmark_group_id)
                                        Filter: (("type" >= 0) AND ("type" <= 1) AND (trace_blog = 'y'::bpchar))
                      ->  Index Scan using users_uid_accepted_only on users u  (cost=0.00..3.06 rows=1 width=14)
                            Index Cond: (u.uid = "outer".marked_uid)
                ->  Index Scan using blog_entry_uid_beid on blog_entry be  (cost=0.00..275.34 rows=24 width=8)
                      Index Cond: ((be.uid = "outer".marked_uid) AND (COALESCE("outer".last_seen_blog_entry_id, 0) < be.blog_entry_id))
                      Filter: ((visibility = 'p'::bpchar) AND ((status = 'p'::bpchar) OR (status = 'l'::bpchar)) AND ((bookmark_group_id IS NULL) OR (subplan)))
                      SubPlan
                        ->  Index Scan using user_bookmark_pkey on user_bookmark fub  (cost=0.00..3.42 rows=1 width=0)
                              Index Cond: ((bookmark_group_id = $0) AND (marked_uid = 256979))

P.S. That particular user has quite many unread entries though...

     |\__/|
     ( oo )    Kari Lavikka - tuner@bdb.fi - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
       ""



view thread (6+ messages)  latest in thread

Message-ID: <Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi>
Permalink:  ../Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi/
Also on:    postgresql.org/message-id/Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tuner@bdb.fi
  Subject: Re: Performance problem with table containing a lot of text (blog)
  In-Reply-To: <Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi>
  References: <Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox