Re: JSON down performacen when id:1

[Render Comunicacion S.L.]

Hi Tom

Thanks for your quick answer.

I did not mention that the index for all tables is:

CREATE INDEX IF NOT EXISTS matrix_relations_idx
ON public.matrix USING gin
((datos #> '{relations}') jsonb_path_ops)  TABLESPACE pg_default;

And we try with and without jsonb_path_ops option with similar results.

My question is about, what is the difference between the first 3 searches and 
the > 4 search? 
We don't know why in the first 3 cases seems that PostgreSQL doesn't use the 
index, and the result takes the same time with or without index, and the > 4, 
every number higher of 3, it works perfectly...

We are really desperate about this... 

Thanks in avance.

Best
Alex
a...@render.es


657661974 · Denia 50, bajo izquierda · 46006 · Valencia





> On 16 Dec 2022, at 16:06, Tom Lane  wrote:
> 
> "Render Comunicacion S.L."  writes:
>> The issue:
>> When we search our locator with section_id: 1 (or any number < 4), 
>> PostgreSQL takes around 4, 5000, 8000ms or more.
>> When we search our locator with section_id: 4 (or any other bigger number), 
>> PostgreSQL takes around 100 ms. ( ~ expected time)
> 
> Your index is providing pretty awful performance:
> 
>>->  Bitmap Heap Scan on matrix  (cost=92.21..199.36 rows=27 
>> width=1144) (actual time=415.708..8325.296 rows=11 loops=1)
>>  Recheck Cond: ((datos #> '{relations}'::text[]) @> 
>> '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb)
>>  Rows Removed by Index Recheck: 272037
>>  Heap Blocks: exact=34164 lossy=33104
>>  ->  Bitmap Index Scan on matrix_relations_idx  
>> (cost=0.00..92.20 rows=27 width=0) (actual time=61.462..61.462 rows=155031 
>> loops=1)
>>Index Cond: ((datos #> '{relations}'::text[]) @> 
>> '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb)
> 
> I read that as 155K hits delivered by the index, of which only 11 were
> real matches.  To make matters worse, with so many hits the bitmap was
> allowed to become "lossy" (ie track some hits at page-level not
> tuple-level) to conserve memory, so that the executor actually had to
> check even more than 155K rows.
> 
> You need a better index.  It might be that switching to a jsonb_path_ops
> index would be enough to fix it, or you might need to build an expression
> index matched specifically to this type of query.  See
> 
> https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
> 
> Also, if any of the terminology there doesn't make sense, read
> 
> https://www.postgresql.org/docs/current/indexes.html
> 
>   regards, tom lane
> 
> 

RE: Postgres12 looking for possible HashAggregate issue workarounds?

[João Paulo Luís]

Thank you. It seems it is precisely that problem.

(I will discuss with the rest of the team upgrade possibilities, as I guess it 
will never be backported to the bugfixes of version 12.)

Meanwhile, as a one-time workaround I've disabled the hashagg algorithm,

SET enable_hashagg=off;

repeated the query, and it finished in 1h28m (and the RAM resident memory 
stayed just a little above the 16GB of shared_buffers).

Happy holidays!


João Luís

Senior Developer

joao.l...@pdmfc.com

+351 210 337 700


[https://dlnk.bio/wp-content/uploads/2022/11/assinaturaPDM-Natal-1-1.gif]
[https://www.pdmfc.com/images/email-signature/28-04.png] 
[https://www.pdmfc.com/images/email-signature/28-06.png] 
   
[https://www.pdmfc.com/images/email-signature/28-05.png] 
   
[https://www.pdmfc.com/images/email-signature/28-07.png] 
 
[https://www.pdmfc.com/images/email-signature/28-08.png] 





Confidentiality
The information in this message is confidential and privileged. It is intended 
solely for the addressee. If you are not the intended recipient, any 
disclosure, copying, or distribution of the message, or any action or omission 
taken by you in reliance on it is prohibited.
Please contact the sender immediately if you have received this message by 
mistake.
Thank you for your cooperation.


De: Justin Pryzby 
Enviado: 16 de dezembro de 2022 16:06
Para: João Paulo Luís 
Cc: pgsql-performance@lists.postgresql.org 

Assunto: Re: Postgres12 looking for possible HashAggregate issue workarounds?

[Não costuma receber e-mails de pry...@telsasoft.com. Saiba por que motivo isto 
é importante em https://aka.ms/LearnAboutSenderIdentification. ]

CAUTION: External E-mail


On Fri, Dec 16, 2022 at 03:24:17PM +, João Paulo Luís wrote:
> Hi! Sorry to post to this mailing list, but I could not find many tips 
> working around HashAggregate issues.
>
> In a research project involving text repetition analysis (on top of public 
> documents)
> I have a VirtualMachine (CPU AMD Epyc 7502P, 128GB RAM, 12TB HDD, 2TB SSD),
> running postgres 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1)
> and some tables with many rows:

> 1 - the query is making a postgresql project have 76.7 GB resident RAM usage.
> Having a WORK_MEM setting of 2GB (and "simple" COUNT() results),
> that was not expected.
> (I risk oom-killer killing my postgres as soon as I run another concurrent
> query.)

> The rows=261275 on HashAggregate  (cost=26397219.92..26399832.67 rows=261275 
> width=8) seems VERY WRONG!
> I was expecting something like rows=1.0E+09 instead.

> I would guess that HashAggregate is behaving very badly (using to much RAM 
> beyond WORK_MEM, amd also badly estimating the #rows and taking forever...)

Huge memory use sounds like what was fixed in postgres 13.

https://www.postgresql.org/docs/13/release-13.html

Allow hash aggregation to use disk storage for large aggregation result
sets (Jeff Davis)

Previously, hash aggregation was avoided if it was expected to use more
than work_mem memory. Now, a hash aggregation plan can be chosen despite
that. The hash table will be spilled to disk if it exceeds work_mem
times hash_mem_multiplier.

This behavior is normally preferable to the old behavior, in which once
hash aggregation had been chosen, the hash table would be kept in memory
no matter how large it got — which could be very large if the planner
had misestimated. If necessary, behavior similar to that can be obtained
by increasing hash_mem_multiplier.

--
Justin

Re: Postgres12 looking for possible HashAggregate issue workarounds?

[David Rowley]

On Sun, 18 Dec 2022 at 23:44, João Paulo Luís  wrote:
> Meanwhile, as a one-time workaround I've disabled the hashagg algorithm,

The way the query planner determines if Hash Aggregate's hash table
will fit in work_mem or not is based on the n_distinct estimate of the
columns being grouped on.  You may want to review what analyze set
n_distinct to on this table. That can be done by looking at:

select attname,n_distinct from pg_Stats where tablename =
'sentencesource' and attname = 'sentence';

If what that's set to does not seem realistic, then you can overwrite this with:

ALTER TABLE sentencesource ALTER COLUMN sentence SET (n_distinct = N);

Please see the paragraph in [1] about n_distinct.  Using an absolute
value is likely not a great idea if the table is going to grow. You
could maybe give it a better estimate about how many times values are
repeated by setting some negative value, as described in the
documents. You'll need to analyze the table again after changing this
setting.

David

[1] https://www.postgresql.org/docs/12/sql-altertable.html

temp_file_limit?

[Frits Jalvingh]

Hi list,

I have a misbehaving query which uses all available disk space and then
terminates with a "cannot write block" error. To prevent other processes
from running into trouble I've set the following:

temp_file_limit = 100GB

The query does parallelize and uses one parallel worker while executing,
but it does not abort when the temp file limit is reached:

345G pgsql_tmp

It does abort way later, after using around 300+ GB:
[53400] ERROR: temporary file size exceeds temp_file_limit (104857600kB)
Where: parallel worker
The comment in the file states that this is a per-session parameter, so
what is going wrong here?

I am using Postgres 14 on Ubuntu.

Regards,

Frits

Re: temp_file_limit?

[Justin Pryzby]

On Sun, Dec 18, 2022 at 12:48:03PM +0100, Frits Jalvingh wrote:
> Hi list,
> 
> I have a misbehaving query which uses all available disk space and then
> terminates with a "cannot write block" error. To prevent other processes
> from running into trouble I've set the following:
> 
> temp_file_limit = 100GB

> The comment in the file states that this is a per-session parameter, so
> what is going wrong here?

Do you mean the comment in postgresql.conf ?

commit d1f822e58 changed to say that temp_file_limit is actually
per-process and not per-session.

Could you send the query plan, preferably "explain analyze" (if the
query finishes sometimes) ?

log_temp_files may be helpful here.

> The query does parallelize and uses one parallel worker while executing,
> but it does not abort when the temp file limit is reached:
> 
> 345G pgsql_tmp
> 
> It does abort way later, after using around 300+ GB:
> [53400] ERROR: temporary file size exceeds temp_file_limit (104857600kB)
> Where: parallel worker

Are you sure the 345G are from only one instance of the query ?
Or is it running multiple times, or along with other queries writing
100GB of tempfiles.

It seems possible that it sometimes runs with more than one parallel
worker.  Also, are there old/stray tempfiles there which need to be
cleaned up?

-- 
Justin

Fwd: temp_file_limit?

[Frits Jalvingh]

Hi Justin, thanks for your help!

Simple things first:
- I am running a single query on a developer machine. Nothing else uses the
database at that point.
- The database runs on a disk that has 473GB in use and 1.3T still free. I
am watching the increase in size used (watch df -hl /d2).
- If I remove the temp_file_limit the query will run until it has used the
1.3TB that was free, then it dies.
- when it runs I see two PG processes active: a main and a worker process
for that main.

I  hope this answers some of the questions: yes, the query is the one using
the tempspace; it is the only one running; it uses only one parallel worker.

Just to be clear: my real question is: why is temp_file_limit not working
at the specified size? Because this is my real problem: when a query is
dying like this it will also kill other queries because these are also
running out of space. Even when the limit is per-process it should not have
exceeded 200GB imo. BTW, if that limit is really per process instead of per
session/query then that is a Very Bad Thing(tm), because this makes the
limit effectively worthless - if a query can spawn 8 parallel processes
then you can suddenly, without any form of control, again fill up that disk.

I'm not really asking for a solution to the bad performance, but hints are
always welcome so I'll include the requested info below:

With the failing plan the query never finishes; it just uses 1.3TB of
space, then dies.
This also means I cannot explain analyze as this does not produce output
when the query dies. This is a pretty terrible bug in my eyes, because you
cannot get the info when it's most needed. If I ever have time left to work
on Postgres' code this will be the first thing to fix 8-/

Anyway. The plan that fails badly is this one:
---
 Unique  (cost=37360.85..37360.86 rows=1 width=42)
   ->  Sort  (cost=37360.85..37360.85 rows=1 width=42)
 Sort Key: (COALESCE(tijd.tijdkey, 'Unknown'::character varying)),
s_h_eenheid_ssm.identificatie
 ->  Hash Join  (cost=34899.49..37360.84 rows=1 width=42)
   Hash Cond: ((ve03678.calender_id)::text =
(COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text)
   Join Filter: ((s_h_eenheid_ssm.dv_start_dts <=
tijd.einddatum) AND (s_h_eenheid_ssm.dv_end_dts > tijd.einddatum) AND
(l_eenheid_sturingslabel_ssm_pe.dv_start_dts <= tijd.einddatum) AND
(l_eenheid_sturingslabel_ssm_pe.dv_end_dts > tijd.einddatum) AND
(sturingslabel_pe.dv_start_dts <= tijd.einddatum) AND
(sturingslabel_pe.dv_end_dts > tijd.einddatum))
   ->  Gather  (cost=34897.66..37358.98 rows=1 width=65)
 Workers Planned: 1
 ->  Parallel Hash Join  (cost=33897.66..36358.88
rows=1 width=65)
   Hash Cond: ((s_h_eenheid_ssm.id_h_eenheid =
l_eenheid_sturingslabel_ssm_pe.id_h_eenheid) AND
(COALESCE(s_h_eenheid_ssm.id_s, '-1'::integer) = ve03678.eenheid_id))
   ->  Parallel Seq Scan on s_h_eenheid_ssm
 (cost=0.00..2326.55 rows=17955 width=34)
   ->  Parallel Hash  (cost=33896.02..33896.02
rows=109 width=47)
 ->  Parallel Hash Join
 (cost=18850.80..33896.02 rows=109 width=47)
   Hash Cond: (ve03678.ve03678 =
sturingslabel_pe.datum)
   ->  Parallel Seq Scan on ve0367801
ve03678  (cost=0.00..12584.92 rows=655792 width=15)
   ->  Parallel Hash
 (cost=18850.78..18850.78 rows=1 width=40)
 ->  Parallel Hash Join
 (cost=15458.27..18850.78 rows=1 width=40)
   Hash Cond:
(l_eenheid_sturingslabel_ssm_pe.id_h_sturingslabel =
sturingslabel_pe.id_h_sturingslabel)
   ->  Parallel Seq Scan on
l_eenheid_sturingslabel_ssm l_eenheid_sturingslabel_ssm_pe
 (cost=0.00..2963.36 rows=114436 width=24)
   ->  Parallel Hash
 (cost=15458.26..15458.26 rows=1 width=24)
 ->  Parallel Seq
Scan on s_h_sturingslabel_ssm sturingslabel_pe  (cost=0.00..15458.26 rows=1
width=24)
   Filter:
((soort = 'MSL'::text) AND (code = 'DAE'::text))
   ->  Hash  (cost=1.37..1.37 rows=37 width=11)
 ->  Seq Scan on tijd  (cost=0.00..1.37 rows=37
width=11)
(24 rows)
https://explain.depesz.com/s/qwsh

By itself I'm used to bad query performance in Postgresql; our

Re: Fwd: temp_file_limit?

[Tom Lane]

Frits Jalvingh  writes:
> Just to be clear: my real question is: why is temp_file_limit not working
> at the specified size?

I've not looked at that code lately, but I strongly suspect that
it's implemented in such a way that it's a per-process limit, not a
per-session limit.  So each parallel worker could use up that much
space.

It's also possible that you've found an actual bug, but without
a reproducer case nobody's going to take that possibility too
seriously.  We're unlikely to accept "the limit should work
across multiple processes" as a valid bug though.  That would
require a vastly more complicated implementation.

regards, tom lane

Re: Fwd: temp_file_limit?

[Justin Pryzby]

On Sun, Dec 18, 2022 at 06:29:41PM +0100, Frits Jalvingh wrote:
> Just to be clear: my real question is: why is temp_file_limit not
> working at the specified size? Because this is my real problem: when a
> query is dying like this it will also kill other queries because these
> are also running out of space. Even when the limit is per-process it
> should not have exceeded 200GB imo.

What OS and filesystem are in use ?

Could you list the tmpdir when it's getting huge?  The filenames include
the PID, which would indicate if there's another procecss involved, or a
bug allowed it to get huge.
sudo du --max=2 -mx ./pgsql_tmp |sort -nr

BTW, pg_ls_tmpdir() hides directories, so you shouldn't rely on it for
listing temporary directories...

One possibility is that there are files in the tmpdir, which have been
unlinked, but are still opened, so their space hasn't been reclaimed.
You could check for that by running lsof -nn |grep pgsql_tmp Any deleted
files would say things like 'DEL|deleted|inode|no such'

> BTW, if that limit is really per process instead of per
> session/query then that is a Very Bad Thing(tm), because this makes the
> limit effectively worthless - if a query can spawn 8 parallel processes
> then you can suddenly, without any form of control, again fill up that disk.

8 is the default value of max_worker_processes and max_parallel_workers,
but 2 is the default value of max_parallel_workers_per_gather.  You're
free the change the default value to balance it with the temp_file_limit
(as suggested by the earlier-mentioned commit).

-- 
Justin

Re: Fwd: temp_file_limit?

[Thomas Munro]

On Mon, Dec 19, 2022 at 9:11 AM Justin Pryzby  wrote:
> On Sun, Dec 18, 2022 at 06:29:41PM +0100, Frits Jalvingh wrote:
> > Just to be clear: my real question is: why is temp_file_limit not
> > working at the specified size? Because this is my real problem: when a
> > query is dying like this it will also kill other queries because these
> > are also running out of space. Even when the limit is per-process it
> > should not have exceeded 200GB imo.

It's really the limit for a single file (or virtual file because we
split them on 1GB boundaries, probably well past time we stopped doing
that), but we create many temporary files for various reasons.  One
possibility is that you've hit a case that needs several rounds of
repartitioning (because of a failure to estimate the number of tuples
well), but we can't see that because you didn't show EXPLAIN (ANALYZE)
output (understandably if it runs out of disk space before
completing...).  The parallel hash code doesn't free up the previous
generations' temporary files; it really only needs two generations'
worth concurrently (the one it's reading from and the one it's writing
to).  In rare cases where more generations are needed it could unlink
the older ones -- that hasn't been implemented yet.  If you set
log_temp_files = 0 to log temporary file names, it should be clear if
it's going through multiple rounds of repartitioning, from the names
(...of32..., ...of64..., ...of128..., ...of256..., ...).

Re: Fwd: temp_file_limit?

[Thomas Munro]

On Mon, Dec 19, 2022 at 1:51 PM Thomas Munro  wrote:
> It's really the limit for a single file

Oops, sorry I take that back.  It should be per process.