>
>
>
> > More important question is, how can I find out why the index was not
> auto vacuumed.
>
> You should have a look at pg_stat_user_tables. It'll let you know if
> the table is being autovacuumed and how often. If you're concerned
> about autovacuum not running properly, then you might wan
On Mon, 28 Aug 2023 at 19:21, Les wrote:
> More important question is, how can I find out why the index was not auto
> vacuumed.
You should have a look at pg_stat_user_tables. It'll let you know if
the table is being autovacuumed and how often. If you're concerned
about autovacuum not running
po 28. 8. 2023 v 13:00 odesÃlatel Les napsal:
>
>
>>
>> =# select * from pgstatindex('media.idx_block_unused');
>> version | tree_level | index_size | root_block_no | internal_pages |
>> leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
>> leaf_fragmentation
>>
>> -+-
>
>
> =# select * from pgstatindex('media.idx_block_unused');
> version | tree_level | index_size | root_block_no | internal_pages |
> leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
> leaf_fragmentation
>
> -+++---++---
>
>>
> All right, I started pgstattuple() and I'll also do pgstatindex(), but it
> takes a while. I'll get back with the results.
>
=# select * from pgstattuple('media.block');
table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | f
>
>
>
> > I'm aware of the problems with random UUID values. I was using this
> > function to create ulids from the beginning:
>
> Oh, well that would have been useful information to provide at the
> outset.
I'm sorry, I left this out.
> Now that we know the index order is correlated with creatio
Les writes:
>>> If I try to select a single unused block this way:
>>> explain analyze select id from media.block b where nrefs =0 limit 1
>>> then it runs for more than 10 minutes (I'm not sure how long, I cancelled
>>> the query after 10 minutes).
>> You might think that even so, it shouldn't t
> Nobody ever deleted anything from this table. Since it was created, this
has been a write-only table.
does write-only include updates? that would create the dead rows tom is
referring to.
> I believe it is not actually using the index, because reading a single
(random?) entry from an index shou
> > If I try to select a single unused block this way:
> > explain analyze select id from media.block b where nrefs =0 limit 1
> > then it runs for more than 10 minutes (I'm not sure how long, I cancelled
> > the query after 10 minutes).
>
> Are you sure it isn't blocked on a lock?
>
Yes, I'm sure
Les writes:
> If I try to select a single unused block this way:
> explain analyze select id from media.block b where nrefs =0 limit 1
> then it runs for more than 10 minutes (I'm not sure how long, I cancelled
> the query after 10 minutes).
Are you sure it isn't blocked on a lock?
Another theor
I have this table:
CREATE TABLE media.block (
id uuid NOT NULL,
"size" int8 NOT NULL,
nrefs int8 NOT NULL DEFAULT 0,
block bytea NOT NULL,
hs256 bytea NOT NULL,
CONSTRAINT block_pkey PRIMARY KEY (id),
CONSTRAINT chk_nrefs CHECK ((nrefs >= 0))
)
WITH (
toast_tuple_target=8160
)
TABLESPA
11 matches
Mail list logo
