> I wasn't thinking about adding a new VM setting functionality to index
> only scan in particular. heapam_index_fetch_tuple() already calls
> heap_page_prune_opt() which will do pruning under certain conditions.
> I was thinking that we start updating the VM after pruning in the
> on-access case t
On Sat, Apr 5, 2025 at 2:02 AM Konstantin Knizhnik wrote:
>
> A more targeted solution to your specific problem would be to update
> the visibility map on access. Then, the first time you have to fetch
> that heap page, you could mark it all-visible (assuming the long
> running transaction has end
Hi,
> ... and it is claimed that autovacuum will never be triggered in order
> to set hint bits, assuming we never modify the table again.
Actually I waited a bit and got a better EXPLAIN:
```
Index Only Scan using humidity_idx on humidity (cost=0.42..181.36
rows=1970 width=4) (actual time=0.3
On 03/04/2025 6:29 pm, Aleksander Alekseev wrote:
I have mixed feelings about addressing this. Although this behavior is
somewhat counterintuitive, if the user has a read-only lookup table
he/she can always execute VACUUM manually. In order to relieve him of
this unbearable burden we are going
On 04/04/2025 10:41 pm, Melanie Plageman wrote:
On Fri, Apr 4, 2025 at 3:27 PM Konstantin Knizhnik wrote:
From logical point of view I agree with you: taken in account number of
inserted tuples makes sense if it allows to mark page as all-visible.
So `ins_since_vacuum` should be better rename
> I think this enables us to update the VM
> during on-access pruning. This is something I plan to work on in 19.
> It seems like it would alleviate situations like this.
IMO, index-only scans hitting the heap have always caught users off guard,
especially because scan performance fluctuates betw
> From logical point of view I agree with you: taken in account number of
> inserted tuples makes sense if it allows to mark page as all-visible.
> So `ins_since_vacuum` should be better renamed to
> `ins_all_visible_since_vacuum` and count only all-visible tuples.
> If newly inserted tuple is no
On Fri, Apr 4, 2025 at 3:27 PM Konstantin Knizhnik wrote:
>
> From logical point of view I agree with you: taken in account number of
> inserted tuples makes sense if it allows to mark page as all-visible.
> So `ins_since_vacuum` should be better renamed to
> `ins_all_visible_since_vacuum` and c
On 04/04/2025 7:10 pm, Melanie Plageman wrote:
On Fri, Apr 4, 2025 at 1:53 AM Konstantin Knizhnik wrote:
What is needed to reproduce the problem?
1. Table with populated data
2. Presence of transaction with assigned XID which prevents vacuum from
marking pages of this table as all visible
3. Va
On Fri, Apr 4, 2025 at 12:11 PM Melanie Plageman
wrote:
> That being said, long-running transactions are a problem for
> autovacuum in general. Even if you track this stat you are proposing
> about heap fetches by index only scans, you won't know if the long
> running transaction is over and thus
On Fri, Apr 4, 2025 at 1:53 AM Konstantin Knizhnik wrote:
>
> What is needed to reproduce the problem?
> 1. Table with populated data
> 2. Presence of transaction with assigned XID which prevents vacuum from
> marking pages of this table as all visible
> 3. Vacuum or autovacuum processed this tabl
On 03/04/2025 6:50 pm, Aleksander Alekseev wrote:
Hi,
... and it is claimed that autovacuum will never be triggered in order
to set hint bits, assuming we never modify the table again.
Actually I waited a bit and got a better EXPLAIN:
```
Index Only Scan using humidity_idx on humidity (c
Hi hackers,
Sometime ago I investigated slow query performance case of one customer
and noticed that index-only scan has made a lot of heap fetches.
-> Index Only Scan using ix_client_objects_vendor_object_id on
client_objects client_objects_1 (cost=0.56..2.78 rows=1 width=0) (actual
time=0
On Thu, Apr 3, 2025 at 4:37 PM Sami Imseih wrote:
>
> From what I can tell in your example, you ran the manual vacuum ( session 1)
> while you had an open transaction (session 2), so vacuum could not remove
> the dead tuples or update the visibility map. Once you committed session 2,
> autovacuum
> Interestingly it takes unusually long for my toy database:
> There is nothing in between these two lines.
>
> To my humble knowledge, CHECKOINT shouldn't set hint bits and should
> take that long. At this point I don't know what's going on.
>
>From what I can tell in your example, you ran the m
Hi Konstantin,
> But the problem can be quite easily reproduced. We can just populate table
> with some data with some other transaction with assigned XID active.
> Then explicitly vacuum this tables or wait until autovacuum does it.
> At this moment table has no more dead or inserted tuples so a
16 matches
Mail list logo
