Re: WIP patch: reducing overhead for repeat de-TOASTing

On Sun, 2008-06-29 at 16:57 -0400, Tom Lane wrote:
> After playing with it for a little bit, I'm not convinced that it buys
> enough performance win to be worth applying --- the restriction of cache
> lifespan to one tuple cycle of a TupleTableSlot is awfully restrictive.
> (For example, sorts that involve toasted sort keys continue to suck,
> because the tuples being sorted aren't in Slots.) It would probably
> fix the specific case that the PostGIS hackers were complaining of,
> but I think we need something more.
>
> Still, I wanted to get it into the archives because the idea of indirect
> toast pointers might be useful for something else.
>

Thank you for posting this to the list, this does help us at Truviso
(sometimes). In some real cases, we're seeing about 15-20x improvement
of the overall query; going from about 9 seconds to under 500 ms. In
other cases that could theoretically benefit from TOAST caching, we see
no improvement at all.

As you say, the cases where it helps are fairly narrow. It's
also very susceptible to changes in the plan. For instance, if the
toasted value is on one side of a nested loop, the patch helps a
lot; but it goes back to the performance of unpatched PostgreSQL for the
same plan if the nested loop is inverted, as shown below.

Regards,
Jeff Davis

[ plan estimates are omitted for readability ]

----------- UNPATCHED -----------

Nested Loop (actual time=3.021..1020.923 rows=1000 loops=1)
-> Seq Scan on toasted (actual time=0.016..0.017 rows=1 loops=1)
-> Seq Scan on a (actual time=0.016..0.735 rows=1000 loops=1)
Total runtime: 1021.397 ms

Nested Loop (actual time=1.352..1037.207 rows=1000 loops=1)
-> Seq Scan on a (actual time=0.007..0.364 rows=1000 loops=1)
-> Seq Scan on toasted (actual time=0.001..0.002 rows=1 loops=1000)
Total runtime: 1037.653 ms

------------ PATCHED ------------
-- patch helps
Nested Loop (actual time=1.266..2.247 rows=1000 loops=1)
-> Seq Scan on toasted (actual time=0.003..0.072 rows=1 loops=1)
-> Seq Scan on a (actual time=0.006..0.328 rows=1000 loops=1)
Total runtime: 2.519 ms

-- patch has no effect
Nested Loop (actual time=1.283..1012.371 rows=1000 loops=1)
-> Seq Scan on a (actual time=0.010..0.719 rows=1000 loops=1)
-> Seq Scan on toasted (actual time=0.001..0.059 rows=1 loops=1000)
Total runtime: 1012.973 ms