RE: Slow query on a one-tuple table

-----Original Message-----
From: Luís Roberto Weck [mailto:luisroberto(at)siscobra(dot)com(dot)br]
Sent: Thursday, September 19, 2019 2:30 PM
To: Michael Lewis <mlewis(at)entrata(dot)com>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)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