significant jump in sql statement timing for on server vs a remote connection

[Sbob]

Hi all;


We are debugging a sql performance issue. We have a sql file with 50,000 
simple select statements in it. If I run the file locally it completes 
in less than 15sec.  If I force the local connection to be a tcp/ip 
connection via psql -h and I get approximately the same results, 15 - 16sec.



However if we move the file to another server in the same network and 
run with a psql -h then it runs for more than 10min. Are there any 
postgres specific issues / settings / connection overhead  we should 
look at? Or is this simply a network issue and fully outside the scope 
of the postgres database?



FYI:

postgresql 13

1.5TB of RAM

512GB of buffer_pool

10GB of work_mem



Thanks in advance

Re: significant jump in sql statement timing for on server vs a remote connection

[Sbob]

On 4/19/22 22:17, Jeff Janes wrote:

On Tue, Apr 19, 2022 at 5:00 PM Sbob  wrote:


However if we move the file to another server in the same network and
run with a psql -h then it runs for more than 10min.


What is the ping time?  Packet loss? You can't take for granted that 
the network is good and fast just because they are on the same LAN.


Cheers,

Jeff



Here is the ping stats:

--- db-primary ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3000ms
rtt min/avg/max/mdev = 0.304/0.348/0.400/0.039 ms


This seems pretty good yes?Anything else I could look at?

checking for a NULL date in a partitioned table kills performance (accidentally sent to the admin list before)

[Sbob]

All;

I am running a select from a partitioned table. The table (and all the 
partitions) have an index on contract_date like this:
CREATE INDEX on part_tab (contract_date) where contract_date > 
'2022-01-01'::date


The table (including all partitions) has 32million rows
The db server is an aurora postgresql instance with 128GB of ram and 16 
vcpu's


The shared buffers is set to 90GB and effective_cache_size is also 90GB
I set default_statistics_target to 1000 and ram a vacuum analyze on the 
table


I am selecting a number of columns and specifying this where clause:

WHERE (
    (contract_date IS NULL)
    OR
    (contract_date > '2022-01-01'::date)
 )

This takes 15 seconds to run and an explain says it's doing a table scan 
on all partitions (the query is not specifying the partition key)

If I change the where clause to look like this:

WHERE (
  (contract_date > '2022-01-01'::date)
 )

Then it performs index scans on all the partitions and runs in about 600ms

If i leave the where clause off entirely it performs table scans of the 
partitions and takes approx 18 seconds to run


I am trying to get the performance to less than 2sec,
I have tried adding indexes on the table and all partitions like this:
CREATE INDEX ON table (contract_date NULLS FIRST) ;
but the performance with the full where clause is the same:

WHERE (
    (contract_date IS NULL)
    OR
    (contract_date > '2022-01-01'::date)
 )

runs in 15 seconds and scans all partitions

I also tried indexes i=on the table and all partitions like this:
CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;

but I get the same result, table scans on all partitions and it runs in 
15 seconds


Any help or advice ?

Thanks in advance

Re: EnterpriseDB

[sbob]

EnterpriseDB is basically postgres with the added oracle compatability 
and some added external tools. THe default user & db will no longer be 
postgres but 'enterprisedb', but it is still postgresql so you won't 
have any issues working with EDB if you already know postgres




On 9/13/21 20:52, Mladen Gogala wrote:
The company I work for will test EnterpriseDB. I am fairly well 
acquainted with Postgres but have no experience whatsoever with 
EnterpriseDB. How compatible to Postgres it is? Do pgAdmin4 and 
pgBadger work with EnterpriseDB? Are psql commands the same? Can 
anyone here share some impressions?


Regards

PostgreSQL and Linux CPU's

[Sbob]

All;


I am looking for information on how PostgreSQL leverages or interfaces 
with CPU's on Linux. Does PostgreSQL let Linux do the work? Does it 
bypass the OS? Any information or docs you can send my way would be much 
appreciated.



Thanks in advance