Hi, Jim,
Jim Nasby wrote:
> I'd love to have the ability to control toasting thresholds manually.
> This could result in a lot of speed improvements in cases where a
> varlena field isn't frequently accessed and will be fairly large, yet
> not large enough to normally trigger toasting. An address field would be
> a good example. Being able to force a field to be toasted before it
> normally would could drastically improve tuple density without requiring
> the developer to use a 'side table' to store the data.
Sounds good.
But I remember that the query planner underestimated sequential scans
when lots of TOAST data was in the table.
IIRC, The specific case (that was discussent on pgperform) was about 70
PostGIS geometries, amounting to about 35MB of TOAST data and only 2 or
3 pages in the actual table.
The query planner used an sequential scan instead of an GIST index scan
(&& operator), leading to deTOASTing and processing all 35 MB of
geometries, instead of just those 2 small ones that matched the index
condition.
So I think before we start toasting more, we should check whether the
query planner could be affected negatively.
It should have statistics about TOAST data, and then see whether he'd
need to detoast for condition checking and for actual data fetching.
Thanks,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org