Re: Slow query on a one-tuple table

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