SEO

Pavan Deolasee wrote:
> together. In the SELECT path, we conditionally try for exclusive lock. If
> the exclusive lock is also cleanup lock, we prune and defrag the page.
> As the patch stands, during index fetch we try to prune only the chain
> originating at that tuple. I am wondering if we should change that
> and prune all the tuple chains in the page ?

I am thinking we are best just doing the index chain we already have to
read.

If you are modifying the table (like with UPDATE) it makes sense to be
more aggressive and do the whole page because you know there are
probably other table modifications, but for an index lookup there isn't
any knowledge of whether the table is being modified so looking at more
than we need seems like overkill.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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

Bruce Momjian <bruce@momjian.us> writes:
> I am thinking we are best just doing the index chain we already have to
> read.

> If you are modifying the table (like with UPDATE) it makes sense to be
> more aggressive and do the whole page because you know there are
> probably other table modifications, but for an index lookup there isn't
> any knowledge of whether the table is being modified so looking at more
> than we need seems like overkill.

Uh, why would any of this code at all execute during a pure lookup?

regards, tom lane

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

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > I am thinking we are best just doing the index chain we already have to
> > read.
>
> > If you are modifying the table (like with UPDATE) it makes sense to be
> > more aggressive and do the whole page because you know there are
> > probably other table modifications, but for an index lookup there isn't
> > any knowledge of whether the table is being modified so looking at more
> > than we need seems like overkill.
>
> 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.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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.

regards, tom lane

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

http://archives.postgresql.org

"Bruce Momjian" <bruce@momjian.us> writes:

> Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>> > I am thinking we are best just doing the index chain we already have to
>> > read.
>>
>> > If you are modifying the table (like with UPDATE) it makes sense to be
>> > more aggressive and do the whole page because you know there are
>> > probably other table modifications, but for an index lookup there isn't
>> > any knowledge of whether the table is being modified so looking at more
>> > than we need seems like overkill.
>>
>> 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.

Pruning chains is kind of the whole point of the exercise no?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

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

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

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> 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.

Ah, as I understand it you can't actually do the pruning then because the
executor holds references to source tuple on the page. In other words you can
never "get the vacuum lock" there because you already have the page pinned
yourself.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

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

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

On 9/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
>
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>
> >
> > 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.
>
> Ah, as I understand it you can't actually do the pruning then because the
> executor holds references to source tuple on the page. In other words you
> can
> never "get the vacuum lock" there because you already have the page pinned
> yourself.
>
>
I don't think executor holding a reference is a problem because when
we check for vacuum lock, we have already pinned the page anyways.
But moving the old tuple around deep down in the UPDATE code path
(when we realize that there is no free space) is an issue. I know Heikki
tried to do it this way - but then moved the pruning code to lookup
code. Heikki ?

Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates. Every lookup to the
visible tuple in this chain would be CPU expensive since it would require
long chain following.

The downside is of course that SELECT may occasionally do more work.
But since we ensure that we do the extra work only when there is no
contention on the page, ISTM that the downside is limited.


Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

"Pavan Deolasee" <pavan.deolasee@gmail.com> writes:

> On 9/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
>
>> Ah, as I understand it you can't actually do the pruning then because the
>> executor holds references to source tuple on the page. In other words you
>> can never "get the vacuum lock" there because you already have the page
>> pinned yourself.
>
> I don't think executor holding a reference is a problem because when
> we check for vacuum lock, we have already pinned the page anyways.

But that's the point. Even though the pin-count is 1 and it may look like we
have the vacuum lock we really don't. The fact that the buffer was already
pinned by us means that there are already pointers around referencing tuples
on that page. If we move them around those pointers become garbage. In fact
Postgres avoids copying data if it can get by with a pointer at the original
tuple on disk so some of the pointers will be Datums pointing at individual
columns in the tuples in the page.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

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

http://archives.postgresql.org

Pavan Deolasee wrote:
> On 9/6/07, Gregory Stark <stark@enterprisedb.com> wrote:
>> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>>
>>> 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.
>> Ah, as I understand it you can't actually do the pruning then because the
>> executor holds references to source tuple on the page. In other words you
>> can
>> never "get the vacuum lock" there because you already have the page pinned
>> yourself.
>>
>>
> I don't think executor holding a reference is a problem because when
> we check for vacuum lock, we have already pinned the page anyways.
> But moving the old tuple around deep down in the UPDATE code path
> (when we realize that there is no free space) is an issue. I know Heikki
> tried to do it this way - but then moved the pruning code to lookup
> code. Heikki ?

When I suggested that we get rid of the LP_DELETE flag for heap tuples,
the tuple-level fragmentation and all that, and just take the vacuum
lock and call PageRepairFragmentation, I was thinking that we'd do it in
heap_update and only when we run out of space on the page. But as Greg
said, it doesn't work because you're already holding a reference to at
least one tuple on the page, the one you're updating, by the time you
get to heap_update. That's why I put the pruning code to heap_fetch
instead. Yes, though the amortized cost is the same, it does push the
pruning work to the foreground query path.

That was a reason for separating the pruning and defragmenting a page.
We could do the pruning in heap_update, and only do the defragmenting in
heap_fetch. I was also thinking of an optimization to the pruning, so
that while we scan the page and remove dead tuples, we also check if we
leave behind an empty gap that's big enough to accommodate the new tuple
we're inserting, and reuse that space immediately, without defragmenting.

> Another real problem with doing pruning only in UPDATE path is that
> we may end up with long HOT chains if the page does not receive a
> UPDATE, after many consecutive HOT updates. Every lookup to the
> visible tuple in this chain would be CPU expensive since it would require
> long chain following.

Yes. For that as well, we could prune only, but not defragment, the page
in the lookup path.

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

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

http://archives.postgresql.org

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> When I suggested that we get rid of the LP_DELETE flag for heap tuples,
> the tuple-level fragmentation and all that, and just take the vacuum
> lock and call PageRepairFragmentation, I was thinking that we'd do it in
> heap_update and only when we run out of space on the page. But as Greg
> said, it doesn't work because you're already holding a reference to at
> least one tuple on the page, the one you're updating, by the time you
> get to heap_update. That's why I put the pruning code to heap_fetch
> instead. Yes, though the amortized cost is the same, it does push the
> pruning work to the foreground query path.

The amortized cost is only "the same" if every heap_fetch is associated
with a heap update. I feel pretty urgently unhappy about this choice.
Have you tested the impact of the patch on read-mostly workloads?

>> Another real problem with doing pruning only in UPDATE path is that
>> we may end up with long HOT chains if the page does not receive a
>> UPDATE, after many consecutive HOT updates.

How is that, if the same number of prune attempts would occur?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster