From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Les <nagylzs(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Slow query, possibly not using index |
Date: | 2023-08-28 11:03:12 |
Message-ID: | CAFj8pRAzwWLDWHuyX-Nx7zjU+YBqfcuidzM2vFr3G9aGKgHUxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
po 28. 8. 2023 v 13:00 odesílatel Les <nagylzs(at)gmail(dot)com> 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
>>
>> ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
>> 4 | 2 | 389677056 | 546 | 114 |
>> 23069 | 0 | 24384 | 90.03 | 0
>> (1 row)
>>
>> After reindex:
>
> =# 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
>
> ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
> 4 | 0 | 8192 | 0 | 0 |
> 0 | 0 | 0 | NaN | NaN
> (1 row)
>
> explain analyze select id from media.block b where nrefs =0 limit 1
>
> QUERY PLAN
> |
>
> -----------------------------------------------------------------------------------------------------------------------------------------+
> Limit (cost=0.14..0.46 rows=1 width=16) (actual time=0.010..0.011 rows=0
> loops=1) |
> -> Index Only Scan using idx_block_unused on block b
> (cost=0.14..698.91 rows=2231 width=16) (actual time=0.008..0.009 rows=0
> loops=1)|
> Heap Fetches: 0
> |
> Planning Time: 0.174 ms
> |
> Execution Time: 0.030 ms
> |
>
> It is actually empty.
>
> Now I only need to figure out why autovacuum did not work on the index.
>
Autovacuum doesn't reindex.
Regards
Pavel
>
> Thank you
>
> Laszlo
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2023-08-28 11:42:30 | Re: Slow query, possibly not using index |
Previous Message | Les | 2023-08-28 10:59:40 | Re: Slow query, possibly not using index |