Re: Query slow for new participants

[MichaelDBA]

Regarding shared_buffers, please install the pg_buffercache extension 
and run the recommended queries with that extension during high load 
times to really get an idea about the right value for shared_buffers.  
Let's take the guess work out of it.


Regards,
Michael Vitale


Justin Pryzby 
Monday, February 25, 2019 6:59 PM
On Tue, Feb 26, 2019 at 12:22:39AM +0100, supp...@mekong.be wrote:


Hardware
Standard DS15 v2 (20 vcpus, 140 GB memory)



"effective_cache_size" "105GB" "configuration file"
"effective_io_concurrency" "200" "configuration file"
"maintenance_work_mem" "2GB" "configuration file"
"max_parallel_workers" "20" "configuration file"
"max_parallel_workers_per_gather" "10" "configuration file"
"max_worker_processes" "20" "configuration file"
"random_page_cost" "1.1" "configuration file"
"shared_buffers" "35GB" "configuration file"
"work_mem" "18350kB" "configuration file"


I don't know for sure, but 35GB is very possibly too large shared_buffers.  The
rule of thumb is "start at 25% of RAM" but I think anything over 10-15GB is
frequently too large, unless you can keep the whole DB in RAM (can you?)


Table Metadata
relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, 
reloptions, pg_table_size(oid)
"companyarticledb" 6191886 "5.40276e+08" 6188459 "r" 44 false "50737979392"


work_mem could probably benefit from being larger (just be careful that you
don't end up with 20x parallel workers running complex plans each node of which
using 100MB work_mem).


Full Table and Index Schema
The difference is very bad for the new company,  even on the simplest query

SELECT * FROM CompanyArticleDB
  WHERE CompanyId = '77'
  AND ArticleId= '7869071'


It sounds to me like the planner thinks that the distribution of companyID and
articleID are independent, when they're not.  For example it think that
companyID=33 filters out 99% of the rows.


  companyid  | integer |   | not 
null |
  articleid  | integer |   | not 
null |



EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
   SELECT * FROM CompanyArticleDB
 WHERE CompanyId = '77'
 AND ArticleId= '7869071'
"Index Scan using ix_companyarticledb_company on companyarticledb (cost=0.57..2.80 
rows=1 width=193) (actual time=1011.335..1011.454 rows=1 loops=1)"
"  Index Cond: (companyid = 77)"
"  Filter: (articleid = 7869071)"
"  Rows Removed by Filter: 2674361"
"  Buffers: shared hit=30287"



Example for another participant, there another index is used.
"Index Scan using pk_pricedb on companyarticledb  (cost=0.57..2.79 rows=1 width=193) 
(actual time=0.038..0.039 rows=0 loops=1)"
"  Index Cond: ((companyid = 39) AND (articleid = 7869071))"
"  Buffers: shared hit=4"



I do not know why this participant is different than the others except that
it was recently added.


Were the tables ANALYZEd since then ?  You could check:
SELECT * FROM pg_stat_user_tables WHERE relname='companyarticledb';

If you have small number of companyIDs (~100), then the table statistics may
incldue a most-common-values list, and companies not in the MCV list may end up
with different query plans, even without correlation issues.

It looks like the NEW company has ~3e6 articles, out of a total ~5e8 articles.
The planner may think that companyID doesn't exist at all, so scanning the idx
on companyID will be slightly faster than using the larger, composite index on
companyID,articleID.

Justin


Indexes:
 "pk_pricedb" PRIMARY KEY, btree (companyid, articleid)
 "EnabledIndex" btree (enabled)
 "ix_companyarticledb_article" btree (articleid)
 "ix_companyarticledb_company" btree (companyid)
 "participantarticlecodeindex" btree (articlecode)
 "participantdescriptionindex" gin (participantdescription gin_trgm_ops)
Foreign-key constraints:
 "fk_companyarticledb_accountsdb" FOREIGN KEY (modifiedby) REFERENCES 
accountsdb(id)
 "fk_companyarticledb_accountsdb1" FOREIGN KEY (createdby) REFERENCES 
accountsdb(id)
 "fk_companyarticledb_accountsdb2" FOREIGN KEY (preventioncounselorid) 
REFERENCES accountsdb(id)
 "fk_companyarticledb_articledb" FOREIGN KEY (articleid) REFERENCES 
articledb(id)
 "fk_companyarticledb_companydb" FOREIGN KEY (companyid) REFERENCES 
companydb(id)
 "fk_companyarticledb_interfaceaccountdb" FOREIGN KEY (interfaceaccountid) 
REFERENCES interfaceaccountdb(id)
 "fk_companyarticledb_supplieraccountdb" FOREIGN KEY (createdbysupplier) 
REFERENCES supplieraccountdb(id)
 "fk_companyarticledb_supplieraccountdb1" FOREIGN KEY (modifiedbysupplier) 
REFERENCES supplieraccountdb(id)

Re: Query slow for new participants

[Justin Pryzby]

On Tue, Feb 26, 2019 at 12:22:39AM +0100, supp...@mekong.be wrote:

> Hardware
> Standard DS15 v2 (20 vcpus, 140 GB memory)

> "effective_cache_size" "105GB" "configuration file"
> "effective_io_concurrency" "200" "configuration file"
> "maintenance_work_mem" "2GB" "configuration file"
> "max_parallel_workers" "20" "configuration file"
> "max_parallel_workers_per_gather" "10" "configuration file"
> "max_worker_processes" "20" "configuration file"
> "random_page_cost" "1.1" "configuration file"
> "shared_buffers" "35GB" "configuration file"
> "work_mem" "18350kB" "configuration file"

I don't know for sure, but 35GB is very possibly too large shared_buffers.  The
rule of thumb is "start at 25% of RAM" but I think anything over 10-15GB is
frequently too large, unless you can keep the whole DB in RAM (can you?)

> Table Metadata
> relname, relpages, reltuples, relallvisible, relkind, relnatts, 
> relhassubclass, reloptions, pg_table_size(oid)
> "companyarticledb" 6191886 "5.40276e+08" 6188459 "r" 44 false "50737979392"

work_mem could probably benefit from being larger (just be careful that you
don't end up with 20x parallel workers running complex plans each node of which
using 100MB work_mem).

> Full Table and Index Schema
> The difference is very bad for the new company,  even on the simplest query
> 
>SELECT * FROM CompanyArticleDB
>  WHERE CompanyId = '77'
>  AND ArticleId= '7869071'

It sounds to me like the planner thinks that the distribution of companyID and
articleID are independent, when they're not.  For example it think that
companyID=33 filters out 99% of the rows.

>  companyid  | integer |   | not 
> null |
>  articleid  | integer |   | not 
> null |

> EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
>   SELECT * FROM CompanyArticleDB
> WHERE CompanyId = '77'
> AND ArticleId= '7869071'
> "Index Scan using ix_companyarticledb_company on companyarticledb 
> (cost=0.57..2.80 rows=1 width=193) (actual time=1011.335..1011.454 rows=1 
> loops=1)"
> "  Index Cond: (companyid = 77)"
> "  Filter: (articleid = 7869071)"
> "  Rows Removed by Filter: 2674361"
> "  Buffers: shared hit=30287"

> Example for another participant, there another index is used.
> "Index Scan using pk_pricedb on companyarticledb  (cost=0.57..2.79 rows=1 
> width=193) (actual time=0.038..0.039 rows=0 loops=1)"
> "  Index Cond: ((companyid = 39) AND (articleid = 7869071))"
> "  Buffers: shared hit=4"

> I do not know why this participant is different than the others except that
> it was recently added.

Were the tables ANALYZEd since then ?  You could check:
SELECT * FROM pg_stat_user_tables WHERE relname='companyarticledb';

If you have small number of companyIDs (~100), then the table statistics may
incldue a most-common-values list, and companies not in the MCV list may end up
with different query plans, even without correlation issues.

It looks like the NEW company has ~3e6 articles, out of a total ~5e8 articles.
The planner may think that companyID doesn't exist at all, so scanning the idx
on companyID will be slightly faster than using the larger, composite index on
companyID,articleID.

Justin

> Indexes:
> "pk_pricedb" PRIMARY KEY, btree (companyid, articleid)
> "EnabledIndex" btree (enabled)
> "ix_companyarticledb_article" btree (articleid)
> "ix_companyarticledb_company" btree (companyid)
> "participantarticlecodeindex" btree (articlecode)
> "participantdescriptionindex" gin (participantdescription gin_trgm_ops)
> Foreign-key constraints:
> "fk_companyarticledb_accountsdb" FOREIGN KEY (modifiedby) REFERENCES 
> accountsdb(id)
> "fk_companyarticledb_accountsdb1" FOREIGN KEY (createdby) REFERENCES 
> accountsdb(id)
> "fk_companyarticledb_accountsdb2" FOREIGN KEY (preventioncounselorid) 
> REFERENCES accountsdb(id)
> "fk_companyarticledb_articledb" FOREIGN KEY (articleid) REFERENCES 
> articledb(id)
> "fk_companyarticledb_companydb" FOREIGN KEY (companyid) REFERENCES 
> companydb(id)
> "fk_companyarticledb_interfaceaccountdb" FOREIGN KEY (interfaceaccountid) 
> REFERENCES interfaceaccountdb(id)
> "fk_companyarticledb_supplieraccountdb" FOREIGN KEY (createdbysupplier) 
> REFERENCES supplieraccountdb(id)
> "fk_companyarticledb_supplieraccountdb1" FOREIGN KEY (modifiedbysupplier) 
> REFERENCES supplieraccountdb(id)

Re: Query slow for new participants

[supp...@mekong.be]

Hi,

thank you for your reply.
Yes, I will go through this page.

Regards,
Kim

Op ma 25 feb. 2019 om 17:16 schreef Justin Pryzby :

> On Mon, Feb 25, 2019 at 03:41:18AM -0700, Kim wrote:
> > Is there any way how I can make the queries fast for new participants?
> This
> > is a big problem, because for new participants, speed is even more
> > important.
> >
> > Thank you for your help.
>
> Could you include information requested here ?
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> Justin
>


-- 
Met vriendelijke groeten,

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

[MichaelDBA]

Was wondering when that would come up, taking queuing logic outside the 
database.  Can be overly painful architecting queuing logic in 
relational databases. imho.


Regards,
Michael Vitale


Jeff Janes 
Monday, February 25, 2019 3:30 PM
On Sat, Feb 23, 2019 at 4:06 PM Gunther > wrote:


Hi,

I am using an SQL queue for distributing work to massively
parallel workers.

You should look into specialized queueing software.

...

I figured I might just pause all workers briefly to schedule the
REINDEX Queue command, but the problem with this is that while the
transaction volume is large, some jobs may take minutes to
process, and in that case we need to wait minutes to quiet the
database with then 47 workers sitting as idle capacity waiting for
the 48th to finish so that the index can be rebuilt!

The jobs that take minutes are themselves the problem.  They prevent 
tuples from being cleaned up, meaning all the other jobs needs to 
grovel through the detritus every time they need to claim a new row.  
If you got those long running jobs to end, you probably wouldn't even 
need to reindex--the problem would go away on its own as the 
dead-to-all tuples get cleaned up.


Locking a tuple and leaving the transaction open for minutes is going 
to cause no end of trouble on a highly active system.  You should look 
at a three-state method where the tuple can be 
pending/claimed/finished, rather than pending/locked/finished.  That 
way the process commits immediately after claiming the tuple, and then 
records the outcome in another transaction once it is done 
processing.  You will need a way to detect processes that failed after 
claiming a row but before finishing, but implementing that is going to 
be easier than all of this re-indexing stuff you are trying to do 
now.  You would claim the row by updating a field in it to have 
something distinctive about the process, like its hostname and pid, so 
you can figure out if it is still running when it comes time to clean 
up apparently forgotten entries.


Cheers,

Jeff

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

[Jeff Janes]

On Mon, Feb 25, 2019 at 11:13 AM Gunther Schadow 
wrote:

> Anyway, I think the partitioned table is the right and brilliant solution,
> because an index really isn't required. The actual pending partition will
> always remain quite small, and being a queue, it doesn't even matter how
> big it might grow, as long as new rows are inserted at the end and not in
> the middle of the data file and still there be some way of fast skip over
> the part of the dead rows at the beginning that have already been processed
> and moved away.
>
Why do you want to do that?  If you are trying to force the queue to be
handled in a "fair" order, then this isn't the way to do it, you would want
to add an "ORDER BY" to your dequeueing query (in which case you are
probably back to adding an index).

Once the space in the beginning of the table has been reclaimed as free,
then it will be reused for newly inserted tuples.  After the space is freed
up but before it is reused, the seq scan can't skip those blocks entirely,
but it can deal with the blocks quickly because they are empty.  If the
blocks are full of dead but not freed tuples (because the long-running
transactions are preventing them from being cleaned up) then it will have
to go through each dead tuple to satisfy itself that it actually is dead.
This might not be as bad as it is for indexes, but certainly won't be good
for performance.

 Cheers,

Jeff

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

[Jeff Janes]

On Sat, Feb 23, 2019 at 4:06 PM Gunther  wrote:

> Hi,
>
> I am using an SQL queue for distributing work to massively parallel
> workers.
>
You should look into specialized queueing software.

...

> I figured I might just pause all workers briefly to schedule the REINDEX
> Queue command, but the problem with this is that while the transaction
> volume is large, some jobs may take minutes to process, and in that case we
> need to wait minutes to quiet the database with then 47 workers sitting as
> idle capacity waiting for the 48th to finish so that the index can be
> rebuilt!
>
The jobs that take minutes are themselves the problem.  They prevent tuples
from being cleaned up, meaning all the other jobs needs to grovel through
the detritus every time they need to claim a new row.  If you got those
long running jobs to end, you probably wouldn't even need to reindex--the
problem would go away on its own as the dead-to-all tuples get cleaned up.

Locking a tuple and leaving the transaction open for minutes is going to
cause no end of trouble on a highly active system.  You should look at a
three-state method where the tuple can be pending/claimed/finished, rather
than pending/locked/finished.  That way the process commits immediately
after claiming the tuple, and then records the outcome in another
transaction once it is done processing.  You will need a way to detect
processes that failed after claiming a row but before finishing, but
implementing that is going to be easier than all of this re-indexing stuff
you are trying to do now.  You would claim the row by updating a field in
it to have something distinctive about the process, like its hostname and
pid, so you can figure out if it is still running when it comes time to
clean up apparently forgotten entries.

Cheers,

Jeff

Re: Aggregate and many LEFT JOIN

[Michael Lewis]

On Mon, Feb 25, 2019 at 2:44 AM kimaidou  wrote:

> I have better results with this version. Basically, I run a first query
> only made for aggregation, and then do a JOIN to get other needed data.
>
> * SQL : http://paste.debian.net/1070007/
> * EXPLAIN: https://explain.depesz.com/s/D0l
>
> Not really "fast", but I gained 30%
>


It still seems that disk sort and everything after that is where the query
plan dies. It seems odd that it went to disk if work_mem was already 250MB.
Can you allocate more as a test? As an alternative, if this is a frequently
needed data, can you aggregate this data and keep a summarized copy updated
periodically?

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

[Corey Huinker]

>
>
> Anyway, I think the partitioned table is the right and brilliant solution,
> because an index really isn't required. The actual pending partition will
> always remain quite small, and being a queue, it doesn't even matter how
> big it might grow, as long as new rows are inserted at the end and not in
> the middle of the data file and still there be some way of fast skip over
> the part of the dead rows at the beginning that have already been processed
> and moved away.
>
> Good thing is, I don't worry about maintenance window.  I have the leisure
> to simply tear down my design now and make a better design. What's 2
> million transactions if I can re-process them at a rate of 80/s? 7 hours
> max. I am still in development. So, no need to worry about migration /
> transition acrobatics. So if I take Corey's steps and envision the final
> result, not worrying about the transition steps, then I understand this:
>
> 1. Create the Queue table partitioned on the pending column, this creates
> the partition with the pending jobs (on which I set the fillfactor kind of
> low, maybe 50) and the default partition with all the rest. Of course that
> allows people with a constant transaction volume to also partition on jobId
> or completionTime and move chunks out to cold archive storage. But that's
> beside the current point.
>
I'm guessing there's a fairly insignificant difference in performance
between one true partition and one false partition vs one true partition
and a default partition, but I don't have insight into which one is
better.

>
> One question I have though: I imagine our pending partition heap file to
> now be essentially sequentially organized as a queue. New jobs are appended
> at the end, old jobs are at the beginning. As pending jobs become completed
> (pending = false) these initial rows will be marked as dead. So, while the
> number of live rows will remain small in that pending partition, sequential
> scans will have to skip over the dead rows in the beginning.
>

That's basically true, but vacuums are erasing deleted rows, and that space
gets re-used. So the table builds up to a working-set size, and I envision
it looking like a clock sweep, where your existing rows are at 11pm to 7pm,
your new rows are inserting into space at 8pm that was vacuumed clean a
while ago, and 9pm and 10pm have deleted rows that haven't been vacuumed
yet. Where the empty spot is just keeps cycling through the table.

Of course vacuum eliminates dead rows, but unless I do vacuum full, it will
> not re-pack the live rows, and that requires an exclusive table lock. So,
> what is the benefit of vacuuming that pending partition? What I *don't*
> want is insertion of new jobs to go into open slots at the beginning of the
> file. I want them to be appended (in Oracle there is an INSERT /*+APPEND*/
> hint for that. How does that work in PostgreSQL?
>

See above, the db (tries to) reuse the space space before new space is
allocated.

I don't know of an append equivalent for pgsql. If memory servers, the big
win of /*+ APPEND */ was that raw data blocks were assembled out-of-band
and then just written to disk.


> Ultimately that partition will amass too many dead rows, then what do I
> do? I don't think that the OS has a way to truncate files physically from
> the head, does it? I guess it could set the file pointer from the first
> block to a later block. But I don't know of an IOCTL/FCNTL command for
> that. On some OS there is a way of making blocks sparse again, is that how
> PostgreSQL might do it? Just knock out blocks as sparse from the front of
> the file?
>

See clock sweep analogy above.

Re: Query slow for new participants

[Justin Pryzby]

On Mon, Feb 25, 2019 at 03:41:18AM -0700, Kim wrote:
> Is there any way how I can make the queries fast for new participants? This
> is a big problem, because for new participants, speed is even more
> important.
> 
> Thank you for your help.

Could you include information requested here ?
https://wiki.postgresql.org/wiki/Slow_Query_Questions

Justin

Query slow for new participants

[Kim]

Hello,

I have an article query which returns articles enabled for a participant.
Article table – Participant table – Table in between which stores the links
between the Article and particitpant including characteristics such as
enabled.
It is possible to search on the articles by number, description,…
For all of my participants, the articles are return in up to 3 seconds.
However, when I add a new participant, which has in fact very few articles
enabled, the query takes up to 30 seconds.
When running analyse explain, I can see that the execution plan for all
participants uses indexes and joins the table in the same order.
For the new participant, also indexes are used, but the tables are joined in
a different order which makes the query very slow.
Is there any way how I can make the queries fast for new participants? This
is a big problem, because for new participants, speed is even more
important.

Thank you for your help.




--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

[Gunther Schadow]

Wow, yes, partition instead of index, that is interesting. Thanks Corey 
and Justin.


The index isn't required at all if all my pending jobs are in a 
partition of only pending jobs. In that case the plan can just be a 
sequential scan.


And Jeff James, sorry, I failed to show the LIMIT 1 clause on my dequeue 
query. That was an omission. My query is actually somewhat more complex 
and I just translated it down to the essentials but forgot the LIMIT 1 
clause.


SELECT seqNo, action
  FROM Queue
 WHERE pending
   AND/... other criteria .../
 LIMIT 1
 FOR UPDATE SKIP LOCKED;

And sorry I didn't capture the stats for vacuum verbose. And they would 
be confusing because there are other things involved.


Anyway, I think the partitioned table is the right and brilliant 
solution, because an index really isn't required. The actual pending 
partition will always remain quite small, and being a queue, it doesn't 
even matter how big it might grow, as long as new rows are inserted at 
the end and not in the middle of the data file and still there be some 
way of fast skip over the part of the dead rows at the beginning that 
have already been processed and moved away.


Good thing is, I don't worry about maintenance window.  I have the 
leisure to simply tear down my design now and make a better design. 
What's 2 million transactions if I can re-process them at a rate of 
80/s? 7 hours max. I am still in development. So, no need to worry about 
migration / transition acrobatics. So if I take Corey's steps and 
envision the final result, not worrying about the transition steps, then 
I understand this:


1. Create the Queue table partitioned on the pending column, this 
creates the partition with the pending jobs (on which I set the 
fillfactor kind of low, maybe 50) and the default partition with all the 
rest. Of course that allows people with a constant transaction volume to 
also partition on jobId or completionTime and move chunks out to cold 
archive storage. But that's beside the current point.


2. Add all needed indexes on the partitioned table, except the main 
partial index that I used before and that required all that reindexing 
maintenance. Actually I won't need any other indexes really, why invite 
another similar problem again.


That's really simple.

One question I have though: I imagine our pending partition heap file to 
now be essentially sequentially organized as a queue. New jobs are 
appended at the end, old jobs are at the beginning. As pending jobs 
become completed (pending = false) these initial rows will be marked as 
dead. So, while the number of live rows will remain small in that 
pending partition, sequential scans will have to skip over the dead rows 
in the beginning.


Does PostgreSQL structure its files such that skipping over dead rows is 
fast? Or do the dead rows have to be read and discarded during a table 
scan?


Of course vacuum eliminates dead rows, but unless I do vacuum full, it 
will not re-pack the live rows, and that requires an exclusive table 
lock. So, what is the benefit of vacuuming that pending partition? What 
I _/don't/_ want is insertion of new jobs to go into open slots at the 
beginning of the file. I want them to be appended (in Oracle there is an 
INSERT /*+APPEND*/ hint for that. How does that work in PostgreSQL?


Ultimately that partition will amass too many dead rows, then what do I 
do? I don't think that the OS has a way to truncate files physically 
from the head, does it? I guess it could set the file pointer from the 
first block to a later block. But I don't know of an IOCTL/FCNTL command 
for that. On some OS there is a way of making blocks sparse again, is 
that how PostgreSQL might do it? Just knock out blocks as sparse from 
the front of the file?


If not, the next thing I can think of is to partition the table further 
by time, may be alternating even and odd days, such that on any given 
day one of the two pending partitions are quiet? Is that how it's done?


regards,
-Gunther

Re: Idle backends outside a transaction holding onto large amounts of memory / swap space?

[Pavel Stehule]

Hi

po 25. 2. 2019 v 11:37 odesílatel Tobias Gierke <
tobias.gie...@code-sourcery.de> napsal:

> Hi,
>
> Recently we started seeing the Linux OOM killer kicking in and killing
> PostgreSQL processes on one of our development machines.
>
> The PostgreSQL version we're using was compiled by us, is running on
> CentOS 7 and is
>
> PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-28), 64-bit
>
> While looking at the machine I saw the following peculiar thing: Swap is
> almost completely full while buff/cache still has ~3GB available.
>
> root@demo:/etc/systemd/system # free -m
>totalusedfree  shared buff/cache
> available
> Mem:   78203932 7701917 31161548
> Swap:  40953627 468
>
> Running the following one-liner shows that two PostgreSQL processes are
> using most of the swap:
>
> for proc in /proc/*;   do echo $proc ; cat $proc/smaps 2>/dev/null | awk
> '/Swap/{swap+=$2}END{print swap "\tKB\t'`echo $proc|awk '{print $1}' `'"
> }'; done | sort -n | awk '{total+=$1}/[0-9]/;END{print total
> "\tKB\tTotal"}'
>
> 1387496 KB  /proc/22788
> 1837872 KB  /proc/22789
>
> I attached the memory mappings of these processes to the mail. Both
> processes inside PostgreSQL show up as idle outside of any transaction
> and belong to a JDBC (Java) connection pool.
>

Is good to close sessions after some times (once per hour) because
allocated memory is released to operation system when process is closed.
Without it, the operation memory can be fragmented.

if run some big queries then some memory can be assigned to process, and is
not released.

Regards

Pavel


> voip=# select * from pg_stat_activity where pid in (22788,22789);
> -[ RECORD 1 ]+--
> datid| 16404
> pid  | 22789
> usesysid | 10
> usename  | postgres
> client_addr  | 127.0.0.1
> client_hostname  |
> client_port  | 45649
> backend_start| 2019-02-25 00:17:15.246625+01
> xact_start   |
> query_start  | 2019-02-25 10:52:07.729096+01
> state_change | 2019-02-25 10:52:07.748077+01
> wait_event_type  | Client
> wait_event   | ClientRead
> state| idle
> backend_xid  |
> backend_xmin |
> query| COMMIT
> backend_type | client backend
> -[ RECORD 2 ]+--
> datid| 16404
> pid  | 22788
> usesysid | 10
> usename  | postgres
> client_addr  | 127.0.0.1
> client_hostname  |
> client_port  | 45648
> backend_start| 2019-02-25 00:17:15.24631+01
> xact_start   |
> query_start  | 2019-02-25 10:55:42.577158+01
> state_change | 2019-02-25 10:55:42.577218+01
> wait_event_type  | Client
> wait_event   | ClientRead
> state| idle
> backend_xid  |
> backend_xmin |
> query| ROLLBACK
> backend_type | client backend
>
> ->8-->8-->8-->8-
>
> I attached the postgresql.conf we're using to this mail as well.
>
> Is this expected behaviour ? Did we over-commission the machine in our
> postgresql.conf ?
>
> Thanks,
> Tobias
>
>
>
>
>
>

Re: Aggregate and many LEFT JOIN

[kimaidou]

I have better results with this version. Basically, I run a first query
only made for aggregation, and then do a JOIN to get other needed data.

* SQL : http://paste.debian.net/1070007/
* EXPLAIN: https://explain.depesz.com/s/D0l

Not really "fast", but I gained 30%

Le lun. 25 févr. 2019 à 09:54, kimaidou  a écrit :

> Thanks for your answers. I tried with
> > set session work_mem='250MB';
> > set session geqo_threshold = 20;
> > set session join_collapse_limit = 20;
>
> It seems to have no real impact :
> https://explain.depesz.com/s/CBVd
>
> Indeed an index cannot really be used for sorting here, based on the
> complexity of the returned fields.
> Wich strikes me is that if I try to simplify it a lot, removing all data
> but the main table (occtax.observation) primary key cd_nom and aggregate,
> the query plan should be able tu use the cd_nom index for sorting and
> provide better query plan (hash aggregate), but it does not seems so :
>
> * SQL ; http://paste.debian.net/hidden/c3ee7889/
> * EXPLAIN : https://explain.depesz.com/s/FR3h -> a group aggregate is
> used, which : GroupAggregate 1 10,639.313 ms 72.6 %
>
> It is better, but I think 10s for such a query seems bad perf for me.
>
> Regards
> Michaël
>
> Le ven. 22 févr. 2019 à 19:06, Tom Lane  a écrit :
>
>> Michael Lewis  writes:
>> > Does the plan change significantly with this-
>> > set session work_mem='250MB';
>> > set session geqo_threshold = 20;
>> > set session join_collapse_limit = 20;
>>
>> Yeah ... by my count there are 16 tables in this query, so raising
>> join_collapse_limit to 15 is not enough to ensure that the planner
>> considers all join orders.  Whether use of GEQO is a big problem
>> is harder to say, but it might be.
>>
>> regards, tom lane
>>
>

Re: Aggregate and many LEFT JOIN

[kimaidou]

Thanks for your answers. I tried with
> set session work_mem='250MB';
> set session geqo_threshold = 20;
> set session join_collapse_limit = 20;

It seems to have no real impact :
https://explain.depesz.com/s/CBVd

Indeed an index cannot really be used for sorting here, based on the
complexity of the returned fields.
Wich strikes me is that if I try to simplify it a lot, removing all data
but the main table (occtax.observation) primary key cd_nom and aggregate,
the query plan should be able tu use the cd_nom index for sorting and
provide better query plan (hash aggregate), but it does not seems so :

* SQL ; http://paste.debian.net/hidden/c3ee7889/
* EXPLAIN : https://explain.depesz.com/s/FR3h -> a group aggregate is used,
which : GroupAggregate 1 10,639.313 ms 72.6 %

It is better, but I think 10s for such a query seems bad perf for me.

Regards
Michaël

Le ven. 22 févr. 2019 à 19:06, Tom Lane  a écrit :

> Michael Lewis  writes:
> > Does the plan change significantly with this-
> > set session work_mem='250MB';
> > set session geqo_threshold = 20;
> > set session join_collapse_limit = 20;
>
> Yeah ... by my count there are 16 tables in this query, so raising
> join_collapse_limit to 15 is not enough to ensure that the planner
> considers all join orders.  Whether use of GEQO is a big problem
> is harder to say, but it might be.
>
> regards, tom lane
>