On Tue, Nov 20, 2018 at 12:53 PM Darafei "Komяpa" Praliaskouski
<me@komzpa.net> wrote:
>>
>> > In PostGIS workloads, UPDATE table SET geom = ST_CostyFunction(geom, magicnumber); is one of biggest time-eaters
thathappen upon initial load and clean up of your data. It is commonly followed by CLUSTER table using table_geom_idx;
tomake sure you're back at full speed and no VACUUM is needed, and your table (usually static after that) is
more-or-lessspatially ordered. I see that zheap can remove the need for VACUUM, which is a big win already. If you can
dosomething that will allow reorder of tuples according to index happen during an UPDATE that rewrites most of table,
thatwould be a game changer :)
>> >
>>
>> If the tuples are already in the order of the index, then we would
>> retain the order, otherwise, we might not want to anything special for
>> ordering w.r.t index. I think this is important as we are not sure of
>> the user's intention and I guess it won't be easy to do such
>> rearrangement during Update statement.
>
>
> User's clustering intention is recorded in existence of CLUSTER index over table. That's not used by anything other
thanCLUSTER command now though.
>
> When I was looking into current heap implementation it seemed that it's possible to hook in a lookup for a couple
blockswith values adjacent to the new value, and prefer them to FSM lookup and "current page", for clustered table. Due
todead tuples, free space is going to end very very soon in usual heap, so it probably doesn't make sense there -
you'reconsuming space with old one in old page and new one in new page.
>
> If I understand correctly, in zheap an update would not result in a dead tuple in old page, so space is not going to
endimmediately, and this may unblock path for such further developments. That is, if there is a spot where to plug in
suchor similar logic in code :)
>
Yeah, in zheap the dead tuples will be less or may not be there in
many cases, but I am not sure how much it can help for your use case.
> I've described the business case in [1].
>
I am not sure but maybe you need something like Clustered Index where
heap pages are linked via leaf pages of btree.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com