From: | Luís Roberto Weck <luisroberto(at)siscobra(dot)com(dot)br> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Cc: | suporte(at)siscobra(dot)com(dot)br |
Subject: | Re: Slow query on a one-tuple table |
Date: | 2019-09-19 22:27:34 |
Message-ID: | 1980a708-ed4a-6fc6-2792-33c07b07d704@siscobra.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Em 19/09/2019 17:41, Luís Roberto Weck escreveu:
> 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_inde
Like Igor suggested, the index bloat seems to be at fault here. After
dropping the PK, I'm getting these plans:
First run (SELECT asscod, asscambol FROM ASSESSORIA WHERE asscod = 1
ORDER BY asscod):
Seq Scan on public.assessoria (cost=0.00..88.01 rows=1 width=62)
(actual time=0.242..0.810 rows=1 loops=1)
Output: asscod, asscambol
Filter: (assessoria.asscod = 1)
Buffers: shared hit=88
Planning Time: 0.312 ms
Execution Time: 0.876 ms
(6 rows)
Subsequent runs get increasingly faster, up to 0.080ms execution times.
Using LIMIT 1, I get on the first run:
Limit (cost=0.00..88.01 rows=1 width=62) (actual time=0.252..0.254
rows=1 loops=1)
Output: asscod, asscambol
Buffers: shared hit=17
-> Seq Scan on public.assessoria (cost=0.00..88.01 rows=1
width=62) (actual time=0.250..0.250 rows=1 loops=1)
Output: asscod, asscambol
Filter: (assessoria.asscod = 1)
Buffers: shared hit=17
Planning Time: 0.334 ms
Execution Time: 0.296 ms
Subsequent runs look more like this:
Limit (cost=0.00..88.01 rows=1 width=62) (actual time=0.057..0.057
rows=1 loops=1)
Output: asscod, asscambol
Buffers: shared hit=17
-> Seq Scan on public.assessoria (cost=0.00..88.01 rows=1
width=62) (actual time=0.056..0.056 rows=1 loops=1)
Output: asscod, asscambol
Filter: (assessoria.asscod = 1)
Buffers: shared hit=17
Planning Time: 0.082 ms
Execution Time: 0.068 ms
I have about 6 bigint fields in this table that are very frequently
updated, but none of these are indexed. I thought that by not having an
index on them, would make all updates HOT, therefore not bloating the
primary key index. Seems I was wrong?
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2019-09-19 22:32:19 | Re: Slow query on a one-tuple table |
Previous Message | Tom Lane | 2019-09-19 21:36:35 | Re: Surprising benchmark count(1) vs. count(*) |