Re: VACUUM FULL versus TOAST

On Sun, Aug 14, 2011 at 7:43 PM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Sun, Aug 14, 2011 at 5:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There would be some merit in your suggestion if we knew that all/most
>> toasted columns would actually get fetched out of the catcache entry
>> at some point.  Then we'd only be moving the cost around, and might even
>> save something on repeated accesses.  But I don't think we know that.
>> In the specific example at hand (pg_statistic entries) it's entirely
>> plausible that the planner would only need the histogram, or only need
>> the MCV list, depending on the sorts of queries it was coping with.
>
> Fwiw detoasting statistics entries sounds like a fine idea to me. I've
> often seen queries that are unexpectedly slow to plan and chalked it
> up to statistics entries getting toasted. If it's ok to read either
> the histogram or MVC list from disk every time we plan a query then
> why are we bothering with an in-memory cache of the statistics at all?
>
> The only thing that gives me pause is that it's possible these entries
> are *really* large. If you have a decent number of tables that are all
> a few megabytes of histograms then things could go poorly. But I don't
> think having to read in these entries from pg_toast every time you
> plan a query is going to go much better for you either.

Yep; in fact, I've previously submitted test results showing that
repeatedly decompressing TOAST entries can significantly slow down
query planning.

That having been said, Tom's fix seems safer to back-patch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company