pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Luís Roberto Weck <luisroberto@siscobra.com.br>
To: pgsql-performance@lists.postgresql.org
Cc: Igor Neyman <ineyman@perceptron.com>
Subject: Re: Slow query on a one-tuple table
Date: Thu, 19 Sep 2019 17:41:19 -0300
Message-ID: <35df2bd8-4c7c-7ace-6bdc-d6d97a101a14@siscobra.com.br> (raw )
In-Reply-To: <18ae8108-acaf-e9cc-96a9-ab20f4147f5a@siscobra.com.br >
References: <CA+t6e1kacbVHq69aihEVeBzH_nMY5eK7bZ-UXL_FuUquEgF_gg@mail.gmail.com >
<24571.1568910502@sss.pgh.pa.us >
<774d6080-d6d7-e9e7-7351-9a412c7d5956@siscobra.com.br >
<CAHOFxGqMpUxn54jMLoU4-HcUata2yfLgjEbRHdJG3k0BBDPbog@mail.gmail.com >
<ab6ea98e-6266-7665-40b7-151c5736c08d@siscobra.com.br >
<BN6PR1701MB1890E31E15D486A85FEC8510DA890@BN6PR1701MB1890.namprd17.prod.outlook.com >
<bf03aedc-9201-f7c9-ef25-e795d4e1626a@siscobra.com.br >
<BN6PR1701MB1890A207FBF02BBC5E3C9F01DA890@BN6PR1701MB1890.namprd17.prod.outlook.com >
<18ae8108-acaf-e9cc-96a9-ab20f4147f5a@siscobra.com.br >
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
view thread (13+ messages) latest in thread
Message-ID: <35df2bd8-4c7c-7ace-6bdc-d6d97a101a14@siscobra.com.br>
Permalink: ../35df2bd8-4c7c-7ace-6bdc-d6d97a101a14@siscobra.com.br/
Also on: postgresql.org/message-id/35df2bd8-4c7c-7ace-6bdc-d6d97a101a14@siscobra.com.br
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: luisroberto@siscobra.com.br, pgsql-performance@lists.postgresql.org, ineyman@perceptron.com
Subject: Re: Slow query on a one-tuple table
In-Reply-To: <35df2bd8-4c7c-7ace-6bdc-d6d97a101a14@siscobra.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox