Re: Batch insert heavily affecting query performance.

[MichaelDBA]

Yes it would/does make a difference!  When you do it with one connection 
you should see a big performance gain.  Delayed, granted, extend locks 
(locktype=extend) can happen due to many concurrent connections trying 
to insert into the same table at the same time. Each insert request 
results in an extend lock (8k extension), which blocks other writers. 
What normally happens is the these extend locks happen so fast that you 
hardly ever see them in the pg_locks table, except in the case where 
many concurrent connections are trying to do a lot of inserts into the 
same table. The following query will show if this is the case:


select * from pg_locks where granted = false and locktype = 'extend';


Jean Baro 
Sunday, December 24, 2017 7:09 PM
Multiple connections, but we are going to test it with only one. Would 
it make any difference?


Thanks



michael...@sqlexec.com 
Sunday, December 24, 2017 6:52 PM
Are the inserts being done through one connection or multiple 
connections concurrently?


Sent from my iPhone



Jean Baro 
Sunday, December 24, 2017 2:51 PM
Hi there,

We are testing a new application to try to find performance issues.

AWS RDS m4.large 500GB storage (SSD)

One table only, called Messages:

Uuid
Country  (ISO)
Role (Text)
User id  (Text)
GroupId (integer)
Channel (text)
Title (Text)
Payload (JSON, up to 20kb)
Starts_in (UTC)
Expires_in (UTC)
Seen (boolean)
Deleted (boolean)
LastUpdate (UTC)
Created_by (UTC)
Created_in (UTC)

Indexes:

UUID (PK)
UserID + Country (main index)
LastUpdate
GroupID


We inserted 160MM rows, around 2KB each. No partitioning.

Insert started at around  3.000 inserts per second, but (as expected) 
started to slow down as the number of rows increased.  In the end we 
got around 500 inserts per second.


Queries by Userd_ID + Country took less than 2 seconds, but while the 
batch insert was running the queries took over 20 seconds!!!


We had 20 Lambda getting messages from SQS and bulk inserting them 
into Postgresql.


The insert performance is important, but we would slow it down if 
needed in order to ensure a more flat query performance. (Below 2 
seconds). Each query (userId + country) returns around 100 diferent 
messages, which are filtered and order by the synchronous Lambda 
function. So we don't do any special filtering, sorting, ordering or 
full text search in Postgres. In some ways we use it more like a 
glorified file system. :)


We are going to limit the number of lambda workers to 1 or 2, and then 
run some queries concurrently to see if the query performance is not 
affect too much. We aim to get at least 50 queries per second 
(returning 100 messages each) under 2 seconds, even when there is 
millions of messages on SQS being inserted into PG.


We haven't done any performance tuning in the DB.

With all that said, the question is:

What can be done to ensure good query performance (UserID+ country) 
even when the bulk insert is running (low priority).


We are limited to use AWS RDS at the moment.

Cheers

Re: need advice to tune postgresql

[MichaelDBA]

What caught my eye is the update count can be up to 10K.  That means if 
autovacuum is not keeping up with this table, bloat may be increasing at 
a high pace leading to more page I/O which causes degraded performance.  
If the table has become bloated, you need to do a blocking VACUUM FULL 
on it or a non-blocking VACUUM using pg_repack.  Then tune autovacuum so 
that it can keep up with the updates to this table or add manual vacuum 
analyze on this table at certain times via a cron job. Manual vacuums 
(user-initiated) will not be bumped as with autovacuums that can be 
bumped due to user priority.


Regards,
Michael Vitale



Darius Pėža 
Friday, February 23, 2018 9:42 AM
 I have issue that update queries is slow, I need some advice how 
improve speed. I don't have much control to change queries. But I can 
change postresql server configuration


query example:

UPDATE "project_work" SET "left" = ("project_work"."left" + 2) WHERE 
("project_work"."left" >= 8366)


sometimes updated lines count is up to 10k

postgresql version 9.3

postgresl.conf
max_connections = 100
shared_buffers = 6GB# min 128kB
work_mem = 100MB# min 64kB

all other values are default

server hardware
Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz
16GB RAM
disk is HDD

about half of resource I can dedicate for postgresql server.

Re: PG 9.6 Slow inserts with long-lasting LWLocks

[MichaelDBA]

Sporadic insert slowness could be due to lock delays (locktype=extend) 
due to many concurrent connections trying to insert into the same table 
at the same time. Each insert request may result in an extend lock (8k 
extension), which blocks other writers. What normally happens is the 
these extend locks happen so fast that you hardly ever see them in the 
pg_locks table, except in the case where many concurrent connections are 
trying to do inserts into the same table. The following query will show 
if this is the case if you execute it during the time the problem is 
occurring.


select * from pg_locks where granted = false and locktype = 'extend';


I don't know if this is your particular problem, but perhaps it is.

Regards,
Michael Vitale

Pavel Suderevsky 
Thursday, March 15, 2018 6:29 AM

Hi,

Well, unfortunately I still need community help.

-- Environment

OS: Centos CentOS Linux release 7.2.1511

Kernel:  3.10.0-327.36.3.el7.x86_64

PostgreSQL: 9.6.3

-- Hardware

Server: Dell PowerEdge R430

CPU: Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz

Raid controller: PERC H730 Mini (1GB cache)

Disks: 8 x 10K RPM SAS 12GB/s 2.5 (ST1200MM0088) in RAID 6

RAM: 192GB (M393A2G40DB0-CPB x 16)

For more detailed hardware info please see attached configuration.txt

-- postgresql.conf

max_connections = 2048

shared_buffers = 48GB

temp_buffers = 128MB

work_mem = 256MB

maintenance_work_mem = 512MB

dynamic_shared_memory_type = posix

wal_level = hot_standby

min_wal_size = 4GB

max_wal_size = 32GB

huge_pages = on

+

numactl interleave=all

-- sysctl.conf

kernel.shmmax=64424509440

kernel.shmall=4294967296

kernel.sem = 1024 32767 128 16384

fs.aio-max-nr=3145728

fs.file-max = 6815744

net.core.rmem_default=262144

net.core.rmem_max=4194304

net.core.wmem_default=262144

net.core.wmem_max=1048586

vm.nr_hugepages=33000

vm.dirty_background_bytes=67108864

vm.dirty_bytes=536870912

vm.min_free_kbytes=1048576

zone_reclaim_mode=0

Again: problem is the occasional long inserts that can happen 1-5 
times per day on OLTP system.


No autovacuum performed during long inserts. WAL rate is 1-2Gb per 
hour, no correlation spotted with this issue.


Wait event "buffer_mapping" happen for appropriate transactions but 
not every time (maybe just not every time catched).


I have two suspects for such behaviour: I/O system and high concurrency.

There is a problem with one application that frequently recreates up 
to 90 sessions but investigation shows that there is no direct 
correlation between such sessions and long transactions, at least it 
is not the root cause of the issue (of course such app behaviour will 
be fixed).


The investigation and tracing with strace in particular showed that:

1. The only long event straced from postgres backends was <... semop 
resumed>.


2. Seems the whole host gets hung during such events.

Example:

Java application located on separate host reports several long 
transactions:


123336.943 - [1239588mks]: event.insert-table

123336.943 - [1240827mks]: event.insert-table

123337.019 - [1292534mks]: event.insert-table

143353.542 - [5467657mks]: event.insert-table

143353.543 - [5468884mks]: event.insert-table

152338.763 - [1264588mks]: event.insert-table

152338.765 - [2054887mks]: event.insert-table

Strace output for event happened at 14:33 with particular known pid:

119971 14:33:48.075375 epoll_wait(3, 

119971 14:33:48.075696 <... epoll_wait resumed> {{EPOLLIN, 
{u32=27532016, u64=27532016}}}, 1, -1) = 1 <0.000313>


119971 14:33:48.075792 recvfrom(9, 

119971 14:33:48.075866 <... recvfrom resumed> 
"B\0\0\3\27\0S_21\0\0*\0\1\0\1\0\1\0\0\0\0\0\1\0\1\0\0\0\0\0"..., 
8192, 0, NULL, NULL) = 807 <0.66>


119971 14:33:48.076243 semop(26706044, {{8, -1, 0}}, 1 

120019 14:33:48.119971 recvfrom(9, 

119971 14:33:53.491029 <... semop resumed> ) = 0 <5.414772>

119971 14:33:53.500356 lseek(18, 0, SEEK_END 

119971 14:33:53.500436 <... lseek resumed> ) = 107790336 <0.72>

119971 14:33:53.500514 lseek(20, 0, SEEK_END 

Checking strace long semop calls for whole day:

root@host [20180314 17:47:36]:/home/user$ egrep " <[1-9]." /tmp/strace 
| grep semop


119991 12:33:36 <... semop resumed> )   = 0 <1.419394>

119942 12:33:36 <... semop resumed> )   = 0 <1.422554>

119930 12:33:36 <... semop resumed> )   = 0 <1.414916>

119988 12:33:36 <... semop resumed> )   = 0 <1.213309>

119966 12:33:36 <... semop resumed> )   = 0 <1.237492>

119958 14:33:53.489398 <... semop resumed> ) = 0 <5.455830>

120019 14:33:53.490613 <... semop resumed> ) = 0 <5.284505>

119997 14:33:53.490638 <... semop resumed> ) = 0 <5.111661>

12 14:33:53.490649 <... semop resumed> ) = 0 <3.521992>

119991 14:33:53.490660 <... semop resumed> ) = 0 <2.522460>

119988 14:33:53.490670 <... semop resumed> ) = 0 <5.252485>

120044 14:33:53.490834 <... semop resumed> ) = 0 <1.718129>

119976 14:33:53.490852 <... semop resumed> ) = 0 <2.489563>

119974 14:33:53.490862 <... semop resumed> ) =

Re: Postgresql TPS Bottleneck

[MichaelDBA]

While setting these 2 parameters to off will make things go faster 
(especially for fsync), it is unrealistic to have these settings in a 
production environment, especiall fsync=off.  You might get by with 
synchronous_commit=off, but with fsync=off you could end up with 
corruption in your database.  synchronous_commit may not make anything 
go faster just change where the time is being spent.


Regards,
Michael Vitale


wakandavis...@outlook.com wrote on 3/31/2022 7:50 AM:

fsync = off
synchronous_commit = off

Re: Identify root-cause for intermittent spikes

[MichaelDBA]

Hello,

Your problem is probably, too many active, concurrent connections.  Get 
it from here the db directly:
select datname, usename, application_name, substring(query, 1, 80) 
query  from pg_stat_activity where state in ('active','idle in 
transaction');


Compare the number of rows returned with the number of vCPUs.  If it's 
more than double the number of vCPUs in your AWS instance class, then 
you are cpu saturated.


Regards,

Michael Vitale



Sengottaiyan T wrote on 10/11/2022 7:06 AM:


Hi All,

I'm looking for suggestions:

Environment: AWS PostgreSQL RDS instance - Version 14.3
Operations support gets intermittent alerts from the monitoring
tool through AWS cloud watch metrics on Disk Queue Depth, CPU
burst-credit & CPU Utilization.
I would like to understand what is causing the spike - is the
number of logon's increased, (or) number of transactions per
second increased, (or) SQL execution picked wrong plan and the
long running (I/O, CPU or memory intensive) SQL is increasing load
on server (cause and effect scenario) etc.,

Due to the reactive nature of the issues, we rely on the metrics
gathered in the AWS cloud watch monitoring (for the underlying OS
stats), Performance Insights (for the DB performance) and
correlate SQL queries with pg_Stat_Statements view. But the data
in the view is an aggregated stats. And, I'm looking to see the
deltas compared to normal runs.
How should I approach and get to the root-cause?

AppDynamics is already configured for the RDS instance. Are there
any open source monitoring tools available which would help to
capture and visualize the deltas?

Thanks,
Senko

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

[MichaelDBA]

Howdy,

Few additional questions:

1. How many concurrent, active connections are running when these BIND
   problems occur?  select count(*) from pg_stat_activity where state
   in ('active','idle in transaction')
2. Are the queries using gigantic IN () values?
3. Perhaps unrelated, but islog_temp_files turned on, and if so, do you
   have a lot of logs related to that?

Regards,
Michael Vitale, just another PG DBA

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

[MichaelDBA]

Hi Maxim,

10-20 active, concurrent connections is way below any CPU load problem 
you should have with 48 available vCPUs.
You never explicitly said what the load is, so what is it in the context 
of the 1,5,15?


Maxim Boguk wrote on 1/1/2023 11:30 AM:

1)usual load (e.g. no anomalies)
10-20 concurrent query runs (e.g. issues isn't related to the load 
spike or similar anomalies)

additionally 5-10 short idle in transaction (usual amount too)
total around 300 active connections to the database (after local 
pgbouncer in transaction mode)



Regards,

Michael Vitale

michael...@sqlexec.com 

703-600-9343

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

[MichaelDBA]

You said it's a dedicated server, but pgbouncer is running locally, 
right?  PGBouncer has a small footprint, but is the CPU high for it?


Maxim Boguk wrote on 1/1/2023 11:51 AM:



On Sun, Jan 1, 2023 at 6:43 PM MichaelDBA <mailto:michael...@sqlexec.com>> wrote:


Hi Maxim,

10-20 active, concurrent connections is way below any CPU load
problem you should have with 48 available vCPUs.
You never explicitly said what the load is, so what is it in the
context of the 1,5,15?


LA 10-15 all time, servers are really overprovisioned (2-3x by 
available CPU resources) because an application is quite sensitive to 
the database latency.
And during these latency spikes - EXECUTE work without any issues 
(e.g. only PARSE/BIND suck).



--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone UA: +380 99 143 
Phone AU: +61  45 218 5678




Regards,

Michael Vitale

michael...@sqlexec.com <mailto:michaelvit...@sqlexec.com>

703-600-9343

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

[MichaelDBA]

What happens if you take pg_stat_statements out of the picture (remove 
from shared_preload_libraries)?  Does your BIND problem go away?

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

[MichaelDBA]

Well if you find out for sure, please let me know.  I'm very interested 
in the outcome of this problem.


Maxim Boguk wrote on 1/5/2023 6:44 AM:



On Thu, Jan 5, 2023 at 1:31 PM MichaelDBA <mailto:michael...@sqlexec.com>> wrote:



What happens if you takepg_stat_statements out of the picture
(remove from shared_preload_libraries)?  Does your BIND problem go
away?


I didn't test this idea, because it requires restart of the database 
(it cannot be done quickly) and without pg_stat_statementsthere will 
be no adequate performance monitoring of the database.
But I'm pretty sure that the issue will go away with 
pg_stat_statements disabled.


--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone UA: +380 99 143 
Phone AU: +61  45 218 5678

Re: ALTER STATEMENT getting blocked

[MichaelDBA]

Do something like this to get it without being behind other 
transactions...You either get in and get your work done or try again


DO language plpgsql $$
BEGIN
FOR get_lock IN 1 .. 100 LOOP
  BEGIN
ALTER TABLE mytable ;
EXIT;
  END;
END LOOP;
END;
$$;



Tom Lane wrote on 1/19/2023 12:45 PM:

aditya desai  writes:

We have a Postgres 11.16 DB which is continuously connected to informatica
and data gets read from it continuously.
When we have to ALTER TABLE.. ADD COLUMN.. it gets blocked by the SELECTs
on the table mentioned by process above.
Is there any way to ALTER the table concurrently without  getting blocked?
Any parameter or option? Can someone give a specific command?

ALTER TABLE requires exclusive lock to do that, so it will queue up
behind any existing table locks --- but then new lock requests will
queue up behind its request.  So this'd only happen if your existing
reading transactions don't terminate.  Very long-running transactions
are unfriendly to other transactions for lots of reasons including
this one; see if you can fix your application to avoid that.  Or
manually cancel the blocking transaction(s) after the ALTER begins
waiting.

regards, tom lane





Regards,

Michael Vitale

michael...@sqlexec.com 

703-600-9343

Re: What is equivalent of v$sesstat and v$sql_plan in postgres?

[MichaelDBA]

RDS does allow you to now create your customized extensions via the 
pg_tle extension.  Regarding your desire to capture session metrics and 
sql plan information, I do not know anything that can do that in PG.

Regards,
Michael Vitale

kunwar singh wrote on 4/20/2023 9:37 AM:
If not , how can I get the same functionality by writing a 
query/job/procedure?


Something which doesn't require me to install a plugin , it is RDS 
PostgreSQL so cannot install /enable plugin without downtime to a 
critical production database


v$sesstat use case - I run a query , I would like to see what all 
metrics in postgres change for my session


v$sql_plan use case - I want to capture all operations for all plans 
and do analysis on those.


--
Cheers,
Kunwar



Regards,

Michael Vitale

michael...@sqlexec.com 

703-600-9343

Re: Automated bottleneck detection

[MichaelDBA]

Wow, freakin cool, can't wait to start fiddling with pg_wait_sampling.  
Reckon we can get lightweight locks and spinlocks history with this cool 
new extension instead of awkwardly and repeatedly querying the 
pg_stat_activity table.


Regards,
Michael Vitale


Thomas Güttler 
Thursday, July 26, 2018 7:27 AM
This sound good. Looks like an automated bootleneck detection
could be possible with pg_wait_sampling.

Regards,
  Thomas

Re: Indexes on UUID - Fragmentation Issue

[MichaelDBA]

or prepend the UUID with a timestamp?

Regards,
Michael Vitale


Andreas Karlsson 
Monday, October 29, 2018 10:52 AM
On 10/29/2018 02:29 PM, Uday Bhaskar V wrote:> I have

How is it implemented? I can personally see two ways of generating 
sequential UUID:s. Either you use something like PostgreSQL's 
sequences or you can implement something based on the system time plus 
some few random bits which means they will be mostly sequential.


It could be worth checking on the hackers mailing list if there is any 
interest in this feature, but if it works like a sequence it should 
also probably be a sequence if it is ever going to be accepted into 
the core.


For your own use I recommend doing like Merlin suggested and write an 
extension. As long as you know a bit of C they are easy to write.


Andreas

Uday Bhaskar V 
Monday, October 29, 2018 9:29 AM
Hi,

I have searched in many postgres blogs for Sequential UUID generation, 
which can avoid Fragmentation issue.


I did a POC(in postgres) with sequential UUID against Non sequential 
which has shown lot of different in space utilization and index size. 
Sql server has "newsequentialid" which generates sequential UUID. I 
have created C function which can generate a sequential UUID, but I am 
not sure how best I can use that in postgres.


I would really like to contribute to Postgres, If I can. Please let me 
know your thoughts or plans regarding UUID generation.


Regards,
Uday

SCRAM question

[MichaelDBA]

I am using pgadmin4 version 3.4 with PG 11.0 and I get this error when I 
try to connect with scram authorization:


User "myuser" does not have a valid SCRAM verifier.

How do I get around this?   And also how would I do this for psql?

Regards,
Michael Vitale

Re: autovacuum big table taking hours and sometimes seconds

[MichaelDBA]

Hi all,

In the myriad of articles written about autovacuum tuning, I really like 
this article by Tomas Vondra of 2ndQuadrant:

https://blog.2ndquadrant.com/autovacuum-tuning-basics/

It is a concise article that touches on all the major aspects of 
autovacuuming tuning: thresholds, scale factors, throttling, etc.


Regards and happy vacuuming to yas!
Michael Vitale


Mariel Cherkassky 
Wednesday, February 6, 2019 8:41 AM
which one you mean ? I changed the threshold and the scale for the 
specific table...


dangal 
Wednesday, February 6, 2019 8:36 AM
Would it be nice to start changing those values ​​found in the default
postgres.conf so low?



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


David Rowley 
Wednesday, February 6, 2019 8:05 AM

Going by the block hits/misses/dirtied and the mentioned vacuum times,
it looks like auto-vacuum is set to the standard settings and if so it
spent about 100% of its time sleeping on the job.

It might be a better idea to consider changing the vacuum settings
globally rather than just for one table.

Running a vacuum_cost_limit of 200 is likely something you'd not want
to ever do with modern hardware... well maybe unless you just bought
the latest Raspberry PI, or something. You should be tuning that
value to something that runs your vacuums to a speed you're happy with
but leaves enough IO and CPU for queries running on the database.

If you see that all auto-vacuum workers are busy more often than not,
then they're likely running too slowly and should be set to run more
quickly.

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: 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: How to get the content of Bind variables

[MichaelDBA]

If you set log_min_duration_statement low enough for your particular 
query, you will see another line below it showing what values are 
associated with each bind variable like this:


2019-02-28 00:07:55CST 2019-02-2800:02:09CST ihr2 10.86.42.184(43460) 
SELECT LOG:  duration: 26078.308 ms  execute : select 
pg_advisory_lock($1)


2019-02-28 00:07:55CST 2019-02-2800:02:09CST ihr2 10.86.42.184(43460) 
SELECT DETAIL:  parameters: $1 = '3428922050323511872'


Regards,
Michael Vitale

ROS Didier 
Thursday, February 28, 2019 7:21 AM

Hi

In the log file of my PostgreSQL cluster, I find :

>>

*Statement:*update t_shared_liste_valeurs set deletion_date=*$1*, 
deletion_login=*$2*, modification_date=*$3*, modification_login=*$4*, 
administrable=*$5*, libelle=*$6*, niveau=*$7* where code=*$8*


<<

èhow to get the content of the bind variables ?

Thanks in advance

Best Regards

cid:image002.png@01D14E0E.8515EB90



*
**Didier ROS*

*Expertise SGBD*

EDF - DTEO - DSIT - IT DMA

Département Solutions Groupe

Groupe Performance Applicative

32 avenue Pablo Picasso

92000 NANTERRE

_didier*.r...@edf.fr* _

Tél. : +33 6 49 51 11 88

cid:image003.png@01D4BE20.1EAF68B0 
cid:image004.png@01D4BE20.1EAF68B0 




Ce message et toutes les pièces jointes (ci-après le 'Message') sont 
établis à l'intention exclusive des destinataires et les informations 
qui y figurent sont strictement confidentielles. Toute utilisation de 
ce Message non conforme à sa destination, toute diffusion ou toute 
publication totale ou partielle, est interdite sauf autorisation expresse.


Si vous n'êtes pas le destinataire de ce Message, il vous est interdit 
de le copier, de le faire suivre, de le divulguer ou d'en utiliser 
tout ou partie. Si vous avez reçu ce Message par erreur, merci de le 
supprimer de votre système, ainsi que toutes ses copies, et de n'en 
garder aucune trace sur quelque support que ce soit. Nous vous 
remercions également d'en avertir immédiatement l'expéditeur par 
retour du message.


Il est impossible de garantir que les communications par messagerie 
électronique arrivent en temps utile, sont sécurisées ou dénuées de 
toute erreur ou virus.



This message and any attachments (the 'Message') are intended solely 
for the addressees. The information contained in this Message is 
confidential. Any use of information contained in this Message not in 
accord with its purpose, any dissemination or disclosure, either whole 
or partial, is prohibited except formal approval.


If you are not the addressee, you may not copy, forward, disclose or 
use any part of it. If you have received this message in error, please 
delete it and all copies from your system and notify the sender 
immediately by return message.


E-mail communication cannot be guaranteed to be timely secure, error 
or virus-free.

Re: Shared_buffers

[MichaelDBA]

Set shared_buffers more accurately by using pg_buffercache extension and 
the related queries during high load times.


Regards,
Michael Vitale


Michael Lewis 
Tuesday, March 12, 2019 3:23 PM
On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe > wrote:


Daulat Ram wrote:
> I want to know about the working and importance of
shared_buffers  in Postgresql?
> is it similar to the oracle database buffer cache?

Yes, exactly.

The main difference is that PostgreSQL uses buffered I/O, while
Oracle usually
uses direct I/O.

Usually you start with shared_buffers being the minimum of a
quarter of the
available RAM and 8 GB.


Any good rule of thumb or write up about when shared buffers in excess 
of 8GBs makes sense (assuming system ram 64+ GBs perhaps)?

Re: Shared_buffers

[MichaelDBA]

Here's one cook article on using pg_buffercache...

https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/

Regards,
Michael Vitale


Justin Pryzby <mailto:pry...@telsasoft.com>
Tuesday, March 12, 2019 4:11 PM

I've tuned ~40 postgres instances, primarily using log_checkpoints and
pg_stat_bgwriter, with custom RRD graphs. pg_buffercache does provide some
valuable insights, and I know it's commonly suggested to check 
histogram of

usagecounts, but I've never had any idea how to apply that to tune
shared_buffers.

Could you elaborate on what procedure you suggest ?

Justin
MichaelDBA <mailto:michael...@sqlexec.com>
Tuesday, March 12, 2019 4:03 PM
Set shared_buffers more accurately by using pg_buffercache extension 
and the related queries during high load times.


Regards,
Michael Vitale


Michael Lewis <mailto:mle...@entrata.com>
Tuesday, March 12, 2019 3:23 PM
On Tue, Mar 12, 2019 at 2:29 AM Laurenz Albe <mailto:laurenz.a...@cybertec.at>> wrote:


Daulat Ram wrote:
> I want to know about the working and importance of
shared_buffers  in Postgresql?
> is it similar to the oracle database buffer cache?

Yes, exactly.

The main difference is that PostgreSQL uses buffered I/O, while
Oracle usually
uses direct I/O.

Usually you start with shared_buffers being the minimum of a
quarter of the
available RAM and 8 GB.


Any good rule of thumb or write up about when shared buffers in excess 
of 8GBs makes sense (assuming system ram 64+ GBs perhaps)?

Re: High concurrency same row (inventory)

[MichaelDBA]

Does pg_stat_user_tables validate that the major updates are indeed "hot 
updates"?  Otherwise, you may be experiencing bloat problems if 
autovacuum is not set aggressively.  Did you change default parameters 
for autovacuum?  You should.  They are set very conservatively right 
outa the box.  Also, I wouldn't increase work_mem too much unless you 
are experiencing query spill over to disk.  Turn on "log_temp_files" 
(=0) and monitor if you have this spillover.  If not, don't mess with 
work_mem.  Also, why isn't effective_cache_size set closer to 80-90% of 
memory instead of 50%? Are there other servers on the same host as 
postgres?  As the other person mentioned, tune checkpoints so that they 
do not happen too often.  Turn on "log_checkpoints" to get more info.


Regards,
Michael Vitale

Rick Otten wrote on 7/29/2019 8:35 AM:


On Mon, Jul 29, 2019 at 2:16 AM Jean Baro > wrote:



We have a new Inventory system running  on its own database (PG 10
AWS RDS.m5.2xlarge 1TB SSD EBS - Multizone). The DB effective size
is less than 10GB at the moment. We provided 1TB to get more IOPS
from EBS.

As we don't have a lot of different products in our catalogue it's
quite common (especially when a particular product is on sale) to
have a high rate of concurrent updates against the same row. There
is also a frequent (every 30 minutes) update to all items which
changed their current stock/Inventory coming from the warehouses
(SAP), the latter is a batch process. We have just installed this
system for a new tenant (one of the smallest one) and although
it's running great so far, we believe this solution would not
scale as we roll out this system to new (and bigger) tenants.
Currently there is up to 1.500 transactions per second (mostly
SELECTS and 1 particular UPDATE which I believe is the one being
aborted/deadlocked some tImes) in this inventory database.

I am not a DBA, but as the DBAs (most of them old school Oracle
DBAs who are not happy with the move to POSTGRES) are considering
ditching Postgresql without any previous tunning I would like to
understand the possibilities.

Considering this is a highly concurrent (same row) system I
thought to suggest:



Another thing which you might want to investigate is your checkpoint 
tunables. My hunch is with that many writes, the defaults are probably 
not going to be ideal.
Consider the WAL tunables documentation: 
https://www.postgresql.org/docs/10/wal-configuration.html

Re: High concurrency same row (inventory)

[MichaelDBA]

Looks like regular updates not HOT UPDATES

Jean Baro wrote on 7/29/2019 8:26 PM:

image.png

The dead tuples goes up at a high ratio, but then it gets cleaned.

if you guys need any further information, please let me know!



On Mon, Jul 29, 2019 at 9:06 PM Jean Baro > wrote:


The UPDATE was something like:

UPDATE bucket SET qty_available = qty_available + 1 WHERE
bucket_uid = 0940850938059380590

Thanks for all your help guys!

On Mon, Jul 29, 2019 at 9:04 PM Jean Baro mailto:jfb...@gmail.com>> wrote:

All the failures come from the Bucket Table (see image below).

I don't have access to the DB, neither the code, but last time
I was presented to the UPDATE it was changing (incrementing or
decrementing) *qty_available*, but tomorrow morning I can be
sure, once the developers and DBAs are back to the office. I
know it's quite a simple UPDATE.

Table is called Bucket:
{autovacuum_vacuum_scale_factor=0.01}

Bucket.png


On Mon, Jul 29, 2019 at 3:12 PM Michael Lewis
mailto:mle...@entrata.com>> wrote:

Can you share the schema of the table(s) involved and an
example or two of the updates being executed?

Re: Strange runtime partition pruning behaviour with 11.4

[MichaelDBA]

I too am a bit perplexed by why runtime partition pruning does not seem 
to work with this example.  Anybody got any ideas of this?


Regards,
Michael Vitale

Thomas Kellerer wrote on 8/2/2019 9:58 AM:

I stumbled across this question on SO: 
https://stackoverflow.com/questions/56517852

Disregarding the part about Postgres 9.3, the example for Postgres 11 looks a 
bit confusing.

There is a script to setup test data in that question:

 start of script 

 create table foo (
 foo_id integer not null,
 foo_name varchar(10),
 constraint foo_pkey primary key (foo_id)
 );

 insert into foo
   (foo_id, foo_name)
 values
   (1, 'eeny'),
   (2, 'meeny'),
   (3, 'miny'),
   (4, 'moe'),
   (5, 'tiger'),
   (6, 'toe');

 create table foo_bar_baz (
 foo_id integer not null,
 bar_id integer not null,
 bazinteger not null,
 constraint foo_bar_baz_pkey primary key (foo_id, bar_id, baz),
 constraint foo_bar_baz_fkey1 foreign key (foo_id)
 references foo (foo_id)
 ) partition by range (foo_id)
 ;

 create table if not exists foo_bar_baz_0 partition of foo_bar_baz for 
values from (0) to (1);
 create table if not exists foo_bar_baz_1 partition of foo_bar_baz for 
values from (1) to (2);
 create table if not exists foo_bar_baz_2 partition of foo_bar_baz for 
values from (2) to (3);
 create table if not exists foo_bar_baz_3 partition of foo_bar_baz for 
values from (3) to (4);
 create table if not exists foo_bar_baz_4 partition of foo_bar_baz for 
values from (4) to (5);
 create table if not exists foo_bar_baz_5 partition of foo_bar_baz for 
values from (5) to (6);

 with foos_and_bars as (
 select ((random() * 4) + 1)::int as foo_id, bar_id::int
 from generate_series(0, 1499) as t(bar_id)
 ), bazzes as (
 select baz::int
 from generate_series(1, 1500) as t(baz)
 )
 insert into foo_bar_baz (foo_id, bar_id, baz)
 select foo_id, bar_id, baz
 from bazzes as bz
   join foos_and_bars as fab on mod(bz.baz, fab.foo_id) = 0;

 end of script 

I see the some strange behaviour similar to to what is reported in the comments 
to that question:

When I run the test query immediately after populating the tables with the 
sample data:

 explain analyze
 select count(*)
 from foo_bar_baz as fbb
   join foo on fbb.foo_id = foo.foo_id
 where foo.foo_name = 'eeny'

I do see an "Index Only Scan  (never executed)" in the plan for the 
irrelevant partitions:

   https://explain.depesz.com/s/AqlE

However once I run "analyze foo_bar_baz" (or "vacuum analyze"), Postgres chooses to do a 
"Parallel Seq Scan" for each partition:

   https://explain.depesz.com/s/WwxE

Why does updating the statistics mess up (runtime) partition pruning?


I played around with random_page_cost and that didn't change anything.
I tried to create extended statistics on "foo(id, name)" so that the planner 
would no, that there is only one name per id. No change.

I saw the above behaviour when running this on Windows 10 (my Laptop) or CentOS 
7 (a test environment on a VM)

On the CentOS server default_statistics_target is set to 100, on my laptop it 
is set to 1000

In both cases the Postgres version was 11.4

Any ideas?

Thomas

Re: Strange runtime partition pruning behaviour with 11.4

[MichaelDBA]

I too got the same plan (non runtime partition pruning plan) with or 
without the statistics.  So it looks like the workaround until this is 
fixed is to re-arrange the query to do a subselect to force the runtime 
partition pruning as Andreas suggested, which I tested and indeed does 
work for me too!


Regards,
Michael Vitale

Thomas Kellerer wrote on 8/3/2019 10:06 AM:

it's posible to rewrite the query to:


test=# explain analyse select count(*) from foo_bar_baz as fbb where 
foo_id = (select foo_id from foo where foo_name = 'eeny');


I know, that's not a solution, but a workaround. :-(


Yes, I discovered that as well.

But I'm more confused (or concerned) by the fact that the (original) 
query works correctly *without* statistics.


Thomas

Re: Planner performance in partitions

[MichaelDBA]

Queries against tables with a lot of partitions (> 1000) start to incur 
an increasing planning time duration even with the current version, 
V11.  V12 purportedly has fixed this problem, allowing thousands of 
partitioned tables without a heavy planning cost.  Can't seem to find 
the threads on this topic, but there are out there.  I personally noted 
a gigantic increase in planning time once I got past 1500 partitioned 
tables in V11.


On another note, hopefully they have fixed runtime partition pruning in 
V12 since V11 introduced it but some query plans don't use it, so you 
have to reconstruct some queries to sub queries to make it work correctly.


Regards,
Michael Vitale


Michael Lewis wrote on 8/12/2019 3:05 PM:
"It is also important to consider the overhead of partitioning during 
query planning and execution. The query planner is generally able to 
handle partition hierarchies with */up to a few hundred partitions 
fairly well/*, provided that typical queries allow the query planner 
to prune all but a small number of partitions. Planning times become 
longer and memory consumption becomes higher as more partitions are 
added." (emphasis added)


--https://www.postgresql.org/docs/current/ddl-partitioning.html

Re: Erratically behaving query needs optimization

[MichaelDBA]

Yes, adding another index might help reduce the number of rows filtered 
--> Rows Removed by Filter: 1308337


Also, make sure you run vacuum analyze on this query.

Regards,
Michael Vitale

Luís Roberto Weck wrote on 8/20/2019 10:58 AM:

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 the performance varies greatly during a run
of the application (while applying the said action by the user several
times).

My tables are only getting bigger with time, not much DELETEs and even
less UPDATEs as far as I can tell.

Problematic query:

EXPLAIN (ANALYZE,BUFFERS)
SELECT DISTINCT ON (results.attribute_id) results.timestamp,
results.data FROM results
 JOIN scheduler_operation_executions ON
scheduler_operation_executions.id = results.operation_execution_id
 JOIN scheduler_task_executions ON scheduler_task_executions.id =
scheduler_operation_executions.task_execution_id
WHERE scheduler_task_executions.device_id = 97
 AND results.data <> ''
 AND results.data IS NOT NULL
 AND results.object_id = 1955
 AND results.attribute_id IN (4, 5) -- possibly a longer list here
 AND results.data_access_result = 'SUCCESS'
ORDER BY results.attribute_id, results.timestamp DESC
LIMIT 2 -- limit by the length of the attributes list

In words: I want the latest (ORDER BY results.timestamp DESC) results
of a device (scheduler_task_executions.device_id = 97 - hence the
joins results -> scheduler_operation_executions ->
scheduler_task_executions)
for a given object and attributes with some additional constraints on
the data column. But I only want the latest attributes for which we
have results, hence the DISTINCT ON (results.attribute_id) and LIMIT.

First run: https://explain.depesz.com/s/qh4C
Limit  (cost=157282.39..157290.29 rows=2 width=54) (actual
time=44068.166..44086.970 rows=2 loops=1)
   Buffers: shared hit=215928 read=85139
   ->  Unique  (cost=157282.39..157298.20 rows=4 width=54) (actual
time=44068.164..44069.301 rows=2 loops=1)
 Buffers: shared hit=215928 read=85139
 ->  Sort  (cost=157282.39..157290.29 rows=3162 width=54)
(actual time=44068.161..44068.464 rows=2052 loops=1)
   Sort Key: results.attribute_id, results."timestamp" DESC
   Sort Method: quicksort  Memory: 641kB
   Buffers: shared hit=215928 read=85139
   ->  Gather  (cost=62853.04..157098.57 rows=3162
width=54) (actual time=23518.745..44076.385 rows=4102 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 Buffers: shared hit=215928 read=85139
 ->  Nested Loop  (cost=61853.04..155782.37
rows=1318 width=54) (actual time=23290.514..43832.223 rows=1367
loops=3)
   Buffers: shared hit=215928 read=85139
   ->  Parallel Hash Join
(cost=61852.61..143316.27 rows=24085 width=4) (actual
time=23271.275..40018.451 rows=19756 loops=3)
 Hash Cond:
(scheduler_operation_executions.task_execution_id =
scheduler_task_executions.id)
 Buffers: shared hit=6057 read=85139
 ->  Parallel Seq Scan on
scheduler_operation_executions  (cost=0.00..74945.82 rows=2482982
width=8) (actual time=7.575..15694.435 rows=1986887 loops=3)
   Buffers: shared hit=2996 
read=47120

 ->  Parallel Hash
(cost=61652.25..61652.25 rows=16029 width=4) (actual
time=23253.337..23253.337 rows=13558 loops=3)
   Buckets: 65536  Batches: 1
Memory Usage: 2144kB
   Buffers: shared hit=2977 
read=38019

   ->  Parallel Seq Scan on
scheduler_task_executions  (cost=0.00..61652.25 rows=16029 width=4)
(actual time=25.939..23222.174 rows=13558 loops=3)
 Filter: (device_id = 97)
 Rows Removed by Filter: 
1308337
 Buffers: shared hit=2977 
read=38019

   ->  Index Scan using
index_operation_execution_id_asc on results  (cost=0.43..0.51 rows=1
width=58) (actual time=0.191..0.191 rows=0 loops=59269)
 Index Cond: (operation_execution_id =
scheduler_operation_executions.id)
 Filter: ((data IS NOT NULL) AND (data
<> ''::text) AND (attribute_id = ANY ('{4,5}'::integer[]))
AND (object_id = 1955) AND (data_access_result = 'SUCCESS'::text))
 Rows Removed by Filter: 0
 Buffers: shared hit=209871
Planning Time: 29.295 ms
Execution 

Re: Extremely slow count (simple query, with index)

[MichaelDBA]

Hi Marco,

Since you said approximates would be good enough, there are two ways to 
do that.  Query pg_class.reltuples or pg_stat_user_tables.n_live_tup. 
Personally, I prefer the pg_stat_user tables since it is more current 
than pg_class table, unless you run ANALYZE on your target table before 
querying pg_class table.  Then of course you get results in a few 
milliseconds since you do not incur the tablescan cost of selecting 
directly from the target table.


Regards,
Michael Vitale


Marco Colli wrote on 8/22/2019 8:44 AM:

Hello!

Any help would be greatly appreciated.
I need to run these simple queries on a table with millions of rows:

```
SELECT COUNT(*) FROM "subscriptions" WHERE 
"subscriptions"."project_id" = 123;

```

```
SELECT COUNT(*) FROM "subscriptions" WHERE 
"subscriptions"."project_id" = 123 AND "subscriptions"."trashed_at" IS 
NULL;

```

The count result for both queries, for project 123, is about 5M.

I have an index in place on `project_id`, and also another index on 
`(project_id, trashed_at)`:


```
"index_subscriptions_on_project_id_and_created_at" btree (project_id, 
created_at DESC)
"index_subscriptions_on_project_id_and_trashed_at" btree (project_id, 
trashed_at DESC)

```

The problem is that both queries are extremely slow and take about 17s 
each.


These are the results of `EXPLAIN ANALIZE`:


```
QUERY PLAN
--
 Aggregate  (cost=2068127.29..2068127.30 rows=1 width=0) (actual 
time=17342.420..17342.420 rows=1 loops=1)
   ->  Bitmap Heap Scan on subscriptions  (cost=199573.94..2055635.23 
rows=4996823 width=0) (actual time=1666.409..16855.610 rows=4994254 
loops=1)

       Recheck Cond: (project_id = 123)
         Rows Removed by Index Recheck: 23746378
         Heap Blocks: exact=131205 lossy=1480411
         ->  Bitmap Index Scan on 
index_subscriptions_on_project_id_and_trashed_at 
 (cost=0.00..198324.74 rows=4996823 width=0) (actual 
time=1582.717..1582.717 rows=4994877 loops=1)

               Index Cond: (project_id = 123)
 Planning time: 0.090 ms
 Execution time: 17344.182 ms
(9 rows)
```


```
    QUERY PLAN
--
 Aggregate  (cost=2047881.69..2047881.70 rows=1 width=0) (actual 
time=17557.218..17557.218 rows=1 loops=1)
   ->  Bitmap Heap Scan on subscriptions  (cost=187953.70..2036810.19 
rows=4428599 width=0) (actual time=1644.966..17078.378 rows=4994130 
loops=1)

       Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))
       Rows Removed by Index Recheck: 23746273
         Heap Blocks: exact=131144 lossy=1480409
         ->  Bitmap Index Scan on 
index_subscriptions_on_project_id_and_trashed_at 
 (cost=0.00..186846.55 rows=4428599 width=0) (actual 
time=1566.163..1566.163 rows=4994749 loops=1)

 Index Cond: ((project_id = 123) AND (trashed_at IS NULL))
 Planning time: 0.084 ms
 Execution time: 17558.522 ms
(9 rows)
```

What is the problem?
What can I do to improve the performance (i.e. count in a few seconds)?

I have also tried to increase work_mem from 16MB to 128MB without any 
improvement.

Even an approximate count would be enough.
Postgresql v9.5

Re: Slow query on a one-tuple table

[MichaelDBA]

Hi all,

I sometimes set autovacuum_vacuum_scale factor = 0 but only when I also 
set autovacuum_vacuum_threshold to some non-zero number to force vacuums 
after a certain number of rows are updated.  It takes the math out of it 
by setting the threshold explicitly.


But in this case he has also set autovacuum_vacuum_threshold to only 
25!  So I think you have to fix your settings by increasing one or both 
accordingly.


Regards,
Michael Vitale


Tom Lane wrote on 9/19/2019 6:57 PM:

=?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 pg_stat_all_tables.last_autovacuum to see if anything
is happening.  If the dates seem reasonably current, then I'm wrong.

regards, tom lane

Re: Some observations on very slow pg_restore operations

[MichaelDBA]

Hi Ogden,

You didn't mention any details about your postgresql.conf settings.  Why 
don't you set them optimally for your loads and try again and see if 
there is any difference.  Make sure you do a DB restart since some of 
these parameters require it.


==
parameter   before  after
--  ---
shared_buffers  Reduce this value to about 25% of total memory
temp_buffersDecrease this value to 8MB since we are not using 
temporary tables or doing intermediate sorts
work_memReduce significantly (1MB) since we are not doing 
memory sorts or hashes per SQL
maintenance_work_memIncrease signficantly for DDL bulk loading, restore 
operations
fsync   off (so that time is not being spent waiting for stuff 
to be written to disk). Note: you may not be able to recover your database 
after a crash when set to off.
checkpoint_segments Increase this significantly for DML bulk loading, 
restore operations
max_wal_sizeIncrease significantly like you would to 
checkpoint_segments
min_wal_sizeIncrease significantly like you would to 
checkpoint_segments
checkpoint_timeout  Increase to at least 30min
archive_modeoff
autovacuum  off
synchronous_commit  off
wal_level   minimal
max_wal_senders 0
full_page_writesoff during DML bulk loading, restore operations
wal_buffers 16MB during DML bulk loading, restore operations


Regards,
Michael Vitale


Ogden Brash wrote on 10/3/2019 4:30 PM:
I recently performed a pg_dump (data-only) of a relatively large 
database where we store intermediate results of calculations. It is 
approximately 3 TB on disk and has about 20 billion rows.


We do the dump/restore about once a month and as the dataset has 
grown, the restores have gotten very slow. So, this time I decided to 
do it a different way and have some observations that puzzle me.


Background:

The data is extremely simple. The rows consist only of numbers and are 
all fixed length. There are no foreign keys, constraints, null values, 
or default values. There are no strings or arrays. There are 66 tables 
and the number of rows in each table forms a gaussian distribution; so 
there are 3 tables which have about 3 billion rows each and the rest 
of the tables have significantly fewer rows.


I used the directory format when doing the pg_dump. The compressed 
data of the dump is 550 GB.


I am using: (PostgreSQL) 11.5 (Ubuntu 11.5-1.pgdg18.04+1)

The machine that I attempted to do a pg_restore to is a dedicated 
server just for one instance of posgresql. It has 32 GB of memory and 
is running Ubuntu 18.04 (headless). It physical hardware, not 
virtualized. Nothing else runs on the machine and the postgresql.conf 
settings have been tuned (to the best of my postgresql abilities which 
are suspect). While the operating system is installed on an SSD, there 
is one extra large, fast HDD that is dedicated to the posgresql 
server. It has been in use for this particular purpose for a while and 
has not had performance issues. (Just with pg_restore)


Autovacuum is off and all indexes have been deleted before the restore 
is started. There is nothing in the db except for the empty data tables.


Restoring over the net:

In the past we have always restored in a way where the dumped data is 
read over a gigabit connection while being restored to the local 
drive. But, the last time we did it it took 2 days and I was looking 
for something faster. So, I decided to copy the dumped directory to 
the local drive and restore from the dump locally. I knew that because 
the machine only had one drive that would fit the data, there would be 
some I/O contention, but I hoped that it might not be as bad as 
reading over the network.


The pg_restore went unbearably slowly... after many hours it had 
written less than 20GB to the database, so I started tracking it with 
iostat to see what was going on. The following is iostat output every 
60 seconds. I tracked it for several hours and this is representative 
of what was happening consistently.



avg-cpu:  %user   %nice %system %iowait  %steal  %idle
           0.39    0.00 0.40   43.10    0.00   56.11

Device             tps    kB_read/s    kB_wrtn/s kB_read    kB_wrtn
loop0    0.00         0.00         0.00          0          0
loop1             0.00         0.00         0.00   0          0
loop2  0.00         0.00         0.00          0          0
sda             263.33       132.87      2990.93  7972     179456
sdb  0.17         0.00         0.73          0         44

avg-cpu: %user   %nice %system %iowait  %steal   %idle
           0.34    0.00    0.41   44.43    0.00   54.82

Device          tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
loop0             0.00         0.00         0.00   0          0
loop1  0.00         0.00         0.00          0          0
l

Re: Optimising a two column OR check

[MichaelDBA]

Another thing to consider is the visibility map.  From what I 
understand, index only scans are preferred for heavily updated tables, 
not infrequently updated ones.  Even though index only scans imply ONLY 
they really aren't in the sense that they may need to visit the 
Visibility Map for the heap. This can be costly and the planner may 
remove index only scan consideration if the VM has tuples that are not 
visible.


BTW, to Andrew, the UNION ALL alternative still results in bitmap index 
scans from my testing.


Regards,
Michael Vitale



Jeff Janes wrote on 10/12/2019 11:17 AM:
On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby > wrote:


On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
> With seqscan disabled, I get this plan on 9.6:
>  Bitmap Heap Scan on friend  (cost=8.42..19.01 rows=14 width=8)
...
> I expected to get an index-only scan in this situation, as that
would be a
> very common query. Is there a way to actually make this sort of
query
> resolvable with an index-only scan? Maybe a different table
structure would
> help?


It would have to scan the entire index to find the cases where 
user2_id=42 but user1_id is not constrained. Technically User1_id 
would be constrained to be less than 42, but I don't think the planner 
will take that into account.



The v11 release notes have this relevant item:

https://www.postgresql.org/docs/11/release-11.html
|Allow bitmap scans to perform index-only scans when possible
(Alexander Kuzmenkov)


But this is not one of those cases.  It is only possible when the only 
data needed is whether the row exists or not.


Cheers,

Jeff

Re: Optimising a two column OR check

[MichaelDBA]

Yikes, apologies to all, my wording is the opposite of what I meant!

Index only scans are preferred for infrequently updated ones, not 
heavily updated ones where the visibility map is updated often.


Regards,
Michael Vitale


MichaelDBA wrote on 10/12/2019 11:27 AM:
Another thing to consider is the visibility map.  From what I 
understand, index only scans are preferred for heavily updated tables, 
not infrequently updated ones.  Even though index only scans imply 
ONLY they really aren't in the sense that they may need to visit the 
Visibility Map for the heap. This can be costly and the planner may 
remove index only scan consideration if the VM has tuples that are not 
visible.


BTW, to Andrew, the UNION ALL alternative still results in bitmap 
index scans from my testing.


Regards,
Michael Vitale



Jeff Janes wrote on 10/12/2019 11:17 AM:
On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby <mailto:pry...@telsasoft.com>> wrote:


On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
> With seqscan disabled, I get this plan on 9.6:
>  Bitmap Heap Scan on friend  (cost=8.42..19.01 rows=14 width=8)
...
> I expected to get an index-only scan in this situation, as that
would be a
> very common query. Is there a way to actually make this sort of
query
> resolvable with an index-only scan? Maybe a different table
structure would
> help?


It would have to scan the entire index to find the cases where 
user2_id=42 but user1_id is not constrained. Technically User1_id 
would be constrained to be less than 42, but I don't think the 
planner will take that into account.



The v11 release notes have this relevant item:

https://www.postgresql.org/docs/11/release-11.html
|Allow bitmap scans to perform index-only scans when possible
(Alexander Kuzmenkov)


But this is not one of those cases.  It is only possible when the 
only data needed is whether the row exists or not.


Cheers,

Jeff

Re: Optimising a two column OR check

[MichaelDBA]

Nope, vacuumed it and still got the bitmap index scans.

Andrew Gierth wrote on 10/12/2019 11:33 AM:

"MichaelDBA" == MichaelDBA   writes:

  MichaelDBA> BTW, to Andrew, the UNION ALL alternative still results in
  MichaelDBA> bitmap index scans from my testing.

You probably forgot to vacuum the table.

Re: Optimising a two column OR check

[MichaelDBA]

Perhaps the fix by Alexander Kuzmenkov in V11 added this VM 
consideration for having a preference of bitmap index scan over an index 
only scan.  Looks like I'm goin' down the rabbit hole...


Regards,
Michael Vitale

MichaelDBA wrote on 10/12/2019 11:35 AM:

Nope, vacuumed it and still got the bitmap index scans.

Andrew Gierth wrote on 10/12/2019 11:33 AM:

"MichaelDBA" == MichaelDBA   writes:

MichaelDBA> BTW, to Andrew, the UNION ALL alternative still results in
  MichaelDBA> bitmap index scans from my testing.

You probably forgot to vacuum the table.

Re: Optimising a two column OR check

[MichaelDBA]

Yep, you're right, Andrew, adding a couple rows made it do the index 
only scan.  I reckon I got misled by turning off sequential scans, 
thinking that actual rows were not important anymore.  Overly simplistic 
reasonings can get one into trouble, lol.


Regards,
Michael Vitale


Andrew Gierth wrote on 10/12/2019 11:46 AM:

"MichaelDBA" == MichaelDBA   writes:

  MichaelDBA> Nope, vacuumed it and still got the bitmap index scans.

Let's see your explains. Here's mine:

# set enable_seqscan=false;  -- because I only have a few rows
SET
# insert into friend values (1,2),(2,5);
INSERT 0 2
# vacuum analyze friend;
VACUUM
# explain analyze SELECT user1_id FROM friend WHERE user2_id=2 UNION ALL select 
user2_id FROM friend WHERE user1_id=2;
QUERY PLAN

  Append  (cost=0.13..8.32 rows=2 width=4) (actual time=0.009..0.014 rows=2 
loops=1)
->  Index Only Scan using friend_user2_id_user1_id_idx on friend  
(cost=0.13..4.15 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)
  Index Cond: (user2_id = 2)
  Heap Fetches: 0
->  Index Only Scan using friend_pkey on friend friend_1  (cost=0.13..4.15 
rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)
  Index Cond: (user1_id = 2)
  Heap Fetches: 0
  Planning Time: 0.271 ms
  Execution Time: 0.045 ms
(9 rows)

Note that you have to put some actual rows in the table; if it is
completely empty, you'll not get a representative result.

Re: Make recently inserted/updated records available in the buffer/cache

[MichaelDBA]

All updated/dirty records go through PG internal memory buffer, 
shared_buffers.  Make sure that is configured optimally.  Use 
pg_buffercache extension to set it correctly.


Regards,
Michael Vitale

Hüseyin Demir wrote on 12/2/2019 12:13 PM:

I guess there won't be any adverse effect

Re: Make recently inserted/updated records available in the buffer/cache

[MichaelDBA]

Yep, I concur completely!  For tables treated like queues you gotta do 
this stuff or deal with bloat and fragmented indexes.


Michael Lewis wrote on 12/3/2019 12:29 PM:

"I am going to use it as a queue"

You may want to look at lowering fillfactor if this queue is going to 
have frequent updates, and also make autovacuum/analyze much more 
aggressive assuming many updates and deletes.

Re: autovacuum locking question

[MichaelDBA]

And Just to reiterate my own understanding of this...

autovacuum priority is less than a user-initiated request, so issuing a 
manual vacuum (user-initiated request) will not result in being cancelled.


Regards,
Michael Vitale

Jeff Janes wrote on 12/6/2019 12:47 PM:
On Fri, Dec 6, 2019 at 10:55 AM Mike Schanne > wrote:


The error is not actually showing up very often (I have 8
occurrences from 11/29 and none since then).  So maybe I should
not be concerned about it.  I suspect we have an I/O bottleneck
from other logs (i.e. long checkpoint sync times), so this error
may be a symptom rather than the cause.


I think that at the point it is getting cancelled, it has done all the 
work except the truncation of the empty pages, and reporting the 
results (for example, updating n_live_tup  and n_dead_tup).  If this 
happens every single time (neither last_autovacuum nor last_vacuum 
ever advances) it will eventually cause problems.  So this is mostly a 
symptom, but not entirely.  Simply running a manual vacuum should fix 
the reporting problem.  It is not subject to cancelling, so it will 
detect it is blocking someone and gracefully bow.  Meaning it will 
suspend the truncation, but will still report its results as normal.
Reading the table backwards in order to truncate it might be 
contributing to the IO problems as well as being a victim of those 
problems.  Upgrading to v10 might help with this, as it implemented a 
prefetch where it reads the table forward in 128kB chunks, and then 
jumps backwards one chunk at a time.  Rather than just reading 
backwards 8kB at a time.


Cheers,

Jeff

Re: TOAST table performance problem

[MichaelDBA]

Yes, I would concur that this planning time and execution time do not 
take into account the network time sending the data back to the client, 
especially since your are sending back the entire contents of the table.


Regards,
Michael Vitale

Andreas Joseph Krogh wrote on 2/7/2020 8:41 AM:
På fredag 07. februar 2020 kl. 14:23:35, skrev Asya Nevra Buyuksoy 
mailto:ayisan1...@gmail.com>>:


Sorry for the misunderstanding.
I have a table like;
CREATE TABLE zamazin
(
  paramuser_id text,
  paramperson_id integer,
  paramdata json,
  paramisdeleted boolean,
  paramactiontime timestamp without time zone
)
paramdata row size is 110KB and over.
When I execute this query like;
*select * from zamazin*
it takes *600 seconds*.
But when analyze the query ;
*"Seq Scan on public.zamazin  (cost=0.00..21.77 rows=1077
width=49) (actual time=0.008..0.151 rows=1077 loops=1)"
"  Output: paramuser_id, paramperson_id, paramdata,
paramisdeleted, paramactiontime"
"  Buffers: shared hit=11"
"Planning time: 0.032 ms"
"Execution time: 0.236 ms"*
 Why the query takes a long time, I do not understand. I
assume that this relates to the TOAST structure.

My guess is the time is spent in the /client/ retrieving the data, not 
in the DB itself. Are you on a slow network?

--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 

Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.

[MichaelDBA]

Hi all,

Since we are talking about multi-tenant databases, the citus extension 
 fits in neatly with that 
using horizontal partitioning/shards.


Regards,
Michael Vitale

Avinash Kumar wrote on 5/8/2020 6:14 AM:

Hi,

On Fri, May 8, 2020 at 3:53 AM Laurenz Albe > wrote:


On Fri, 2020-05-08 at 03:47 -0300, Avinash Kumar wrote:
> > Just set "autovacuum_max_workers" higher.
>
> No, that wouldn't help. If you just increase
autovacuum_max_workers, the total cost limit of
> autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is shared by
so many workers and it
> further delays autovacuum per each worker. Instead you need to
increase autovacuum_vacuum_cost_limit
> as well when you increase the number of workers.

True, I should have mentioned that.

> But, if you do that and also increase workers, well, you would
easily reach the limitations
> of the disk. I am not sure it is anywhere advised to have 20
autovacuum_max_workers unless
> i have a disk with lots of IOPS and with very tiny tables across
all the databases.

Sure, if you have a high database load, you will at some point
exceed the limits of
the machine, which is not surprising.  What I am trying to say is
that you have to ramp
up the resources for autovacuum together with increasing the
overall workload.
You should consider autovacuum as part of that workload.

If your machine cannot cope with the workload any more, you have
to scale, which
is easily done by adding more machines if you have many databases.

Agreed. Getting back to the original question asked by Sammy, i think 
it is still bad to create 2000 databases for storing 2000 
clients/(schemas) for a multi-tenant setup.



Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




--
Regards,
Avinash Vallarapu

Re: When to use PARTITION BY HASH?

[MichaelDBA]

Hi,

I use it quite often, since I'm dealing with partitioning keys that have 
high cardinality, ie, high number of different values.  If your 
cardinality is very high, but your spacing between values is not 
uniform, HASH will balance your partitioned tables naturally.  If your 
spacing between values is consistent, perhaps RANGE partitioning would 
be better.


Regards,
Michael Vitale

Oleksandr Shulgin wrote on 6/2/2020 1:17 PM:

Hi!

I was reading up on declarative partitioning[1] and I'm not sure what 
could be a possible application of Hash partitioning.


Is anyone actually using it? What are typical use cases?  What 
benefits does such a partitioning scheme provide?


On its face, it seems that it can only give you a number of tables 
which are smaller than the un-partitioned one, but I fail to see how 
it would provide any of the potential advantages listed in the 
documentation.


With a reasonable hash function, the distribution of rows across 
partitions should be more or less equal, so I wouldn't expect any of 
the following to hold true:
- "...most of the heavily accessed rows of the table are in a single 
partition or a small number of partitions."
- "Bulk loads and deletes can be accomplished by adding or removing 
partitions...",

etc.

That *might* turn out to be the case with a small number of distinct 
values in the partitioning column(s), but then why rely on hash 
assignment instead of using PARTITION BY LIST in the first place?


Regards,
--
Alex

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html

Re: When to use PARTITION BY HASH?

[MichaelDBA]

The article referenced below assumes a worst case scenario for 
bulk-loading with hash partitioned tables.  It assumes that the values 
being inserted are in strict ascending or descending order with no gaps 
(like a sequence number incrementing by 1), thereby ensuring every 
partition is hit in order before repeating the process.  If the values 
being inserted are not strictly sequential with no gaps, then the 
performance is much better.  Obviously, what part of the tables and 
indexes are in memory has a lot to do with it as well.


Regards,
Michael Vitale

Imre Samu wrote on 6/5/2020 7:48 AM:

> "Bulk loads ...",

As I see - There is an interesting bulkload benchmark:

"How Bulkload performance is affected by table partitioning in 
PostgreSQL" by Beena Emerson (Enterprisedb, December 4, 2019 )
/SUMMARY: This article covers how benchmark tests can be used to 
demonstrate the effect of table partitioning on performance. Tests 
using range- and hash-partitioned tables are compared and the reasons 
for their different results are explained:

                 1. Range partitions
           2. Hash partitions
                 3. Combination graphs
               4. Explaining the behavior
                 5. Conclusion/
/
/
/"For the hash-partitioned table, the first value is inserted in the 
first partition, the second number in the second partition and so on 
till all the partitions are reached before it loops back to the first 
partition again until all the data is exhausted. Thus it exhibits the 
worst-case scenario where the partition is repeatedly switched for 
every value inserted. As a result, the number of times the partition 
is switched in a range-partitioned table is equal to the number of 
partitions, while in a hash-partitioned table, the number of times the 
partition has switched is equal to the amount of data being inserted. 
This causes the massive difference in timing for the two partition 
types."/


https://www.enterprisedb.com/postgres-tutorials/how-bulkload-performance-affected-table-partitioning-postgresql

Regards,
 Imre

Re: sizing / capacity planning tipps related to expected request or transactions per second

[MichaelDBA]

Hi Dirk,

There are a bunch of other things to consider besides just TPS and size 
of database.  Since PG is process-bound, I would consider connection 
activity: How many active connections at any one time?  This greatly 
affects your CPUs.  SQL workload is another big factor: a lot of complex 
queries may use up or want to use up large amounts of work_mem, which 
greatly affects your memory capacity.


Bunch of other stuff, but these are my top 2.


Regards,
Michael Vitale

Dirk Krautschick wrote on 8/24/2020 12:39 PM:

Hi,

are there any nice rules of thumb about capacity planning in relation the 
expected
amount of transactions or request per second?

For example, if I have around 100 000 transactions per second on a 5 TB 
database.
With what amount of Memory and CPUs/Cores and which settings would you basically
Start to evaluate the performance.

Or are there any other recommendations or experiences here?

Thanks and best regards

Dirk

Re: Too many waits on extension of relation

[MichaelDBA]

Are you having locks where the type = extend?

If so, this is a symptom of slow insertsdue to many concurrent 
connections trying to insert into the same table at the same time. Each 
insert request may result in an extend lock (8k extension), which blocks 
other writers. What normally happens is that these extend locks happen 
so fast that you hardly ever see them in the*pg_locks*table, except in 
the case where many concurrent connections are trying to do inserts into 
the same table.


Regards,
Michael Vitale

Sushant Pawar wrote on 10/5/2020 1:38 PM:
We are also getting similar warning messages in the log file, for 
Insert operation as it is blocking concurrent inserts on the same 
table. As per the online documents, I have come across, suggest 
is because the Postgres process takes time to search for the relevant 
buffer in the shared_buffer area if shared_buffer is too big.


In the highly transactional system, there may not be enough free 
buffers to allocate for incoming transactions.  In our case allocated 
shared buffer is 24GB and has RAM 120GB, not sure whether we can call 
it too big but while querying pg_buffercache  has always given 
indication that 12-13GB shared_buffers would be appropriate in our 
case. I have used the below URL to evaluate the shared buffer sizing.


https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/



Best Regards,

*Sushant Pawar *



On Mon, Oct 5, 2020 at 10:14 PM Michael Lewis > wrote:


What is relation 266775 of database 196511? Is
it cms_c207c1e2_0ce7_422c_aafb_77d43f61e563.cms_item or some
system catalog table?

When I search google for "ExclusiveLock on extension of relation"
I find one thread about shared_buffers being very high but not big
enough to fit the entire data in the cluster. How much ram, what
is shared buffers and what is the total size of the database(s) on
that Postgres instance?

Re: Potential performance issues

[MichaelDBA]

Hi,

It is worthy work trying to compare performance across multiple database 
vendors, but unfortunately, it does not really come across as comparing 
apples to apples.


For instance, configuration parameters:  I do not see where you are 
doing any modification of configuration at all.  Since DBVendors are 
different in how they apply "out of the box" configuration,  this alone 
can severely affect your comparison tests even though you are using a 
standard in benchmark testing, TPCC-C.  Postgres is especially 
conservative in "out of the box" configuration.  For instance, 
"work_mem" is set to an incredibly low value of 4MB.  This has a big 
impact on many types of queries. Oracle has something called SGA_TARGET, 
which if enabled, self-regulates where the memory is utilized, thus not 
limiting query memory specifically in the way Postgres does.  This is 
just one example of a bazillion others where differences in "out of the 
box" configuration makes these tests more like comparing apples to 
oranges.  There are many other areas of configuration related to memory, 
disk, parallel execution, io concurrency, etc.


In sum, when comparing performance across different database vendors, 
there are many other factors that must be taken into account when trying 
to do an impartial comparison.  I just showed one: how configuration 
differences can skew the results.


Regards,
Michael Vitale




Jung, Jinho wrote on 2/28/2021 10:04 AM:

# Performance issues discovered from differential test

Hello. We are studying DBMS from GeorgiaTech and reporting interesting 
queries that potentially show performance problems.


To discover such cases, we used the following procedures:

* Install four DBMSs with the latest version (PostgreSQL, SQLite, 
MySQL, CockroachDB)

* Import TPCC-C benchmark for each DBMS
* Generate random query (and translate the query to handle different 
dialects)

* Run the query and measure the query execution time
   * Remove `LIMIT` to prevent any non-deterministic behaviors
   * Discard the test case if any DBMS returned an error
   * Some DBMS does not show the actual query execution time. In this 
case, query the `current time` before and after the actual query, and 
then we calculate the elapsed time.


In this report, we attached a few queries. We believe that there are 
many duplicated or false-positive cases. It would be great if we can 
get feedback about the reported queries. Once we know the root cause 
of the problem or false positive, we will make a follow-up report 
after we remove them all.


For example, the below query runs x1000 slower than other DBMSs from 
PostgreSQL.


    select ref_0.ol_amount as c0
    from order_line as ref_0
        left join stock as ref_1
          on (ref_0.ol_o_id = ref_1.s_w_id )
        inner join warehouse as ref_2
        on (ref_1.s_dist_09 is NULL)
    where ref_2.w_tax is NULL;


* Query files link:

wget https://gts3.org/~jjung/report1/pg.tar.gz

* Execution result (execution time (second))

| Filename | Postgres |   Mysql  | Cockroachdb |  Sqlite  |   Ratio  |
|-:|-:|-:|:|-:|-:|
|    34065 |  1.31911 |    0.013 |     0.02493 |    1.025 | 101.47 |
|    36399 |  3.60298 |    0.015 |     1.05593 |    3.487 | 240.20 |
|    35767 |  4.01327 |    0.032 |     0.00727 |    2.311 | 552.19 |
|    11132 |   4.3518 |    0.022 |     0.00635 |    3.617 | 684.88 |
|    29658 |   4.6783 |    0.034 |     0.00778 |     2.63 | 601.10 |
|    19522 |  1.06943 |    0.014 |     0.00569 |   0.0009 |  1188.26 |
|    38388 |  3.21383 |    0.013 |     0.00913 |    2.462 | 352.09 |
|     7187 |  1.20267 |    0.015 |     0.00316 |   0.0009 |  1336.30 |
|    24121 |  2.80611 |    0.014 |     0.03083 |    0.005 | 561.21 |
|    25800 |  3.95163 |    0.024 |     0.73027 |    3.876 | 164.65 |
|     2030 |  1.91181 |    0.013 |     0.04123 |    1.634 | 147.06 |
|    17383 |  3.28785 |    0.014 |     0.00611 |      2.4 | 538.45 |
|    19551 |  4.70967 |    0.014 |     0.00329 |   0.0009 |  5232.97 |
|    26595 |  3.70423 |    0.014 |     0.00601 |    2.747 | 615.92 |
|      469 |  4.18906 |    0.013 |     0.12343 |    0.016 | 322.23 |


# Reproduce: install DBMSs, import TPCC benchmark, run query

### Cockroach (from binary)

```sh
# install DBMS
wget https://binaries.cockroachdb.com/cockroach-v20.2.5.linux-amd64.tgz
tar xzvf cockroach-v20.2.5.linux-amd64.tgz
sudo cp -i cockroach-v20.2.5.linux-amd64/cockroach 
/usr/local/bin/cockroach20


sudo mkdir -p /usr/local/lib/cockroach
sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos.so 
/usr/local/lib/cockroach/
sudo cp -i cockroach-v20.2.5.linux-amd64/lib/libgeos_c.so 
/usr/local/lib/cockroach/


# test
which cockroach20
cockroach20 demo

# start the DBMS (to make initial node files)
cd ~
cockroach20 start-single-node --insecure --store=node20 
--listen-addr=localhost:26259 --http-port=28080 --max-sql-memory=1GB 
--background

# quit
cockroach20 quit --insecure --host=localhos

Re: Potential performance issues

[MichaelDBA]

Ha, Andrew beat me to the punch!

Andrew Dunstan wrote on 3/1/2021 7:59 AM:

On 2/28/21 10:04 AM, Jung, Jinho wrote:

# install DBMS
sudo su
make install
adduser postgres
rm -rf /usr/local/pgsql/data
mkdir /usr/local/pgsql/data
chown -R postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb jjung


Using an untuned Postgres is fairly useless for a performance test. Out
of the box, shared_buffers and work_mem are too low for almost all
situations, and many other settings can also usually be improved. The
default settings are deliberately very conservative.


cheers


andrew



-- Andrew Dunstan EDB: https://www.enterprisedb.com

Re: AWS forcing PG upgrade from v9.6 a disaster

[MichaelDBA]

Hi Lance,

Did you customize the PG 12 DB Parameter group to be in sync as much as 
possible with the 9.6 RDS version?  Or are you using PG12 default DB 
Parameter group?


Are you using the same AWS Instance Class?

Did you vacuum analyze all your tables after the upgrade to 12?

Regards,
Michael Vitale

Campbell, Lance wrote on 5/28/2021 3:18 PM:


Also, did you check your RDS setting in AWS after upgrading?  I run 
four databases in AWS.  I found that the work_mem was set way low 
after an upgrade.  I had to tweak many of my settings.


Lance

*From: *Andrew Dunstan 
*Date: *Friday, May 28, 2021 at 2:08 PM
*To: *Dean Gibson (DB Administrator) , 
pgsql-performance@lists.postgresql.org 


*Subject: *Re: AWS forcing PG upgrade from v9.6 a disaster


On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
> [Reposted to the proper list]
>
> I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
> at one point), gradually moving to v9.0 w/ replication in 2010.  In
> 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
> & was entirely satisfied with the result.
>
> In March of this year, AWS announced that v9.6 was nearing end of
> support, & AWS would forcibly upgrade everyone to v12 on January 22,
> 2022, if users did not perform the upgrade earlier.  My first attempt
> was successful as far as the upgrade itself, but complex queries that
> normally ran in a couple of seconds on v9.x, were taking minutes in v12.
>
> I didn't have the time in March to diagnose the problem, other than
> some futile adjustments to server parameters, so I reverted back to a
> saved copy of my v9.6 data.
>
> On Sunday, being retired, I decided to attempt to solve the issue in
> earnest.  I have now spent five days (about 14 hours a day), trying
> various things, including adding additional indexes.  Keeping the v9.6
> data online for web users, I've "forked" the data into new copies, &
> updated them in turn to PostgreSQL v10, v11, v12, & v13.  All exhibit
> the same problem:  As you will see below, it appears that versions 10
> & above are doing a sequential scan of some of the "large" (200K rows)
> tables.  Note that the expected & actual run times both differ for
> v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
> a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
> ANALYZE" from both v9.6 & v13.2, followed by the related table & view
> definitions.  With one exception, table definitions are from the FCC
> (Federal Communications Commission);  the view definitions are my own.
>
>
>

Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?


cheers


andrew



--
Andrew Dunstan
EDB: 
https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$ 
 

temporary file log lines

[MichaelDBA]

Hi all,

I got a question about PG log lines with temporary file info like this:

case 1: log line with no contextual info
2021-07-07 20:28:15 UTC:10.100.11.95(50274):myapp@mydb:[35200]:LOG: 
temporary file: path "base/pgsql_tmp/pgsql_tmp35200.0", size 389390336


case 2: log line with contextual info
2021-07-07 20:56:18 UTC:172.16.193.118(56080):myapp@mydb:[22418]:LOG: 
temporary file: path "base/pgsql_tmp/pgsql_tmp22418.0", size 1048576000
2021-07-07 20:56:18 
UTC:172.16.193.118(56080):myapp@mydb:[22418]:CONTEXT:  PL/pgSQL function 
memory.f_memory_usage(boolean) line 13 at RETURN QUERY


There are at least 2 cases where stuff can spill over to disk:
* queries that don't fit in work_mem, and
* temporary tables that don't fit in temp_buffers

Question, if log_temp_files is turned on (=0), then how can you tell 
from where the temporary log line comes from?
I see a pattern where work_mem spill overs have a CONTEXT line that 
immediately follows the LOG LINE with keyword, temporary. See case 2 above.


For other LOG lines with keyword, temporary, there is no such pattern. 
Could those be the ones caused by temp_buffer spill overs to disk?  case 
1 above.


I really want to tune temp_buffers, but I would like to be able to 
detect when temporary tables are spilling over to disk, so that I can 
increase temp_buffers.


Any help would be appreciated.

Regards,
Michael Vitale

Re: temporary file log lines

[MichaelDBA]

hmmm, I think spilling over to disk for temporary tables is handled by 
an entirely different branch in the PG source code.  In fact, some other 
folks have chimed in and said log_temp_files doesn't relate to temp 
files at all use by temporary tables, just queries as you mentioned 
below elsewhere.  This seems to be a dark area of PG that is not 
convered well.


Regards,
Michael Vitale


Laurenz Albe wrote on 7/12/2021 8:01 AM:

On Thu, 2021-07-08 at 17:22 -0400, MichaelDBA wrote:

I got a question about PG log lines with temporary file info like this:

case 1: log line with no contextual info
2021-07-07 20:28:15 UTC:10.100.11.95(50274):myapp@mydb:[35200]:LOG:
temporary file: path "base/pgsql_tmp/pgsql_tmp35200.0", size 389390336

case 2: log line with contextual info
2021-07-07 20:56:18 UTC:172.16.193.118(56080):myapp@mydb:[22418]:LOG:
temporary file: path "base/pgsql_tmp/pgsql_tmp22418.0", size 1048576000
2021-07-07 20:56:18
UTC:172.16.193.118(56080):myapp@mydb:[22418]:CONTEXT:  PL/pgSQL function
memory.f_memory_usage(boolean) line 13 at RETURN QUERY

There are at least 2 cases where stuff can spill over to disk:
* queries that don't fit in work_mem, and
* temporary tables that don't fit in temp_buffers

Question, if log_temp_files is turned on (=0), then how can you tell
from where the temporary log line comes from?
I see a pattern where work_mem spill overs have a CONTEXT line that
immediately follows the LOG LINE with keyword, temporary. See case 2 above.

For other LOG lines with keyword, temporary, there is no such pattern.
Could those be the ones caused by temp_buffer spill overs to disk?  case
1 above.

I really want to tune temp_buffers, but I would like to be able to
detect when temporary tables are spilling over to disk, so that I can
increase temp_buffers.

Any help would be appreciated.

I am not sure if you can istinguish those two cases from the log.

What I would do is identify the problematic query and run it with
EXPLAIN (ANALYZE, BUFFERS).  Then you should see which part of the query
creates the temporary files.

If it is a statement in a function called from your top level query,
auto_explain with the correct parameters can get you that output for
those statements too.

Yours,
Laurenz Albe

Re: Lock contention high

[MichaelDBA]

1.Is there a way to tune the lock contention ?
2.Is any recommendations to tune/reduce the lock contention via postgres.conf

I think you'd want to find *which* LW locks are being waited on, to see if it's
something that can be easily tuned.

You can check pg_stat_activity, or maybe create a cronjob to record its content
for later analysis.


Hello,

Also turn on log_lock_waits so you can evaluate the actual SQL causing 
the problems in the PG log files.  Thinking ahead, you may want to 
consider if using advisory locks from the application side of things 
might be helpful to manage locks in a more pessimistic way.  Also, join 
with pg_locks table to find out the specific resources that are in 
contention.


Regards,
Michael Vitale

Re: Slow plan choice with prepared query

[MichaelDBA]

Dag, if you ain't right!  I can duplicate this on the ones I tested 
with: PG v11 and v14.  Gonna start diving into this myself...


Regards,
Michael Vitale


Mark Saward wrote on 2/23/2022 10:37 PM:

Hi,


I've experienced a situation where the planner seems to make a very 
poor choice with a prepared query after the first five executions.  
Looking at the documentation, I think this happens because it switches 
from a custom plan to a generic one, and doesn't make a good choice 
for the generic one.


Postgres version: running in docker, reports to be 'Debian 
14.1-1.pgdg110+1'


If I force it to use a custom plan via 'set local plan_cache_mode = 
force_custom_plan', then I don't notice any slowdown.  Without it, the 
6th and onwards calls can take 1 second to 15 seconds each, as opposed 
to about 10ms.


Since I have a workaround, I don't necessarily need assistance, but 
posting this here in case it's of value as a test case. Here's a test 
case that reliably duplicates this issue for me:




create table test (
  test_id serial primary key,
  data text
);

insert into test (data) (select data from (select 
generate_series(1,1) AS id, md5(random()::text) AS data) x);


prepare foo_test(text, text, int, text, bool) as SELECT * FROM (SELECT
  *,
  count(*) OVER () > $3 AS has_more,
  row_number() OVER ()
  FROM (
    WITH counted AS (
  SELECT count(*) AS total
  FROM   (select test_id::text, data
from test
where
  (cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
  (cast($2 as text) is null or lower(test_id::text) like '%' || 
lower($2) || '%')) base

    ), cursor_row AS (
  SELECT base.test_id
  FROM   (select test_id::text, data
from test
where
  (cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
  (cast($2 as text) is null or lower(test_id::text) like '%' || 
lower($2) || '%')) base

  WHERE  base.test_id = $4
    )
    SELECT counted.*, base.*
  FROM   (select test_id::text, data
from test
where
  (cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
  (cast($2 as text) is null or lower(test_id::text) like '%' || 
lower($2) || '%')) base

  LEFT JOIN   cursor_row ON true
  LEFT JOIN   counted ON true
  WHERE ((
    $4 IS NULL OR cast($5 as bool) IS NULL
  ) OR (
    (base.test_id)
  > (cursor_row.test_id)
  ))
  ORDER BY base.test_id ASC
  LIMIT $3 + 1
) xy LIMIT $3 ) z ORDER BY row_number ASC;

\timing

execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);

-- This one should be slower:
execute foo_test(null, null, 5, 500, true);

Re: Slow plan choice with prepared query

[MichaelDBA]

As per PG official documentation on PREPARE, it is working as expected.  
Use custom plan, but after 5th iteration compare cost of custom plan vs 
generic plan and use the one with the less cost which is the generic 
plan even though it is not as performant. Look at explain output to see 
the diffs between 5th iteration and 6th one:  explain (analyze, summary, 
buffers true) execute foo_test(null, null, 5, 500, true);


It appears the SORT is the problem and a mismatch between text and 
integer for base.text_id? --> WHERE  base.test_id = $4


Regards,
Michael Vitale



MichaelDBA wrote on 2/24/2022 1:45 PM:
Dag, if you ain't right!  I can duplicate this on the ones I tested 
with: PG v11 and v14.  Gonna start diving into this myself...


Regards,
Michael Vitale


Mark Saward wrote on 2/23/2022 10:37 PM:

Hi,


I've experienced a situation where the planner seems to make a very 
poor choice with a prepared query after the first five executions. 
Looking at the documentation, I think this happens because it 
switches from a custom plan to a generic one, and doesn't make a good 
choice for the generic one.


Postgres version: running in docker, reports to be 'Debian 
14.1-1.pgdg110+1'


If I force it to use a custom plan via 'set local plan_cache_mode = 
force_custom_plan', then I don't notice any slowdown.  Without it, 
the 6th and onwards calls can take 1 second to 15 seconds each, as 
opposed to about 10ms.


Since I have a workaround, I don't necessarily need assistance, but 
posting this here in case it's of value as a test case. Here's a test 
case that reliably duplicates this issue for me:




create table test (
  test_id serial primary key,
  data text
);

insert into test (data) (select data from (select 
generate_series(1,1) AS id, md5(random()::text) AS data) x);


prepare foo_test(text, text, int, text, bool) as SELECT * FROM (SELECT
  *,
  count(*) OVER () > $3 AS has_more,
  row_number() OVER ()
  FROM (
    WITH counted AS (
  SELECT count(*) AS total
  FROM   (select test_id::text, data
from test
where
  (cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
  (cast($2 as text) is null or lower(test_id::text) like '%' || 
lower($2) || '%')) base

    ), cursor_row AS (
  SELECT base.test_id
  FROM   (select test_id::text, data
from test
where
  (cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
  (cast($2 as text) is null or lower(test_id::text) like '%' || 
lower($2) || '%')) base

  WHERE  base.test_id = $4
    )
    SELECT counted.*, base.*
  FROM   (select test_id::text, data
from test
where
  (cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
  (cast($2 as text) is null or lower(test_id::text) like '%' || 
lower($2) || '%')) base

  LEFT JOIN   cursor_row ON true
  LEFT JOIN   counted ON true
  WHERE ((
    $4 IS NULL OR cast($5 as bool) IS NULL
  ) OR (
    (base.test_id)
  > (cursor_row.test_id)
  ))
  ORDER BY base.test_id ASC
  LIMIT $3 + 1
) xy LIMIT $3 ) z ORDER BY row_number ASC;

\timing

execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);

-- This one should be slower:
execute foo_test(null, null, 5, 500, true);

Re: many sessions wait on LWlock WALWrite suddenly

[MichaelDBA]

LWLock always shows up in the case where you have too many concurrent
active connections.  Do a select from the pg_stat_activity table where
state in ('idle in transaction','active'); Then count how many CPUs you
have. If the sql query count returned is greater than 2-3 times the
number of CPUs, you probably have a CPU overload problem and your
solution may be to add a connection pooler between the client and the DB
server.  This is all due to the nature of how PG is architected: every
connection is a process, not a thread.

Regards,
Michael Vitale

James Pang wrote on 4/11/2025 10:36 AM:

   pgv14.8 , during peak time, we suddenly see hundreds of active
sessions waiting on LWlock  WALWrite at the same time, but we did not
find any issue on storage .
any suggestions ?

Thanks,

James



Regards,

Michael Vitale

michael...@sqlexec.com 

703-600-9343

Re: DB connection issue suggestions

[MichaelDBA Vitale]

 
 
  
   Please show output of "show max_connections" to validate your assumptions.
  
  
   
On 05/10/2022 12:59 PM Sudhir Guna  wrote:
   
   

   
   

   
   

 Dear All,


 


 We have recently upgraded Postgresql 9.4 standalone server to Postgresql 11.2 with High Availability (2 servers : Master and Standby).


 


 While trying to test using ETL applications and reports, we observe that the ETL jobs fails with below error,


 


 2022/05/06 16:27:36 - Error occurred while trying to connect to the database
 2022/05/06 16:27:36 - Error connecting to database: (using class org.postgresql.Driver)
 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already


 


 We have increased the max_connections = 1000 in postgresql.conf file.


 


 It worked ok for a day and later we get the same error message.


 


 Please help to advise on any additional settings required. The prior Postgresql 9.4 had the default max_connections = 100 and the applications worked fine.


 


 Regards,


 Guna