Hash: SHA1
Tom Lane wrote:
> We've seen complaints about toast fetch time before. I don't think
> there's any really simple solution. You could experiment with disabling
> compression (SET STORAGE external) but I'd bet on that being a net loss
> unless the data is only poorly compressible.
I am trying it with External and then I'll try it with Plain.
However, this is a hugely long process.
After the alter table, then I have to update each row with an
update dbmail_messageblks set messageblk=messageblk;
so that it uses the new storage.
After that I have to vacuum analyze (which is taking over an hour so
far, most of it on toast).
After this is complete, I'll test the examples again and go through the
process using Plain.
> If the table is not updated very often, it's possible that doing a
> CLUSTER every so often would help. I'm not 100% sure but I think that
> would result in the toast table being rewritten in the same order as the
> newly-built main table, which ought to cut down on the cost of fetching.
This is an email table, it is never updated, but constant inserts.
Deletes only happen once a week. I'll try the cluster after I try the
storage changes.
> Also, what database encoding are you using? I note from the CVS logs
> that some post-8.2 work was done to make LIKE faster in multibyte
> encodings. (Though if you were doing the LIKE directly in bytea, that
> wouldn't matter ... what was the reason for the encode() call again?)
>
We are using UTF-8, and I am testing SQL-ASCII at the moment. DBMail is
a pre-built application, so until I am ready to start playing with its
internals I don't really have a choice about a number of its features.
The reason for the bytea is because of the multiple encodings, I have
suggested using SQL-ASCII to them and then it will be possible to use a
text datatype.
I don't know the reason for using the encode, I assumed that it was
because bytea wouldn't take a LIKE, but I see that I was mistaken. It
could be that in an earlier release LIKE was not supported against
bytea, but I don't know that for sure.
Thanks for your help
Sim
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkiZpDoACgkQjDX6szCBa+pJVACfbkAQuvsOqCCFdlMzpC1rx5yp
KpAAoIV17U+gKjXcDYhlOjRIE1PHUbaK
=A+Ru
-----END PGP SIGNATURE-----
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
No comments:
Post a Comment