Florian Pflug wrote:
> Tom Lane wrote:
> A rather wild idea: Could we maybe pin individual tuples, instead
> of the whole page? Then we'd just have to be careful not to move
> those when pruning during the update.

Uh, that's a huge change. We might be able to keep track of tuples that
we have references to in our own backend, but even that seems like a
non-starter to me.

Yet another idea is to add an "intent" argument (or somehow pass it out
of line) to heap_fetch. You would prune the page in heap_fetch, but only
if you're fetching for the purpose of updating the tuple.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Wed, 2007-09-05 at 18:15 -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Tom Lane wrote:
> >> Uh, why would any of this code at all execute during a pure lookup?
>
> > No idea. It seems an index lookup tries to prune a heap chain, and he
> > was asking if it should look at other chains on the page; I said not.
> > Whether the index lookup should prune the heap chain is another issue.
>
> ISTM the only time we should be doing HOT cleanup work is when we are
> trying to insert a new tuple on that page --- and maybe even only when
> we try and it doesn't fit straight off. Otherwise you're pushing
> maintenance work into foreground query pathways, which is exactly where
> I don't think we should be headed.

This is one of the many heuristics for tuning HOT.

We can choose to do this immediately, when the page fills or somewhere
in between.

The rationale was: In other circumstances we dirty a page to set a hint
bit on the first time we see the need for it. That avoids the clog
lookup on all later reads of that tuple. By analogy, we do the same
thing with HOT: when we see a tuple chain that can be pruned, we prune
it. If we dirty the page for the hint bit it seems like we may as well
dirty the page some more and prune it also at the same time.

We could begin pruning only when the chain is N long. Currently N=2, but
we could set N=3+ easily enough. There's no code yet to actually count
that, but we can do that easily as we do each lookup. We should also be
able to remember the visibility result for each tuple in the chain to
decide whether pruning will be effective or not.

I would say that if the buffer is already dirty and the chain is
prunable, we should prune it at the first possible chance.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Heikki Linnakangas escribió:

> Hmm. I wonder if we could prune/defragment in bgwriter?

That would be best, if at all possible. You can prune without accessing
anything outside the page itself, right?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Alvaro Herrera wrote:
> Heikki Linnakangas escribió:
>
>> Hmm. I wonder if we could prune/defragment in bgwriter?
>
> That would be best, if at all possible. You can prune without accessing
> anything outside the page itself, right?

Yes, though you do need to have an oldest xmin to determine which tuples
are dead, and the removed tuples need to be WAL-logged.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Heikki Linnakangas escribió:

> Every time you do an index lookup, you have to follow that chain from 1
> to 5. Which gets expensive. That's why we want to prune the chain to
> make it shorter, even if there's still plenty of room on the page for
> updates, and even if we're never going to update it again.
>
> That's the theory. I don't know *how* expensive following the chain
> really is, compared to index scans skipping over entries marked as
> killed. I don't remember seeing any measurements of that.

I suggest you let that be. Chain following can't be *that* expensive --
and if it is, then pruning would be a lot more expensive, so it's not a
thing you want to be doing for every heap_fetch.

Pruning is going to take place on next vacuum anyway, isn't it?

AFAIUI the point is that you can't do cleanup selectively only on UPDATE
because of pointers that your process may already have to the page,
which is why you wanted to do it on every heap_fetch. I suggest it is
optimization which can be left for a later version (8.4?)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Thu, 2007-09-06 at 16:15 +0100, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> >> Imagine a page with just one tuple on it:
> >
> >> 1
> >
> >> After a bunch of updates, it looks like this
> >
> >> 1 -> 2 -> 3 -> 4 -> 5
> >
> >> 1 is the tuple the indexes point to, others are heap only.
> >
> > But if we were attempting prune at every update, at least some of the
> > later updates should have managed to prune. "2" should certainly be
> > gone at this point, unless there's lots of contention for the page,
> > in which case pruning at select won't make things better.
>
> Oh I see. Yeah, hopefully you don't end up with long chains too often.
> You don't need contention for it, though, a long-running transaction
> will cause it. Or a transaction that inserts a tuple and updates it
> multiple times in the same transaction.

Yes, the main point is that an UPDATE doesn't always allow you to prune.
If it did, that would be the right place. Since it doesn't the best
place to prune is surely the first time we see we *can* prune.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Simon Riggs wrote:
> Yes, the main point is that an UPDATE doesn't always allow you to prune.

You can always remove dead HOT tuples in heap_update. But you can never
defragment the page at that point.

> If it did, that would be the right place. Since it doesn't the best
> place to prune is surely the first time we see we *can* prune.

Not necessarily. Pruning is expensive, you need to scan all tuples on
the page and write WAL record. And defragment the page if you consider
that part of pruning. You don't want to do it too aggressively.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Tom Lane wrote:
>> I don't follow. HOT chains can only get longer by updates.

> Yes. We don't seem to be on the same wavelength...

> Imagine a page with just one tuple on it:

> 1

> After a bunch of updates, it looks like this

> 1 -> 2 -> 3 -> 4 -> 5

> 1 is the tuple the indexes point to, others are heap only.

But if we were attempting prune at every update, at least some of the
later updates should have managed to prune. "2" should certainly be
gone at this point, unless there's lots of contention for the page,
in which case pruning at select won't make things better.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Heikki Linnakangas escribió:
> Alvaro Herrera wrote:
> > Heikki Linnakangas escribió:
> >
> >> Hmm. I wonder if we could prune/defragment in bgwriter?
> >
> > That would be best, if at all possible. You can prune without accessing
> > anything outside the page itself, right?
>
> Yes, though you do need to have an oldest xmin to determine which tuples
> are dead,

Hmm, well, I think that with the VXID patch it might actually be
possible to get a Xmin without being in a transaction.

> and the removed tuples need to be WAL-logged.

Is this a problem for bgwriter?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Alvaro Herrera wrote:
>> That would be best, if at all possible. You can prune without accessing
>> anything outside the page itself, right?

> Yes, though you do need to have an oldest xmin to determine which tuples
> are dead, and the removed tuples need to be WAL-logged.

Hmm ... also check commit status (pg_clog access). I've always thought
that those things couldn't be done in bgwriter, because it wasn't
running real transactions, but right at the moment I can't see that
there is any obstacle. Perhaps that meme dates from a time when
GetOldestXmin didn't work outside a transaction?

Pushing the prune/defrag work into bgwriter would certainly fix my
worries about having it in foreground query paths.

(And screw up all Greg Smith's work on how much bgwriter should run...
or maybe this should be a third independent task within bgwriter?)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend