From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Kari Lavikka <tuner@bdb.fi>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem with table containing a lot of text (blog)
Date: Wed, 29 Aug 2007 09:29:21 +0100
Message-ID: <46D52E61.8020706@enterprisedb.com> (raw)
In-Reply-To: <Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi>
References: <Pine.HPX.4.62.0708282016250.3324@purple.bdb.fi>
Kari Lavikka wrote:
> It would be nice if I could flag a column to be toasted always,
> regardless of it's length.
The idea of being able to set the toast threshold per column was
discussed during 8.3 development, but no patch was produced IIRC. We
might do that in the future. If you're willing to compile from source,
you can lower TOAST_TUPLE_THRESHOLD.
You could also use ALTER TABLE ... ALTER COLUMN ... SET STORAGE EXTERNAL
to force the long blog entries to be stored in the toast table instead
of compressing them in the main table. Values smaller than
TOAST_TUPLE_THRESHOLD (2k by default?) still wouldn't be toasted,
though, so it might not make much difference.
> Because there isn't such option maybe I should create a separate table
> for blog text content. Does anybody have better ideas for this? :)
That's probably the easiest solution. You can put a view on top of them
to hide it from the application.
> 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...
If the user_bookmark table is not clustered by uid, I'm surprised the
planner didn't choose a bitmap index scan. Which version of PostgreSQL
is this?
PS. EXPLAIN ANALYZE is much more helpful than plain EXPLAIN.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, tuner@bdb.fi
Subject: Re: Performance problem with table containing a lot of text (blog)
In-Reply-To: <46D52E61.8020706@enterprisedb.com>
* 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