Re: Speed up transaction completion faster after many relations are accessed in a transaction

On Tue, 19 Feb 2019 at 00:20, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2019-02-18 19:13:31 -0500, Tom Lane wrote:
> > Andres Freund <andres(at)anarazel(dot)de> writes:
> > > On 2019-02-18 19:01:06 -0500, Tom Lane wrote:
> > >> Mmm ... AIUI, the patches currently proposed can only help for what
> > >> David called "point lookup" queries. There are still going to be
> > >> queries that scan a large proportion of a partition tree, so if you've
> > >> got tons of partitions, you'll be concerned about this sort of thing.
>

> > I think what Maumau-san is on about here is that not only does your
> > $giant-query take a long time, but it has a permanent negative effect
> > on all subsequent transactions in the session. That seems worth
> > doing something about.
>
> Ah, yes, that makes sense. I'm inclined to think however that the
> original approach presented in this thread is better than the
> reset-the-whole-hashtable approach.
>

If it was just many-tables then blowing away the hash table would work fine.

The main issue seems to be with partitioning, not with the general case of
many-tables. For that case, it seems like reset hashtable is too much.

Can we use our knowledge of the structure of locks, i.e. partition locks
are all children of the partitioned table, to do a better job?

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services