Em 19/09/2019 17:24, Luís Roberto Weck escreveu:
Em 19/09/2019 17:11, Igor Neyman escreveu:
With LIMIT 1, I get 3 shared buffers hit, pretty much always.
____________________________________________________________________________________
Check if assessoria_pkey index is bloated.
Regards,
Igor Neyman
With this query[1] it shows:
current_database|schemaname|tblname |idxname
|real_size|extra_size|extra_ratio|fillfactor|bloat_size|bloat_ratio|is_na|
----------------|----------|----------|---------------|---------|----------|-----------|----------|----------|-----------|-----|
database_name |public |assessoria|assessoria_pkey| 16384|
0| 0.0| 90| 0.0| 0.0|false|
[1]https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat-superuser.sql
Using the quer provided here[1] I see this comment:
/*
* distinct_real_item_keys is how many distinct "data" fields on page
* (excludes highkey).
*
* If this is less than distinct_block_pointers on an internal page, that
* means that there are so many duplicates in its children that there are
* duplicate high keys in children, so the index is probably pretty
bloated.
*
* Even unique indexes can have duplicates. It's sometimes
interesting to
* watch out for how many distinct real items there are within leaf
pages,
* compared to the number of live items, or total number of items.
Ideally,
* these will all be exactly the same for unique indexes.
*/
In my case, I'm seeing:
distinct_real_item_keys|distinct_block_pointers|
-----------------------|-----------------------|
1| 63|
This is about half an hour after running VACUUM FULL ANALYZE on the table.
What can I do to reduce this?
[1]
https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index
