pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query on a one-tuple table
13+ messages / 5 participants
[nested] [flat]
* Slow query on a one-tuple table
@ 2019-09-19 16:55 Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Luís Roberto Weck @ 2019-09-19 16:55 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi!
I have a query that SELECT's only one tuple using a PK
(https://explain.depesz.com/s/Hskt) <https://explain.depesz.com/s/Hskt;
the field I am selecting are a bigint and a text. Why does it read 1095
shared buffers read?
If I adda LIMIT 1 clause, the query runs much faster:
https://explain.depesz.com/s/bSZn
This table has only one tuple anyway, so I can't understand why does it
takes so long without the LIMIT 1.
<https://explain.depesz.com/s/Hskt;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 17:21 Michael Lewis <mlewis@entrata.com>
parent: Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Lewis @ 2019-09-19 17:21 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org
Is this result able to be repeated?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 18:30 Luís Roberto Weck <luisroberto@siscobra.com.br>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 13+ messages in thread
From: Luís Roberto Weck @ 2019-09-19 18:30 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: pgsql-performance@lists.postgresql.org
Em 19/09/2019 14:21, Michael Lewis escreveu:
> Is this result able to be repeated?
Yes, I can consistently repeat it.
Postgres version is 11.1.
Other executions:
Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47
rows=1 width=62) (actual time=1.591..4.035 rows=1 loops=1)
Output: asscod, asscambol
Index Cond: (assessoria.asscod = 1)
Buffers: shared hit=1187
Planning Time: 0.053 ms
Execution Time: 4.055 ms
Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47
rows=1 width=62) (actual time=1.369..3.838 rows=1 loops=1)
Output: asscod, asscambol
Index Cond: (assessoria.asscod = 1)
Buffers: shared hit=1187
Planning Time: 0.033 ms
Execution Time: 3.851 ms
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Slow query on a one-tuple table
@ 2019-09-19 18:34 Igor Neyman <ineyman@perceptron.com>
parent: Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Igor Neyman @ 2019-09-19 18:34 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; Michael Lewis <mlewis@entrata.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
-----Original Message-----
From: Luís Roberto Weck [mailto:luisroberto@siscobra.com.br]
Sent: Thursday, September 19, 2019 2:30 PM
To: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow query on a one-tuple table
WARNING: This email originated from outside of Perceptron! Please be mindful of PHISHING and MALWARE risks.
Em 19/09/2019 14:21, Michael Lewis escreveu:
> Is this result able to be repeated?
Yes, I can consistently repeat it.
Postgres version is 11.1.
Other executions:
Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47
rows=1 width=62) (actual time=1.591..4.035 rows=1 loops=1)
Output: asscod, asscambol
Index Cond: (assessoria.asscod = 1)
Buffers: shared hit=1187
Planning Time: 0.053 ms
Execution Time: 4.055 ms
Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47
rows=1 width=62) (actual time=1.369..3.838 rows=1 loops=1)
Output: asscod, asscambol
Index Cond: (assessoria.asscod = 1)
Buffers: shared hit=1187
Planning Time: 0.033 ms
Execution Time: 3.851 ms
________________________________________________________________________________________________________________
But can you repeat it with "LIMIT 1"?
Notice huge difference in "buffers hit" while doing (the same) Index Scan in two plans.
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 19:59 Luís Roberto Weck <luisroberto@siscobra.com.br>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 13+ messages in thread
From: Luís Roberto Weck @ 2019-09-19 19:59 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: pgsql-performance@lists.postgresql.org
Em 19/09/2019 15:34, Igor Neyman escreveu:
> -----Original Message-----
> From: Luís Roberto Weck [mailto:luisroberto@siscobra.com.br]
> Sent: Thursday, September 19, 2019 2:30 PM
> To: Michael Lewis <mlewis@entrata.com>
> Cc: pgsql-performance@lists.postgresql.org
> Subject: Re: Slow query on a one-tuple table
>
> WARNING: This email originated from outside of Perceptron! Please be mindful of PHISHING and MALWARE risks.
>
> Em 19/09/2019 14:21, Michael Lewis escreveu:
>> Is this result able to be repeated?
> Yes, I can consistently repeat it.
>
> Postgres version is 11.1.
>
> Other executions:
>
> Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47
> rows=1 width=62) (actual time=1.591..4.035 rows=1 loops=1)
> Output: asscod, asscambol
> Index Cond: (assessoria.asscod = 1)
> Buffers: shared hit=1187
> Planning Time: 0.053 ms
> Execution Time: 4.055 ms
>
> Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47
> rows=1 width=62) (actual time=1.369..3.838 rows=1 loops=1)
> Output: asscod, asscambol
> Index Cond: (assessoria.asscod = 1)
> Buffers: shared hit=1187
> Planning Time: 0.033 ms
> Execution Time: 3.851 ms
>
> ________________________________________________________________________________________________________________
>
> But can you repeat it with "LIMIT 1"?
> Notice huge difference in "buffers hit" while doing (the same) Index Scan in two plans.
>
> Regards,
> Igor Neyman
With LIMIT 1, I get 3 shared buffers hit, pretty much always.
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Slow query on a one-tuple table
@ 2019-09-19 20:11 Igor Neyman <ineyman@perceptron.com>
parent: Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Igor Neyman @ 2019-09-19 20:11 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; Michael Lewis <mlewis@entrata.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
With LIMIT 1, I get 3 shared buffers hit, pretty much always.
____________________________________________________________________________________
Check if assessoria_pkey index is bloated.
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 20:24 Luís Roberto Weck <luisroberto@siscobra.com.br>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 13+ messages in thread
From: Luís Roberto Weck @ 2019-09-19 20:24 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Michael Lewis <mlewis@entrata.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 20:41 Luís Roberto Weck <luisroberto@siscobra.com.br>
parent: Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Luís Roberto Weck @ 2019-09-19 20:41 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org; +Cc: Igor Neyman <ineyman@perceptron.com>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 22:27 Luís Roberto Weck <luisroberto@siscobra.com.br>
parent: Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Luís Roberto Weck @ 2019-09-19 22:27 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org; +Cc: suporte@siscobra.com.br
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?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 22:32 Michael Lewis <mlewis@entrata.com>
parent: Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Lewis @ 2019-09-19 22:32 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org; suporte@siscobra.com.br
>
> 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?
>
HOT update is only possible if there is room in the page. How wide is your
single tuple?
Have you tuned autovacuum or are you running defaults? Not sure of your
perception of "very frequently" updated values, but if you have bloat
issue, vacuum early and often. Not sure how the math works out on a table
with single tuple in terms of calculating when it is time to vacuum, but it
certainly needs to be tuned differently than a table with millions of rows
which is what I would be more used to.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 22:46 Luís Roberto Weck <luisroberto@siscobra.com.br>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 13+ messages in thread
From: Luís Roberto Weck @ 2019-09-19 22:46 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: pgsql-performance@lists.postgresql.org
Em 19/09/2019 19:32, Michael Lewis escreveu:
>
> 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?
>
>
> HOT update is only possible if there is room in the page. How wide is
> your single tuple?
>
> Have you tuned autovacuum or are you running defaults? Not sure of
> your perception of "very frequently" updated values, but if you have
> bloat issue, vacuum early and often. Not sure how the math works out
> on a table with single tuple in terms of calculating when it is time
> to vacuum, but it certainly needs to be tuned differently than a table
> with millions of rows which is what I would be more used to.
I'm not sure how to measure how wide the tuple is, can you point me in
the right direction?
As fas as autovacuum options, this is what I'm using:
autovacuum_enabled=true,
fillfactor=50,
autovacuum_vacuum_threshold=25,
autovacuum_vacuum_scale_factor=0,
autovacuum_analyze_threshold=10,
autovacuum_analyze_scale_factor=0.05,
autovacuum_vacuum_cost_delay=10,
autovacuum_vacuum_cost_limit=1000,
toast.autovacuum_enabled=true
By "very frequently" I mean I can update it up to 800000 times a day.
Usually this number is closer to 100000.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-19 22:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Luís Roberto Weck <luisroberto@siscobra.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2019-09-19 22:57 UTC (permalink / raw)
To: Luís Roberto Weck <luisroberto@siscobra.com.br>; +Cc: Michael Lewis <mlewis@entrata.com>; pgsql-performance@lists.postgresql.org
=?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br> writes:
> As fas as autovacuum options, this is what I'm using:
> autovacuum_vacuum_scale_factor=0,
Ugh ... maybe I'm misremembering, but I *think* that has the effect
of disabling autovac completely. You don't want zero.
Check in pg_stat_all_tables.last_autovacuum to see if anything
is happening. If the dates seem reasonably current, then I'm wrong.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Slow query on a one-tuple table
@ 2019-09-20 13:09 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: MichaelDBA @ 2019-09-20 13:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Luís Roberto Weck <luisroberto@siscobra.com.br>; Michael Lewis <mlewis@entrata.com>; pgsql-performance@lists.postgresql.org
Hi all,
I sometimes set autovacuum_vacuum_scale factor = 0 but only when I also
set autovacuum_vacuum_threshold to some non-zero number to force vacuums
after a certain number of rows are updated. It takes the math out of it
by setting the threshold explicitly.
But in this case he has also set autovacuum_vacuum_threshold to only
25! So I think you have to fix your settings by increasing one or both
accordingly.
Regards,
Michael Vitale
Tom Lane wrote on 9/19/2019 6:57 PM:
> =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br> writes:
>> As fas as autovacuum options, this is what I'm using:
>> autovacuum_vacuum_scale_factor=0,
> Ugh ... maybe I'm misremembering, but I *think* that has the effect
> of disabling autovac completely. You don't want zero.
>
> Check in pg_stat_all_tables.last_autovacuum to see if anything
> is happening. If the dates seem reasonably current, then I'm wrong.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2019-09-20 13:09 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-19 16:55 Slow query on a one-tuple table Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-09-19 17:21 ` Michael Lewis <mlewis@entrata.com>
2019-09-19 18:30 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-09-19 18:34 ` Igor Neyman <ineyman@perceptron.com>
2019-09-19 19:59 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-09-19 20:11 ` Igor Neyman <ineyman@perceptron.com>
2019-09-19 20:24 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-09-19 20:41 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-09-19 22:27 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-09-19 22:32 ` Michael Lewis <mlewis@entrata.com>
2019-09-19 22:46 ` Luís Roberto Weck <luisroberto@siscobra.com.br>
2019-09-19 22:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-09-20 13:09 ` MichaelDBA <MichaelDBA@sqlexec.com>
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