Re: Slow query on a one-tuple table

=?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= 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

Re: Slow query on a one-tuple table

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.

Re: Slow query on a one-tuple table

> > 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

Re: Slow query on a one-tuple table

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.

Re: Surprising benchmark count(1) vs. count(*)

Adam Brusselback writes: > It would be nice if Postgres optimized this case though because it is > really really common from what i've seen. Since the introduction of the "planner support function" infrastructure, it'd be possible to do this without it being a completely ugly kluge: we could put

Re: Surprising benchmark count(1) vs. count(*)

I will say I've seen count(1) in the wild a ton, as well as at my own company from developers who were used to it not making a difference. There have been a couple queries in the hot path that I have had to changed from count(1) to count(*) as part of performance tuning, but in general it's not

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.

Re: Slow query on a one-tuple table

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:

RE: Slow query on a one-tuple table

With LIMIT 1, I get 3 shared buffers hit, pretty much always. Check if assessoria_pkey index is bloated. Regards, Igor Neyman

Re: Slow query on a one-tuple table

Em 19/09/2019 15:34, Igor Neyman escreveu: -Original Message- From: Luís Roberto Weck [mailto:luisrobe...@siscobra.com.br] Sent: Thursday, September 19, 2019 2:30 PM To: Michael Lewis Cc: pgsql-performance@lists.postgresql.org Subject: Re: Slow query on a one-tuple table WARNING: This

RE: Slow query on a one-tuple table

-Original Message- From: Luís Roberto Weck [mailto:luisrobe...@siscobra.com.br] Sent: Thursday, September 19, 2019 2:30 PM To: Michael Lewis Cc: pgsql-performance@lists.postgresql.org Subject: Re: Slow query on a one-tuple table WARNING: This email originated from outside of

Re: Slow query on a one-tuple table

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

Re: Delete huge Table under XFS

Re: Joao Junior 2019-09-19 > A table with 800 gb means 800 files of 1 gb. When I use truncate or drop > table, xfs that is a log based filesystem, will write lots of data in its > log and this is the problem. The problem is not postgres, it is the way > that xfs works with big files , or being

Re: Slow query on a one-tuple table

Is this result able to be repeated?

Re: Delete huge Table under XFS

A table with 800 gb means 800 files of 1 gb. When I use truncate or drop table, xfs that is a log based filesystem, will write lots of data in its log and this is the problem. The problem is not postgres, it is the way that xfs works with big files , or being more clear, the way that it handles

Slow query on a one-tuple table

Hi! I have a query that SELECT's only one tuple using a PK (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:

Re: Delete huge Table under XFS

Am 19.09.19 um 17:59 schrieb Joao Junior: I have a table that Is not being use anymore, I want to drop it. The table is huge, around 800GB and it has some index on it. When I execute the drop table command it goes very slow, I realised that the problem is the filesystem. It seems that XFS

Re: comparing output of internal pg tables of referenced tables

Mariel Cherkassky writes: > 2)select conname, (select r.relname from pg_class r where r.oid = > c.confrelid) as orig_table,(select array_agg(attname) from pg_attribute >where attrelid = c.confrelid and ARRAY[attnum] <@ c.conkey) as > orig_cols, (select r.relname from pg_class r where

Delete huge Table under XFS

Hi, I am running Postgresql 9.6 XFS as filesystem , kernel Linux 2.6.32. I have a table that Is not being use anymore, I want to drop it. The table is huge, around 800GB and it has some index on it. When I execute the drop table command it goes very slow, I realised that the problem is the

Re: Surprising benchmark count(1) vs. count(*)

Thomas Kellerer writes: > Laurenz Albe schrieb am 19.09.2019 um 12:22: >> "count(1)" has to check if 1 IS NULL for each row, because NULL >> values are not counted. "count(*)" doesn't have to do that. > But 1 is a constant, why does it need to check it for each row? [ shrug... ] There's no

Re: Surprising benchmark count(1) vs. count(*)

On Thu, 2019-09-19 at 12:09 +0200, Thomas Kellerer wrote: > https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ > > Is there a reason why count(*) seems to be faster? "count(*)" is just the SQL standard's way of saying what you'd normally call "count()", that is, an aggregate without

Surprising benchmark count(1) vs. count(*)

https://blog.jooq.org/2019/09/19/whats-faster-count-or-count1/ Is there a reason why count(*) seems to be faster?