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

On Wed, 29 Aug 2007, Heikki Linnakangas wrote:

> 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.

We are currently using Postgres 8.1 but have to upgrade to 8.2 shortly.
New version fixes some vacuum problems.

I always compile postgres from source. Maybe I have to do some
calculations because that setting affects all tables and databases. Most
of our text/varchar columns are quite short but setting the threshold too
low causes excessive seeks to toast tables... right?

>> 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.

Yeh.

> If the user_bookmark table is not clustered by uid, I'm surprised the
> planner didn't choose a bitmap index scan.

Drumroll... there are:
"user_bookmark_pkey" PRIMARY KEY, btree (bookmark_group_id, marked_uid), tablespace "lun3"
"user_bookmark_marked_uid" btree (marked_uid)
"user_bookmark_uid" btree (uid) CLUSTER, tablespace "lun3"

Queries are mostly like "Gimme all of my bookmarked friends in all of my
bookmark groups" and rarely the opposite "Gimme all users who have
bookmarked me"

I have clustered the table using uid to minimize random page fetches.

- Kari

>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>