Re: Fwd: TOAST table performance problem

-- Forwarded message - Gönderen: *Asya Nevra Buyuksoy* > Date: 10 Şub 2020 Pzt, 10:51 Subject: Re: TOAST table performance problem To: Andreas Joseph Krogh mailto:andr...@visena.com>> I copied my data to the CSV file, yes it is very fast. However, th

Re: Hash Join over Nested Loop

Em 22/11/2019 14:55, Pavel Stehule escreveu: pá 22. 11. 2019 v 18:37 odesílatel Luís Roberto Weck mailto:luisrobe...@siscobra.com.br>> napsal: Hey, I'm trying to figure out why Postgres is choosing a Hash Join over a Nested Loop in this query: SELECT T

Re: Hash Join over Nested Loop

Hey, I'm trying to figure out why Postgres is choosing a Hash Join over a Nested Loop in this query: SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti, T1.CarCod, T1.EmpCod,    T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE( T3.PesDatAnt, DATE '00010101') AS PesDatAnt   F

Hash Join over Nested Loop

Hey, I'm trying to figure out why Postgres is choosing a Hash Join over a Nested Loop in this query: SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti, T1.CarCod, T1.EmpCod,    T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE( T3.PesDatAnt, DATE '00010101') AS PesDatAnt   F

Re: Postgresql planning time too high

Em 22/11/2019 08:46, Sterpu Victor escreveu: I did runned "VACCUM FULL" followed by "VACUUM" but no difference. -- Original Message -- From: "Fırat Güleç" > To: "Sterpu Victor" mailto:vic...@caido.ro>> Cc: pgsql-performance@lists.postgresql.org

Re: Parallel Query

Em 13/11/2019 19:08, Jeff Janes escreveu: On Wed, Nov 13, 2019 at 3:59 PM Luís Roberto Weck mailto:luisrobe...@siscobra.com.br>> wrote: Indeed, reducing the costs made the query run in parallel, but the improvement in speed was not worth the cost (CPU). Could you show the pl

Re: Parallel Query

Em 13/11/2019 17:40, Jeff Janes escreveu: On Wed, Nov 13, 2019 at 3:11 PM Luís Roberto Weck mailto:luisrobe...@siscobra.com.br>> wrote: Hi! Is there a reason query 3 can't use parallel workers? Using q1 and q2 they seem very similar but can use up to 4 workers to

Re: Parallel Query

Em 13/11/2019 17:47, Tomas Vondra escreveu: On Wed, Nov 13, 2019 at 05:16:44PM -0300, Luís Roberto Weck wrote: Hi! Is there a reason query 3 can't use parallel workers? Using q1 and q2 they seem very similar but can use up to 4 workers to run faster: q1: https://pastebin.com/ufk

Parallel Query

Hi! Is there a reason query 3 can't use parallel workers? Using q1 and q2 they seem very similar but can use up to 4 workers to run faster: q1: https://pastebin.com/ufkbSmfB q2: https://pastebin.com/Yt32zRNX q3: https://pastebin.com/dqh7yKPb The sort node on q3 takes almost 12 seconds, making

Re: Slow query on V12.

Em 23/09/2019 16:44, Tom Lane escreveu: =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= writes: This is the query that is actually slow: -- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT table_schema, table_name,    n_live_tup::numeric as est_rows,    pg_table_size(relid)::numeric as table_si

Re: Slow query on V12.

Em 23/09/2019 16:03, Luís Roberto Weck escreveu: Em 23/09/2019 15:43, nikhil raj escreveu: Hi, Can you check by vacuum analyze  the database. And run the query. **Remember don't  use Vacuum full. On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, mailto:luisrobe...@siscobra.com.br>

Re: Slow query on V12.

Em 23/09/2019 15:43, nikhil raj escreveu: Hi, Can you check by vacuum analyze  the database. And run the query. **Remember don't  use Vacuum full. On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, mailto:luisrobe...@siscobra.com.br>> wrote: Hi! Recently I've been l

Slow query on V12.

Hi! Recently I've been looking for bloat in my databases and found a query to show which tables are more bloated and by how much. This is the explain plan on v12.3: https://explain.depesz.com/s/8dW8C And this is with v11: https://explain.depesz.com/s/diXY Both databases have approx. the same

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

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: 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. Regards

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

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

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

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: https://expl

Re: Erratically behaving query needs optimization

6s to run, how the hell can I get it to behave the same every time? After I added the index you suggested, it was fine for a while, next morning the run time exploded back to several seconds per query... and it oscillates. On Wed, Aug 21, 2019 at 2:25 PM Luís Roberto Weck wrote: Em 21/08/2019 04

Re: Erratically behaving query needs optimization

oved by Filter: 0 Buffers: shared hit=209871 Planning Time: 2.327 ms Execution Time: 618.935 ms On Tue, Aug 20, 2019 at 5:54 PM Luís Roberto Weck wrote: Em 20/08/2019 10:54, Barbu Paul - Gheorghe escreveu: Hello, I'm running "PostgreSQL 11.2, compiled by

Re: Erratically behaving query needs optimization

Em 20/08/2019 10:54, Barbu Paul - Gheorghe escreveu: Hello, I'm running "PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit" and I have a query that runs several times per user action (9-10 times). The query takes a long time to execute, specially at first, due to cold caches I think, but

Re: Last event per user

On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck mailto:luisrobe...@siscobra.com.br>> wrote: If you modify last_user_event_2 to select user and event info in the view, and just put there where clause directly on the view which is not joined to anything, instead of on the

Re: Last event per user

> If you modify last_user_event_2 to select user and event info in the view, > and just put there where clause directly on the view which is not joined to > anything, instead of on the "extra copy" of the users table like you were > showing previously, I would expect that the performance should

Re:

> It seems like it should be- > > SELECT * FROM users u JOIN last_user_event_1 e USING (user_id,user_group); > --OR-- > SELECT * FROM last_user_event_2 e; > > for them to produce the same result set, since the last_user_event_2 already > (could) have users info in it very simply by select * i

Re: Last event per user

> The obfuscation makes it difficult to guess at the query you are writing and > the schema you are using. Can you provide any additional information without > revealing sensitive info? > > 1) Do you have an index on ( user_id ASC, timestamp_inc DESC ) ? > 2) Sub-queries can't be re-written inl

Last event per user

Hey guys, So I have two tables: users and events. It is very common for my application to request the last user event. Usually, what I'll do is get the user, and then SELECT * from events WHERE user_id = :user order by timestamp_inc desc LIMIT 1. I have a big problem, however: My app uses