Re: Performance problem with table containing a lot of text (blog)

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