Re: Performance implications of 8K pread()s

[Dimitrios Apostolou]

Exciting! Since I still have the same performance issues on compressed btrfs, 
I'm looking forward to testing the patches, probably when a 17 Beta is out and 
I can find binaries on my platform (OpenSUSE). It looks like it will make a 
huge difference.

Thank you for persisting and getting this through.

Dimitris


On 12 April 2024 07:45:52 CEST, Thomas Munro  wrote:
>On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou  wrote:
>> So would it make sense for postgres to perform reads in bigger blocks? Is it
>> easy-ish to implement (where would one look for that)? Or must the I/O unit 
>> be
>> tied to postgres' page size?
>
>FYI as of last week we can do a little bit of that on the master branch:
>
>postgres=# select count(*) from t;
>
>preadv(46, ..., 8, 256237568) = 131072
>preadv(46, ..., 5, 256368640) = 131072
>preadv(46, ..., 8, 256499712) = 131072
>preadv(46, ..., 5, 256630784) = 131072
>
>postgres=# set io_combine_limit = '256k';
>postgres=# select count(*) from t;
>
>preadv(47, ..., 5, 613728256) = 262144
>preadv(47, ..., 5, 613990400) = 262144
>preadv(47, ..., 5, 614252544) = 262144
>preadv(47, ..., 5, 614514688) = 262144
>
>Here's hoping the commits implementing this stick, for the PostgreSQL
>17 release.  It's just the beginning though, we can only do this for
>full table scans so far (plus a couple of other obscure places).
>Hopefully in the coming year we'll get the "streaming I/O" mechanism
>that powers this hooked up to lots more places... index scans and
>other stuff.  And writing.  Then eventually pushing the I/O into the
>background.  Your questions actually triggered us to talk about why we
>couldn't switch a few things around in our project and get the I/O
>combining piece done sooner.  Thanks!

Re: Performance implications of 8K pread()s

[Thomas Munro]

On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou  wrote:
> So would it make sense for postgres to perform reads in bigger blocks? Is it
> easy-ish to implement (where would one look for that)? Or must the I/O unit be
> tied to postgres' page size?

FYI as of last week we can do a little bit of that on the master branch:

postgres=# select count(*) from t;

preadv(46, ..., 8, 256237568) = 131072
preadv(46, ..., 5, 256368640) = 131072
preadv(46, ..., 8, 256499712) = 131072
preadv(46, ..., 5, 256630784) = 131072

postgres=# set io_combine_limit = '256k';
postgres=# select count(*) from t;

preadv(47, ..., 5, 613728256) = 262144
preadv(47, ..., 5, 613990400) = 262144
preadv(47, ..., 5, 614252544) = 262144
preadv(47, ..., 5, 614514688) = 262144

Here's hoping the commits implementing this stick, for the PostgreSQL
17 release.  It's just the beginning though, we can only do this for
full table scans so far (plus a couple of other obscure places).
Hopefully in the coming year we'll get the "streaming I/O" mechanism
that powers this hooked up to lots more places... index scans and
other stuff.  And writing.  Then eventually pushing the I/O into the
background.  Your questions actually triggered us to talk about why we
couldn't switch a few things around in our project and get the I/O
combining piece done sooner.  Thanks!

Re: Performance

[Samed YILDIRIM]

Hi Mehmet,

On Wed, 31 Jan 2024 at 13:33, Mehmet COKCEVIK 
wrote:

> Hi,
> We want to work with PostgreSQL in our new project. I need your opinion on
> the best way to create a database.
>

First of all, congratulations on your decision to use PostgreSQL for your
new project. :)


> Description of our Project:
> It will be in Client/Server Architecture. Windows Application users will
> access the server as clients and they are all in different locations. There
> will be a simple ERP system that will perform CRUD transactions and report
> them.
>

I hope you are not thinking of keeping business logic on the application
side and querying the database from different locations. If you treat the
database as a regular application's database and run multiple DML's for
each request through the internet, performance of the application will be
horrible due to latency between the application and the database. In case
you plan to use such a model, the best approach would be to decrease the
number of queries as much as possible, and achieve multiple operations by a
single request, instead of reading from multiple tables, doing some
calculations, writing back something to the database etc. I would move the
logic to the database side as much as possible and do function/procedure
calls, or have an application nearby the database and make clients'
applications interact with it. So, the business logic would still be in an
application and close to the database.


> We are considering connecting to the Embarcadero Firedac dataset. We can
> also connect clients with PosgreRestAPI.
> Our number of clients can be between 5k-20K.
> We have a maximum of 200 tables consisting of invoice, order, customer,
> bank and stock information. I can create a second Postgre SQL for reporting
> if necessary.
>

This is an interesting point. Because, if you plan to have 20k clients, you
should also be planning high availability, backups, replications etc.
Serving 20k clients with a standalone server would not be something I would
like to involve :)


> Question 1 :
> Should we install PostgreSQL on Windows server operating system or Linux
> operating system?
>

My personal opinion, this is not even a question. The answer is and will
always be Linux for me :D
However, the actual question is what is the cost of managing a Linux server
for you. If you are not familiar with Linux, if you don't have any
experience with linux, and if you don't have a company or budget to
hire/work with you on this who is a professional linux or PostgreSQL admin,
going with Windows is a much more sensible option for you even though it is
not the best OS or not the best performing option for PostgreSQL.


> 2:
> Is it correct to open a field named client_id for each table, for example
> the customer table, and use this field in CRUD operations to host the same
> single customer table for all users?
>

It depends on the data size and your project's isolation/security
requirements. You may also consider partitioning and row level security
features of PostgreSQL. There is not a single recipe that is good for all
multi-tenancy needs. :)


> 3:
> Create a separate table for each User? (result: 5000 users x 200 Tables =
> 1,000,000 tables)
> 4:
> Create a database per user? (result: 5000 databases)
> 5:
> Is each user a separate schema? (result: 5000 schemas)
>
> Can you share your ideas with me?
> Thank you.
>

Best regards.
Samed YILDIRIM

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

[Tom Lane]

Jean-Christophe Boggio  writes:
> I did upgrade :-) But we have many users for which we don't decide on 
> when they do upgrade so we have to keep compatibility with most versions 
> of PG and in that particular case (non-existence of the materialized 
> keyword for PG 11 and before) it is a real problem.

PG 11 is out of support as of earlier this month, so your users really
need to be prioritizing getting onto more modern versions.

regards, tom lane

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

[Jean-Christophe Boggio]

Andreas,

Le 22/11/2023 à 15:25, Andreas Kretschmer a écrit :
Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio:  >> Also, adding "materialized" to both "withcwrack" and "withcwrack0" 
>> CTEs gets the result in acceptable timings (a few seconds). The >> 
problem with this is that we have some clients with older versions >> of 
PG and I guess blindly adding the "materialized" keyword will >> cause 
errors. > yeah, prior to 11 CTEs are a optimizer barrier. You can try to 
> rewrite the queries to not using CTEs - or upgrade. If i were you i > 
would upgrade.
I did upgrade :-) But we have many users for which we don't decide on 
when they do upgrade so we have to keep compatibility with most versions 
of PG and in that particular case (non-existence of the materialized 
keyword for PG 11 and before) it is a real problem.


Best regards,

JC

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

[Andreas Kretschmer]

Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio:



Also, adding "materialized" to both "withcwrack" and "withcwrack0" 
CTEs gets the result in acceptable timings (a few seconds). The 
problem with this is that we have some clients with older versions of 
PG and I guess blindly adding the "materialized" keyword will cause 
errors.




yeah, prior to 11 CTEs are a optimizer barrier. You can try to rewrite 
the queries to not using CTEs - or upgrade. If i were you i would upgrade.



Regards, Andreas

--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

[Jean-Christophe Boggio]

John,

Le 22/11/2023 à 14:30, John Naylor a écrit :
Note that "vacuum full" is not recommended practice in most  > situations. Among the downsides, it removes the visibility map, > 
which is necessary to allow index-only scans. Plain vacuum should > 
always be used except for certain dire situations. Before proceeding > 
further, please perform a plain vacuum on the DB. After that, check > if 
there are still problems with your queries.
Did both VACUUM ANALYZE and VACUUM (which one did you recommend 
exactly?) and things go much faster now, thanks a lot. I will also check 
why autovacuum did not do its job.


Is there anything I can do to prevent that kind of behaviour ? I'm  >> a little afraid to have to review all the queries in my softwares 
>> to keep good performances with PG 15 ? Maybe there's a way to >> 
configure the server so that CTEs are materialized by default ? > > 
There is no such a way. It would be surely be useful for some users > to 
have a way to slowly migrate query plans to new planner versions, > but 
that's not how it works today.
Thanks for your input so I know I did not miss a parameter. And yes, 
that would be handy.


Best regards,

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

[John Naylor]

On Wed, Nov 22, 2023 at 6:39 PM Jean-Christophe Boggio
 wrote:
>
> Hello,
>
> I just switched from PG11 to PG15 on our production server (Version is
> 15.5). Just made a vacuum full analyze on the DB.

Note that "vacuum full" is not recommended practice in most
situations. Among the downsides, it removes the visibility map, which
is necessary to allow index-only scans. Plain vacuum should always be
used except for certain dire situations. Before proceeding further,
please perform a plain vacuum on the DB. After that, check if there
are still problems with your queries.

> Also, adding "materialized" to both "withcwrack" and "withcwrack0" CTEs
> gets the result in acceptable timings (a few seconds). The problem with
> this is that we have some clients with older versions of PG and I guess
> blindly adding the "materialized" keyword will cause errors.

Yes, meaning 11 and earlier don't recognize that keyword keyword.

> Is there anything I can do to prevent that kind of behaviour ? I'm a
> little afraid to have to review all the queries in my softwares to keep
> good performances with PG 15 ? Maybe there's a way to configure the
> server so that CTEs are materialized by default ?

There is no such a way. It would be surely be useful for some users to
have a way to slowly migrate query plans to new planner versions, but
that's not how it works today.

Re: Performance problems with Postgres JDBC 42.4.2

[Dave Cramer]

On Mon, 6 Nov 2023 at 09:59, Jose Osinde  wrote:

>
> Dear all,
>
> I'm running a query  from Java on a postgres database:
>
> Java version: 17
> JDBC version: 42.4.2
> Postgres version: 13.1
>
> In parallel I'm testing the same queries from pgAdmin 4 version 6.13
>
> The tables I'm using contains more than 10million rows each and I have two
> questions here:
>
> 1. I need to extract the path of a file without the file itself. For this
> I use two alternatives as I found that sentence "A" is much faster than
> the "B" one:
>
> "A" sentence:
>
> SELECT DISTINCT ( LEFT(opf.file_path, length(opf.file_path) - position('/'
> in reverse(opf.file_path))) ) AS path
>FROM product AS op JOIN product_file AS opf ON
> opf.product_id = op.id
>WHERE op.proprietary_end_date <= CURRENT_DATE
> AND op.id LIKE 'urn:esa:psa:%'
>
> "B" sentence:
>
> SELECT DISTINCT ( regexp_replace(opf.file_path, '(.*)\/(.*)$', '\1') ) AS
> path
>FROM product AS op JOIN product_file AS opf ON
> opf.product_id = op.id
>WHERE op.proprietary_end_date <= CURRENT_DATE
> AND op.id LIKE 'urn:esa:psa:%'
>
> 2. Running sentence "A" on the pgAdmin client takes 4-5 minutes to finish
> but running it from a Java program it never ends. This is still the case
> when I limit the output to the first 100 rows so I assume this is not a
> problem with the amount of data being transferred but the way postgres
> resolve the query. To make it work in Java I had to define a postgres
> function that I call from the Java code instead of running the query
> directly.
>
> I had a similar problem in the past with a query that performed very
> poorly from a Java client while it was fine from pgAdmin or a python
> script. In that case it was a matter of column types not compatible with
> the JDBC (citext) deriving in an implicit cast that prevented the
> postgres engine from using a given index or to cast all the values of that
> column before using it, not sure now. But I don't think this is not the
> case here.
>
> Could anyone help me again?
>

Can you share your java code ?

If you are using a PreparedStatement the driver will use the extended
protocol which may be slower. Statements use SimpleQuery which is faster
and more like pgadmin

Issuing a Query and Processing the Result | pgJDBC (postgresql.org)



Dave

>
>

Re: [EXTERNAL] Re: Performance down with JDBC 42

[David Rowley]

On Mon, 6 Nov 2023 at 08:37, Abraham, Danny  wrote:
>
> Both plans refer to the same DB.

JDBC is making use of PREPARE statements, whereas psql, unless you're
using PREPARE is not.

> #1 – Fast – using psql or old JDBC driver

The absence of any $1 type parameters here shows that's a custom plan
that's planned specifically using the parameter values given.

> Slow – when using JDBC 42

Because this query has $1, $2, etc, that's a generic plan. When
looking up statistics histogram bounds and MCV slots cannot be
checked. Only ndistinct is used. If you have a skewed dataset, then
this might not be very good.

You might find things run better if you adjust postgresql.conf and set
plan_cache_mode = force_custom_plan then select pg_reload_conf();

Please also check the documentation so that you understand the full
implications for that.

David

RE: [EXTERNAL] Re: Performance down with JDBC 42

[Abraham, Danny]

Thanks for the help.
Both plans refer to the same DB.

#1 – Fast – using psql or old JDBC driver
==>
Sort  (cost=13113.27..13113.33 rows=24 width=622)
   Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character 
varying(400))
   Sort Key: dm.calname, dm.jobyear
   ->  HashAggregate  (cost=13112.24..13112.48 rows=24 width=622)
 Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character 
varying(400))
 Group Key: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
 ->  Append  (cost=4603.96..13112.00 rows=24 width=622)
   ->  Unique  (cost=4603.96..4604.20 rows=19 width=535)
 Output: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
 ->  Sort  (cost=4603.96..4604.01 rows=19 width=535)
   Output: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
   Sort Key: dm.calname, dm.jobyear, dm.caltype, 
((dm.daymask)::character varying(400))
   ->  Nested Loop  (cost=0.00..4603.56 rows=19 
width=535)
 Output: dm.calname, dm.jobyear, dm.caltype, 
(dm.daymask)::character varying(400)
 Join Filter: (((dm.calname)::text = 
(jd.dayscal)::text) OR ((dm.calname)::text = (jd.weekcal)::text) OR 
((dm.calname)::text = (jd.confcal)::text))
 ->  Seq Scan on public.cms_datemm dm  
(cost=0.00..16.33 rows=171 width=389)
   Output: dm.calname, dm.jobyear, 
dm.daymask, dm.caltype, dm.caldesc
   Filter: ((dm.jobyear >= '2021'::bpchar) 
AND (dm.jobyear <= '2025'::bpchar))
 ->  Materialize  (cost=0.00..4559.84 rows=8 
width=3)
   Output: jd.dayscal, jd.weekcal, 
jd.confcal
   ->  Seq Scan on public.cms_jobdef jd  
(cost=0.00..4559.80 rows=8 width=3)
 Output: jd.dayscal, jd.weekcal, 
jd.confcal
 Filter: (((jd.schedtab)::text = 
'PZL-ZETA_REDIS_UTILITY_PSE'::text) OR ((jd.schedtab)::text ~~ 
'PZL-ZETA_REDIS_UTILITY_PSE/%'::text))
   ->  Unique  (cost=3857.44..3857.46 rows=1 width=535)
 Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, 
((dm_1.daymask)::character varying(400))
 ->  Sort  (cost=3857.44..3857.45 rows=1 width=535)
   Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, 
((dm_1.daymask)::character varying(400))
   Sort Key: dm_1.calname, dm_1.jobyear, dm_1.caltype, 
((dm_1.daymask)::character varying(400))
   ->  Nested Loop  (cost=0.30..3857.43 rows=1 
width=535)
 Output: dm_1.calname, dm_1.jobyear, 
dm_1.caltype, (dm_1.daymask)::character varying(400)
 Join Filter: (((dm_1.calname)::text = 
(tag.dayscal)::text) OR ((dm_1.calname)::text = (tag.weekcal)::text) OR 
((dm_1.calname)::text = (tag.confcal)::text))
 ->  Nested Loop  (cost=0.30..3838.11 rows=1 
width=3)
   Output: tag.dayscal, tag.weekcal, 
tag.confcal
   Inner Unique: true
   ->  Seq Scan on public.cms_tag tag  
(cost=0.00..30.96 rows=1396 width=7)
 Output: tag.tagname, tag.groupid, 
tag.maxwait, tag.cal_andor, tag.monthstr, tag.dayscal, tag.weekcal, 
tag.confcal, tag.shift, tag.retro, tag.daystr, tag.wdaystr, tag.tagfrom, 
tag.tagtill, tag.roworder, tag.exclude_rbc
   ->  Memoize  (cost=0.30..4.02 rows=1 
width=4)
 Output: jd_1.jobno
 Cache Key: tag.groupid
 Cache Mode: logical
 ->  Index Scan using job on 
public.cms_jobdef jd_1  (cost=0.29..4.01 rows=1 width=4)
   Output: jd_1.jobno
   Index Cond: (jd_1.jobno = 
tag.groupid)
   Filter: 
(((jd_1.schedtab)::text = 'PZL-ZETA_REDIS_UTILITY_PSE'::text) OR 
((jd_1.schedtab)::text ~~ 'PZL-ZETA_REDIS_UTILITY_PSE/%'::text))
 ->  Seq Scan on public.cms_datemm dm_1  
(cost=0.00..16.33 rows=171 width=389)
   Output: dm_1.calname, dm_1.jobyear, 
dm_1.daymask, dm_1.caltype, dm_1.caldesc
   Filter: ((dm_1.jobyear >= 
'2021'::bpchar) AND (dm_1.jobyear <= '2025'::bpchar))
   ->  Unique  (cost=4649.93..4649.98 

Re: [EXTERNAL] Re: Performance down with JDBC 42

[Jeff Janes]

On Sun, Nov 5, 2023 at 11:20 AM Abraham, Danny 
wrote:

> Thanks Laurenz,
>
> Traced two huge plans. They differ.
> The fast one does use Materialize and Memoize  (the psql).
> Is there something in JDBC 42 that blocks these algoruthms?


Directly blocking those is not likely. Maybe the way the drivers fetch
partial results is different, such that with one the planner knows to
expect only partial results to be fetched and with the other it does not.
So in one case it chooses the fast-start plan, and in the other it
doesn't.  But it will be hard to get anywhere if you just dribble
information at us a bit at a time.  Can you come up with a self-contained
test case?  Or at least show the entirety of both plans?

Cheers,

Jeff

Re: [EXTERNAL] Re: Performance down with JDBC 42

[Frits Hoogland]

Are you absolutely sure that the two databases you’re comparing the executing 
with are identical, and that the objects involved in the query are physically 
and logically identical?

The planning is done based on cost/statistics of the objects. If the statistics 
are different, the planner may come up with another plan.

Frits



> Op 5 nov 2023 om 17:20 heeft Abraham, Danny  het 
> volgende geschreven:
> 
> Thanks Laurenz,
> 
> Traced two huge plans. They differ.
> The fast one does use Materialize and Memoize  (the psql).
> Is there something in JDBC 42 that blocks these algoruthms?
> 
> Thanks again
> 
> Danny
> 
> -Original Message-
> From: Laurenz Albe 
> Sent: Saturday, November 4, 2023 11:07 PM
> To: Abraham, Danny ; psql-performance 
> 
> Subject: [EXTERNAL] Re: Performance down with JDBC 42
> 
>> On Sat, 2023-11-04 at 19:08 +, Abraham, Danny wrote:
>> Asking for help with a JDBC related issue.
>> Environment: Linux 7.9 PG 14.9 , very busy PG Server.
>> 
>> A big query - 3 unions and about 10 joins runs :
>> - 70ms on psql , DBeaver with JDBC 42  and  in our Server using old
>> JDBC 9.2
>> - 2500 ms in our Server using new JDBC 42 driver. ( and  this is
>> running many times)
>> 
>> Question: Is there a structured way to identify optimization setup ( Planner 
>> Method s ) changes?
>> Are there any known changes specific to JDBC 42.
> 
> What I would do is enable auto_explain and look at the execution plan when 
> the statement is run by the JDBC driver.  Then you can compare the execution 
> plans and spot the difference.
> 
> Yours,
> Laurenz Albe

Re: [EXTERNAL] Re: Performance down with JDBC 42

[Andreas Kretschmer]

Am 05.11.23 um 17:20 schrieb Abraham, Danny:

Thanks Laurenz,

Traced two huge plans. They differ.
The fast one does use Materialize and Memoize  (the psql).
Is there something in JDBC 42 that blocks these algoruthms?


*maybe* the driver changed some settings. You can check it with

select name, setting from pg_settings where name ~ 'enable';

using the JDBC-connection.


Regards, Andreas




Thanks again

Danny

-Original Message-
From: Laurenz Albe 
Sent: Saturday, November 4, 2023 11:07 PM
To: Abraham, Danny ; psql-performance 

Subject: [EXTERNAL] Re: Performance down with JDBC 42

On Sat, 2023-11-04 at 19:08 +, Abraham, Danny wrote:

Asking for help with a JDBC related issue.
Environment: Linux 7.9 PG 14.9 , very busy PG Server.

A big query - 3 unions and about 10 joins runs :
- 70ms on psql , DBeaver with JDBC 42  and  in our Server using old
JDBC 9.2
- 2500 ms in our Server using new JDBC 42 driver. ( and  this is
running many times)

Question: Is there a structured way to identify optimization setup ( Planner 
Method s ) changes?
Are there any known changes specific to JDBC 42.

What I would do is enable auto_explain and look at the execution plan when the 
statement is run by the JDBC driver.  Then you can compare the execution plans 
and spot the difference.

Yours,
Laurenz Albe


--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com

RE: [EXTERNAL] Re: Performance down with JDBC 42

[Abraham, Danny]

Thanks Laurenz,

Traced two huge plans. They differ.
The fast one does use Materialize and Memoize  (the psql).
Is there something in JDBC 42 that blocks these algoruthms?

Thanks again

Danny

-Original Message-
From: Laurenz Albe  
Sent: Saturday, November 4, 2023 11:07 PM
To: Abraham, Danny ; psql-performance 

Subject: [EXTERNAL] Re: Performance down with JDBC 42

On Sat, 2023-11-04 at 19:08 +, Abraham, Danny wrote:
> Asking for help with a JDBC related issue.
> Environment: Linux 7.9 PG 14.9 , very busy PG Server.
> 
> A big query - 3 unions and about 10 joins runs :
> - 70ms on psql , DBeaver with JDBC 42  and  in our Server using old 
> JDBC 9.2
> - 2500 ms in our Server using new JDBC 42 driver. ( and  this is 
> running many times)
> 
> Question: Is there a structured way to identify optimization setup ( Planner 
> Method s ) changes?
> Are there any known changes specific to JDBC 42. 

What I would do is enable auto_explain and look at the execution plan when the 
statement is run by the JDBC driver.  Then you can compare the execution plans 
and spot the difference.

Yours,
Laurenz Albe

Re: Performance down with JDBC 42

[Laurenz Albe]

On Sat, 2023-11-04 at 19:08 +, Abraham, Danny wrote:
> Asking for help with a JDBC related issue.
> Environment: Linux 7.9 PG 14.9 , very busy PG Server.
> 
> A big query - 3 unions and about 10 joins runs :
> - 70ms on psql , DBeaver with JDBC 42  and  in our Server using old JDBC 9.2
> - 2500 ms in our Server using new JDBC 42 driver. ( and  this is running many 
> times) 
> 
> Question: Is there a structured way to identify optimization setup ( Planner 
> Method s ) changes?
> Are there any known changes specific to JDBC 42. 

What I would do is enable auto_explain and look at the execution plan
when the statement is run by the JDBC driver.  Then you can compare the
execution plans and spot the difference.

Yours,
Laurenz Albe

Re: Performance implications of 8K pread()s

[Andres Freund]

Hi,

On 2023-07-17 16:42:31 +0200, Dimitrios Apostolou wrote:
> Thanks, it sounds promising! Are the changes in the 16 branch already,
> i.e. is it enough to fetch sources for 16-beta2?

No, this is in a separate branch.

https://github.com/anarazel/postgres/tree/aio


> If so do I just configure --with-liburing (I'm on linux) and run with
> io_method=io_uring?

It's probably worth trying out both io_uring and worker. I've not looked at
performance on btrfs. I know that some of the optimized paths for io_uring
(being able to perform filesystem IO without doing so synchronously in an
in-kernel thread) require filesystem cooperation, and I do not know how much
attention btrfs has received for that.


> Else, if I use the io_method=worker what is a sensible amount of worker
> threads?

Depends on your workload :/. If you just want to measure whether it fixes your
single-threaded query execution issue, the default should be just fine.


> Should I also set all the flags for direct I/O?  (io_data_direct=on
> io_wal_direct=on).

FWIW, I just pushed a rebased version to the aio branch, and there the config
for direct io is
io_direct = 'data, wal, wal_init'
(or a subset thereof).

>From what I know of btrfs, I don't think you want direct IO though. Possibly
for WAL, but definitely not for data. IIRC it currently can cause corruption.

Greetings,

Andres Freund

Re: Performance implications of 8K pread()s

[Dimitrios Apostolou]

Thanks, it sounds promising! Are the changes in the 16 branch already,
i.e. is it enough to fetch sources for 16-beta2? If
so do I just configure --with-liburing (I'm on linux) and run with
io_method=io_uring? Else, if I use the io_method=worker what is a sensible
amount of worker threads? Should I also set all the flags for direct I/O?
(io_data_direct=on io_wal_direct=on).

Re: Performance implications of 8K pread()s

[Thomas Munro]

On Thu, Jul 13, 2023 at 6:50 AM Dimitrios Apostolou  wrote:
> Interesting and kind of sad that the last update on the wiki page is from
> 2021. What is the latest prototype? I'm not sure I'm up to the task of
> putting my database to the test. ;-)

It works pretty well,  certainly well enough to try out, and work is
happening.  I'll try to update the wiki with some more up-to-date
information soon.  Basically, compare these two slides (you could also
look at slide 11, which is the most most people are probably
interested in, but then you can't really see what's going on with
system call-level tools):

https://speakerdeck.com/macdice/aio-and-dio-for-postgresql-on-freebsd?slide=7
https://speakerdeck.com/macdice/aio-and-dio-for-postgresql-on-freebsd?slide=9

Not only are the IOs converted into 128KB preadv() calls, they are
issued concurrently and ahead of time while your backend is chewing on
the last lot of pages.  So even if your file system completely fails
at prefetching, we'd have a fighting chance at getting closer to
device/line speed.  That's basically what you have to do to support
direct I/O, where there is no system-provided prefetching.

Re: Performance implications of 8K pread()s

[Dimitrios Apostolou]

Hello and thanks for the feedback!

On Wed, 12 Jul 2023, Thomas Munro wrote:


On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou  wrote:

Note that I suspect my setup being related, (btrfs compression behaving
suboptimally) since the raw device can give me up to 1GB/s rate. It is however
evident that reading in bigger chunks would mitigate such setup inefficiencies.
On a system that reads are already optimal and the read rate remains the same,
then bigger block size would probably reduce the sys time postgresql consumes
because of the fewer system calls.


I don't know about btrfs but maybe it can be tuned to prefetch
sequential reads better...


I tried a lot to tweak the kernel's block layer read-ahead and to change
different I/O schedulers, but it made no difference. I'm now convinced
that the problem manifests specially on compressed btrfs: the filesystem
doesn't do any read-ahed (pre-fetch) so no I/O requests "merge" on the
block layer.

Iostat gives an interesting insight in the above measurements.  For both
postgres doing sequential scan and for dd with bs=8k, the kernel block
layer does not appear to merge the I/O requests. `iostat -x` shows 16
sectors average read request size, 0 merged requests, and very high
reads/s IOPS number.

The dd commands with bs=32k block size show fewer IOPS on `iostat -x` but
higher speed(!), larger average block size and high number of merged
requests.

Example output for some random second out of dd bs=8k:

Devicer/s rMB/s   rrqm/s  %rrqm r_await rareq-sz
sdc   1313.00 20.93 2.00   0.150.5316.32

with dd bs=32k:

Devicer/s rMB/s   rrqm/s  %rrqm r_await rareq-sz
sdc290.00 76.44  4528.00  93.981.71   269.92

On the same filesystem, doing dd bs=8k reads from a file that has not been
compressed by the filesystem I get 1GB/s device read throughput!

I sent this feedback to the btrfs list, but got no feedback yet:

https://www.spinics.net/lists/linux-btrfs/msg137200.html




So would it make sense for postgres to perform reads in bigger blocks? Is it
easy-ish to implement (where would one look for that)? Or must the I/O unit be
tied to postgres' page size?


It is hard to implement.  But people are working on it.  One of the
problems is that the 8KB blocks that we want to read data into aren't
necessarily contiguous so you can't just do bigger pread() calls
without solving a lot more problems first.


This kind of overhaul is good, but goes much deeper. Same with async I/O
of course. But what I have in mind should be much simpler (add grains
of salt since I don't know postgres internals :-)

+ A process wants to read a block from a file
+ Postgres' buffer cache layer (shared_buffers?) looks it up in the cache,
  if not found it passes the request down to
+ postgres' block layer; it submits an I/O request for 32KB that include
  the 8K block requested; it returns the 32K block to
+ postgres' buffer cache layer; it stores all 4 blocks read from the disk
  into the buffer cache, and returns only the 1 block requested.

The danger here is that in random non-contiguous 8K reads, the buffer
cache gets satsurated by 4x the amount of data because of 32K reads, and
75% of that data is useless, but may still evict useful data. The answer
is that is should be marked as unused then (by putting it in front of the
cache's LRU for example) so that those unused read-ahead pages are re-used
for upcoming read-ahead, without evicting too much useful pages.


The project at
https://wiki.postgresql.org/wiki/AIO aims to deal with the
"clustering" you seek plus the "gathering" required for non-contiguous
buffers by allowing multiple block-sized reads to be prepared and
collected on a pending list up to some size that triggers merging and
submission to the operating system at a sensible rate, so we can build
something like a single large preadv() call.  In the current
prototype, if io_method=worker then that becomes a literal preadv()
call running in a background "io worker" process, but it could also be
OS-specific stuff (io_uring, ...) that starts an asynchronous IO
depending on settings.  If you take that branch and run your test you
should see 128KB-sized preadv() calls.



Interesting and kind of sad that the last update on the wiki page is from
2021. What is the latest prototype? I'm not sure I'm up to the task of
putting my database to the test. ;-)


Thanks and regards,
Dimitris

Re: Performance implications of 8K pread()s

[Thomas Munro]

On Wed, Jul 12, 2023 at 5:12 AM Thomas Munro  wrote:
> "gathering"

(Oops, for reads, that's "scattering".  As in scatter/gather I/O but I
picked the wrong one...).

Re: Performance implications of 8K pread()s

[Thomas Munro]

On Wed, Jul 12, 2023 at 1:11 AM Dimitrios Apostolou  wrote:
> Note that I suspect my setup being related, (btrfs compression behaving
> suboptimally) since the raw device can give me up to 1GB/s rate. It is however
> evident that reading in bigger chunks would mitigate such setup 
> inefficiencies.
> On a system that reads are already optimal and the read rate remains the same,
> then bigger block size would probably reduce the sys time postgresql consumes
> because of the fewer system calls.

I don't know about btrfs but maybe it can be tuned to prefetch
sequential reads better...

> So would it make sense for postgres to perform reads in bigger blocks? Is it
> easy-ish to implement (where would one look for that)? Or must the I/O unit be
> tied to postgres' page size?

It is hard to implement.  But people are working on it.  One of the
problems is that the 8KB blocks that we want to read data into aren't
necessarily contiguous so you can't just do bigger pread() calls
without solving a lot more problems first.  The project at
https://wiki.postgresql.org/wiki/AIO aims to deal with the
"clustering" you seek plus the "gathering" required for non-contiguous
buffers by allowing multiple block-sized reads to be prepared and
collected on a pending list up to some size that triggers merging and
submission to the operating system at a sensible rate, so we can build
something like a single large preadv() call.  In the current
prototype, if io_method=worker then that becomes a literal preadv()
call running in a background "io worker" process, but it could also be
OS-specific stuff (io_uring, ...) that starts an asynchronous IO
depending on settings.  If you take that branch and run your test you
should see 128KB-sized preadv() calls.

Re: Performance issues in query with multiple joins

[Laurenz Albe]

On Fri, 2023-04-28 at 15:19 +0300, Παρασκευη Πασσαρη wrote:
> We are facing a performance issue with the following query. Executing this 
> query takes about 20 seconds.
> (the database version is 14.1)

The execution plan seems to be incomplete.

Yours,
Laurenz Albe

Re: Performance issues in query with multiple joins

[Tom Lane]

=?UTF-8?B?zqDOsc+BzrHPg866zrXPhc63IM6gzrHPg8+DzrHPgc63?= 
 writes:
> We are facing a performance issue with the following query. Executing this
> query takes about 20 seconds.

Raising join_collapse_limit (to more than the number of joins in
the query) might help.  But I think really if performance is a
problem you should think about ditching the star schema design.

regards, tom lane

Re: Performance of UPDATE operation

[Jeff Janes]

On Mon, Feb 13, 2023 at 10:09 AM Mkrtchyan, Tigran 
wrote:

>
>  0.524   0  BEGIN;
>  0.819   0  INSERT INTO t_inodes (inumber, icrtime,
> igeneration)
>  0.962   0  UPDATE t_inodes SET igeneration = igeneration
> + 1 where  inumber = :inumber;
>  9.203   0  END;
> ```
>
> My naive expectation will be that updating the newly inserted record
> should cost nothing


It takes less than 1/10 of the total time.  That is pretty close to
nothing.  Why would you expect it to be truly free?


> ... Are there ways
> to make it less expensive?
>

Obviously here you could just insert the correct value in the first place
and not do the update at all.

Cheers,

Jeff

Re: Performance of UPDATE operation

[Oluwatobi Ogunsola]

Maybe reconsider your expectation.
Note: Every “update” have to “select” before modifying data.
Even if the page is in memory, there still work…reading ,acquiring lock, 
modifying and request to write to disk.


Regards,
Tobi

> On 13 Feb 2023, at 18:48, Laurenz Albe  wrote:
> 
> On Mon, 2023-02-13 at 16:09 +0100, Mkrtchyan, Tigran wrote:
>> Typically we expect that UPDATE is a slow operation in PostgreSQL, however,
>> there are cases where it's hard to understand why. In particular, I have a 
>> table like
>> 
>> ```
>> CREATE SEQUENCE t_inodes_inumber_seq
>> START WITH 1
>> INCREMENT BY 1
>> NO MINVALUE
>> NO MAXVALUE
>> CACHE 1;
>> 
>> 
>> CREATE TABLE t_inodes (
>> inumber bigint PRIMARY KEY,
>> icrtime timestamp with time zone NOT NULL,
>> igeneration bigint NOT NULL
>> );
>> ```
>> 
>> and a transaction that inserts and update an entry in that table:
>> 
>> ```
>> BEGIN;
>> INSERT INTO t_inodes (inumber, icrtime, igeneration)
>>VALUES (nextval('t_inodes_inumber_seq'), now(), 0) RETURNING inumber \gset
>> 
>> UPDATE t_inodes SET igeneration = igeneration + 1 where  inumber = :inumber;
>> END;
>> ```
>> 
>> The pgbench shows the following result:
>> 
>> ```
>> $ pgbench -h localhost -n -r  -f update.sql -t 1 -c 64 -j 64 testdb
>> pgbench (15.0 (Debian 15.0-1.pgdg110+1))
>> transaction type: update.sql
>> scaling factor: 1
>> query mode: simple
>> number of clients: 64
>> number of threads: 64
>> maximum number of tries: 1
>> number of transactions per client: 1
>> number of transactions actually processed: 64/64
>> number of failed transactions: 0 (0.000%)
>> latency average = 11.559 ms
>> initial connection time = 86.038 ms
>> tps = 5536.736898 (without initial connection time)
>> statement latencies in milliseconds and failures:
>>  0.524   0  BEGIN;
>>  0.819   0  INSERT INTO t_inodes (inumber, icrtime, 
>> igeneration)
>>  0.962   0  UPDATE t_inodes SET igeneration = igeneration + 
>> 1 where  inumber = :inumber;
>>  9.203   0  END;
>> ```
>> 
>> My naive expectation will be that updating the newly inserted record should 
>> cost nothing... Are there ways
>> to make it less expensive?
> 
> Updating a newly inserted row is about as expensive as inserting the row in 
> the first place.
> 
> You can reduce the overall impact somewhat by creating the table with a 
> "fillfactor" below
> 100, in your case 90 would probably be enough.  That won't speed up the 
> UPDATE itself, but
> it should greatly reduce the need for VACUUM.
> 
> Yours,
> Laurenz Albe
> 
> 

Re: Performance of UPDATE operation

[Laurenz Albe]

On Mon, 2023-02-13 at 16:09 +0100, Mkrtchyan, Tigran wrote:
> Typically we expect that UPDATE is a slow operation in PostgreSQL, however,
> there are cases where it's hard to understand why. In particular, I have a 
> table like
> 
> ```
> CREATE SEQUENCE t_inodes_inumber_seq
>     START WITH 1
>     INCREMENT BY 1
>     NO MINVALUE
>     NO MAXVALUE
>     CACHE 1;
> 
> 
> CREATE TABLE t_inodes (
>     inumber bigint PRIMARY KEY,
>     icrtime timestamp with time zone NOT NULL,
>     igeneration bigint NOT NULL
> );
> ```
> 
> and a transaction that inserts and update an entry in that table:
> 
> ```
> BEGIN;
> INSERT INTO t_inodes (inumber, icrtime, igeneration)
>    VALUES (nextval('t_inodes_inumber_seq'), now(), 0) RETURNING inumber \gset
> 
> UPDATE t_inodes SET igeneration = igeneration + 1 where  inumber = :inumber;
> END;
> ```
> 
> The pgbench shows the following result:
> 
> ```
> $ pgbench -h localhost -n -r  -f update.sql -t 1 -c 64 -j 64 testdb
> pgbench (15.0 (Debian 15.0-1.pgdg110+1))
> transaction type: update.sql
> scaling factor: 1
> query mode: simple
> number of clients: 64
> number of threads: 64
> maximum number of tries: 1
> number of transactions per client: 1
> number of transactions actually processed: 64/64
> number of failed transactions: 0 (0.000%)
> latency average = 11.559 ms
> initial connection time = 86.038 ms
> tps = 5536.736898 (without initial connection time)
> statement latencies in milliseconds and failures:
>  0.524   0  BEGIN;
>  0.819   0  INSERT INTO t_inodes (inumber, icrtime, 
> igeneration)
>  0.962   0  UPDATE t_inodes SET igeneration = igeneration + 1 
> where  inumber = :inumber;
>  9.203   0  END;
> ```
> 
> My naive expectation will be that updating the newly inserted record should 
> cost nothing... Are there ways
> to make it less expensive?

Updating a newly inserted row is about as expensive as inserting the row in the 
first place.

You can reduce the overall impact somewhat by creating the table with a 
"fillfactor" below
100, in your case 90 would probably be enough.  That won't speed up the UPDATE 
itself, but
it should greatly reduce the need for VACUUM.

Yours,
Laurenz Albe

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

[Emil Iggland]

> I don't think that index can be used for your original query. It could
> only be used if "channel" is unique in "valueseries" and you'd written
> the query as:

Thanks! That explanation I can understand, now I know how to avoid this 
in future.


> I guess "channel" must not be the primary key to "valueseries" and
> that's why you use an IN().
Correct. We create a new valueseries in some circumstances, so multiple 
valueseries can point to the same channel.





On 2022-04-27 10:22, David Rowley wrote:

On Wed, 27 Apr 2022 at 19:54, Emil Iggland  wrote:


  > You've got the wrong column order (for this query anyway) in that
  > index.  It'd work a lot better if dataview were the first column;



I might be misunderstanding you, but I assume that you are suggesting an
index on (dataview, valuetimestamp).
We have that index, it is the primary key. For some reason it isn't
being selected.


I don't think that index can be used for your original query. It could
only be used if "channel" is unique in "valueseries" and you'd written
the query as:

select * from datavalue
where dataview = (select id from valueseries where channel = 752433)
ORDER BY VALUETIMESTAMP DESC
FETCH FIRST ROW only;

that would allow a backwards index scan using the (dataview,
valuetimestamp) index.  Because you're using the IN clause to possibly
look for multiple "dataview" values matching the given "channel", the
index range scan does not have a single point to start at.  What
you've done with the LATERAL query allows the index to be scanned once
for each "valueseries" row with a "channel" value matching your WHERE
clause.

I guess "channel" must not be the primary key to "valueseries" and
that's why you use an IN().

The above query would return an error if multiple rows were returned
by the subquery.

David

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

[David Rowley]

On Wed, 27 Apr 2022 at 19:54, Emil Iggland  wrote:
>
>  > You've got the wrong column order (for this query anyway) in that
>  > index.  It'd work a lot better if dataview were the first column;

> I might be misunderstanding you, but I assume that you are suggesting an
> index on (dataview, valuetimestamp).
> We have that index, it is the primary key. For some reason it isn't
> being selected.

I don't think that index can be used for your original query. It could
only be used if "channel" is unique in "valueseries" and you'd written
the query as:

select * from datavalue
where dataview = (select id from valueseries where channel = 752433)
ORDER BY VALUETIMESTAMP DESC
FETCH FIRST ROW only;

that would allow a backwards index scan using the (dataview,
valuetimestamp) index.  Because you're using the IN clause to possibly
look for multiple "dataview" values matching the given "channel", the
index range scan does not have a single point to start at.  What
you've done with the LATERAL query allows the index to be scanned once
for each "valueseries" row with a "channel" value matching your WHERE
clause.

I guess "channel" must not be the primary key to "valueseries" and
that's why you use an IN().

The above query would return an error if multiple rows were returned
by the subquery.

David

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

[Emil Iggland]

> You've got the wrong column order (for this query anyway) in that
> index.  It'd work a lot better if dataview were the first column;
I might be misunderstanding you, but I assume that you are suggesting an 
index on (dataview, valuetimestamp).
We have that index, it is the primary key. For some reason it isn't 
being selected.


I can understand that it has to go through the whole index, potentially 
even the whole table, but I do not why it takes so long.


Even a query that should take equally long (probably longer) is 
substantially faster:


explain (analyze, buffers)
select valuetimestamp from datavalue
where valuetimestamp <> '1965-01-07 05:50:59';

Completes in less than 500ms using a sequential scan,

...
->  Seq Scan on datavalue_2022_04 datavalue_7  (cost=0.00..1450.39 
rows=56339 width=8) (actual time=0.013..5.988 rows=56109 loops=1)"
	Filter: (valuetimestamp <> '1965-01-07 05:50:59'::timestamp without 
time zone)

Buffers: shared hit=742 read=4
...
Planning Time: 0.781 ms
Execution Time: 394.408 ms


while the original query takes over 1 second.
...
->  Index Scan Backward using 
datavalue_2022_04_valuetimestamp_dataview_idx on datavalue_2022_04 
datavalue_7  (cost=0.29..4292.48 rows=56351 width=227) (actual 
time=0.166..17.340 rows=56109 loops=1)

Buffers: shared hit=42013 read=278
...
Planning Time: 0.964 ms
Execution Time: 1291.509 ms

I do not understand how looking at every value in the index and 
returning none be slower than looking at every table in the table and 
returning none. If it takes 500ms to return every value in the table via 
a sequential scan, then it should take less via an index scan.



In case we never solve it, and someone else runs into similiar problems, 
we (hopefully temporarily) worked around it by reformulating the query 
to use a lateral join:


EXPLAIN (analyze, buffers)
SELECT dv.* FROM valueseries vs
LEFT JOIN LATERAL (
SELECT * FROM datavalue dv WHERE dv.dataview = vs.id
ORDER BY VALUETIMESTAMP
FETCH FIRST 1 ROWS ONLY
) dv ON TRUE
where vs.channel = 752433

This causes it to use the correct index:
->  Index Scan using datavalue_2022_01_pkey on datavalue_2022_01 dv_4 
(cost=0.42..2951.17 rows=1032 width=228) (actual time=0.034..0.034 
rows=0 loops=1)

Index Cond: (dataview = vs.id)
Buffers: shared read=3
...
Planning Time: 1.169 ms
Execution Time: 0.524 ms


Regards
Emil


On 2022-04-25 18:00, Tom Lane wrote:

Emil Iggland  writes:

The query that is giving us issues is the following, channel 752433 has
NO values, 752431 has values.
(Channel 752433 only has valueseries 752434)



select * from datavalue
where dataview in ( select id from valueseries where channel =
%channel_idx%)
ORDER BY VALUETIMESTAMP DESC
FETCH FIRST ROW only;



Running explain analyze shows strange numbers, 52'000 rows are being
returned but there are no rows there.



For channel 752433
->  Index Scan Backward using
datavalue_2022_03_valuetimestamp_dataview_idx on datavalue_2022_03
datavalue_6  (cost=0.42..7166.19 rows=119673 width=226) (actual
time=0.008..32.831 rows=119601 loops=1)


You've got the wrong column order (for this query anyway) in that
index.  It'd work a lot better if dataview were the first column;
or at least, it wouldn't tempt the planner to try this unstably-
performing plan.  It's trying to use the index ordering to satisfy
the ORDER BY, which works great as long as it finds a dataview
match in some reasonably recent index entry.  Otherwise, it's
going to crawl the whole index to discover that there's no match.

regards, tom lane

Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected.

[Tom Lane]

Emil Iggland  writes:
> The query that is giving us issues is the following, channel 752433 has 
> NO values, 752431 has values.
> (Channel 752433 only has valueseries 752434)

> select * from datavalue
> where dataview in ( select id from valueseries where channel = 
> %channel_idx%)
> ORDER BY VALUETIMESTAMP DESC
> FETCH FIRST ROW only;

> Running explain analyze shows strange numbers, 52'000 rows are being 
> returned but there are no rows there.

> For channel 752433
> ->  Index Scan Backward using 
> datavalue_2022_03_valuetimestamp_dataview_idx on datavalue_2022_03 
> datavalue_6  (cost=0.42..7166.19 rows=119673 width=226) (actual 
> time=0.008..32.831 rows=119601 loops=1)

You've got the wrong column order (for this query anyway) in that
index.  It'd work a lot better if dataview were the first column;
or at least, it wouldn't tempt the planner to try this unstably-
performing plan.  It's trying to use the index ordering to satisfy
the ORDER BY, which works great as long as it finds a dataview
match in some reasonably recent index entry.  Otherwise, it's
going to crawl the whole index to discover that there's no match.

regards, tom lane

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

[overland]

Azure VM's are incredibly slow. I couldn't host a OpenStreetMap
database because the disk IO would die off from reasonable performance
to about 5KB/s and the data import wouldn't finish. Reboot and it would
be fine for a while then repeat. $400 a month for that. 

You are better off on bare metal outside of Azure, otherwise it is
going to be cloudy misery. I'm saving hundreds renting a bare metal
machine in a data center and I get the expected performance on top of
the cost savings. 


-Original Message-
From: "Kumar, Mukesh" 
To: pgsql-performa...@postgresql.org
, MUKESH KUMAR

Subject: Performance for SQL queries on Azure PostgreSQL PaaS instance
Date: Tue, 12 Apr 2022 09:10:23 +

Hi Team,
 
Greetings !!
 
We have recently done the migration from Oracle Database Version 12C to
Azure PostgreSQL PaaS instance version 11.4 and most of the application
functionality testing has been over and tested successfully 
 
However, there is 1 process at application level which is taking
approx. 10 mins in PostgreSQL and in oracle it is taking only 3 mins.
 
After investigating further we identified that process which is
executed from application end contains 500 to 600 no of short SQL
queries into the database. We tried to run the few queries individually
on database and they are taking less than sec in Postgres Database to
execute, and we noticed that in Oracle taking half of the time as is
taking in PostgreSQL. for ex . in oracle same select statement is
taking 300 millisecond and in PostgreSQL it is taking approx. 600
millisecond which over increases the execution of the process.
 
Oracle Database are hosted on ON- Prem DC with dedicated application
server on OnPrem and same for PostgreSQL.
We are using below specifications for PostgreSQL
PostgreSQL Azure PaaS instance -Single Server (8cvore with 1 TB storage
on general purpose tier ) = 8 Core and 40 Gb of Memory
PostgreSQL version - 11.4
 
We have tried running maintenance Jobs like vaccum, analyze, creating
indexes, increasing compute but no sucess
 
 
I am happy to share my server parameter for PostgreSQL for more
information.
 
Please let us know if this is expected behavior in PostgreSQL or is
there any way i can decrease the time for the SQL queries and make it a
comparison with Oracle
 
Regards,
Mukesh Kumar
 
 

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

[andrew cooke]

On Wed, Apr 13, 2022 at 10:34:24AM +0200, Laurenz Albe wrote:
> On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote:
> > We have recently done the migration from Oracle Database Version 12C to 
> > Azure
> > PostgreSQL PaaS instance version 11.4 and most of the application 
> > functionality
> > testing has been over and tested successfully 
> >  
> > However, there is 1 process at application level which is taking approx. 10 
> > mins
> > in PostgreSQL and in oracle it is taking only 3 mins.
> >  
> > After investigating further we identified that process which is executed 
> > from
> > application end contains 500 to 600 no of short SQL queries into the 
> > database.
> > We tried to run the few queries individually on database and they are taking
> > less than sec in Postgres Database to execute, and we noticed that in Oracle
> > taking half of the time as is taking in PostgreSQL. for ex . in oracle same
> > select statement is taking 300 millisecond and in PostgreSQL it is taking
> > approx. 600 millisecond which over increases the execution of the process.
> >  
> > Oracle Database are hosted on ON- Prem DC with dedicated application server 
> > on
> > OnPrem and same for PostgreSQL.
> 
> How can a database hosted with Microsoft be on your permises?

Not OP, but it couldn't it be
https://azure.microsoft.com/en-us/overview/azure-stack/ ?

> Apart from all other things, compare the network latency.  If a single request
> results in 500 database queries, you will be paying 1000 times the network
> latency per request.
> 
> Yours,
> Laurenz Albe
> -- 
> Cybertec | https://www.cybertec-postgresql.com
> 
> 
> 

RE: Performance for SQL queries on Azure PostgreSQL PaaS instance

[Kumar, Mukesh]

Hi Albe , 

I mean to say that , we have everything hosted on Oracle is on On - Prem DC and 
everything hosted on Azure PostgreSQL on Microsoft Azure Cloud like Application 
Server and PaaS Instance,

Please revert in case of any query

Thanks and Regards, 
Mukesh Kumar

-Original Message-
From: Laurenz Albe  
Sent: Wednesday, April 13, 2022 2:04 PM
To: Kumar, Mukesh ; pgsql-performa...@postgresql.org; 
MUKESH KUMAR 
Subject: Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote:
> We have recently done the migration from Oracle Database Version 12C 
> to Azure PostgreSQL PaaS instance version 11.4 and most of the 
> application functionality testing has been over and tested 
> successfully
>  
> However, there is 1 process at application level which is taking 
> approx. 10 mins in PostgreSQL and in oracle it is taking only 3 mins.
>  
> After investigating further we identified that process which is 
> executed from application end contains 500 to 600 no of short SQL queries 
> into the database.
> We tried to run the few queries individually on database and they are 
> taking less than sec in Postgres Database to execute, and we noticed 
> that in Oracle taking half of the time as is taking in PostgreSQL. for 
> ex . in oracle same select statement is taking 300 millisecond and in 
> PostgreSQL it is taking approx. 600 millisecond which over increases the 
> execution of the process.
>  
> Oracle Database are hosted on ON- Prem DC with dedicated application 
> server on OnPrem and same for PostgreSQL.

How can a database hosted with Microsoft be on your permises?

Apart from all other things, compare the network latency.  If a single request 
results in 500 database queries, you will be paying 1000 times the network 
latency per request.

Yours,
Laurenz Albe
--
Cybertec | 
https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!KupS4sW4BlfImQPd!Na6zYPRuqYDPkzxkeKGFLkUk5TtVvDNeBotFXA-DpoSA8sO0hMkFnUll1op05OICvy74bGAGSzuTfzBWN-4PfzlYkK0vvQ$
 

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

[Laurenz Albe]

On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote:
> We have recently done the migration from Oracle Database Version 12C to Azure
> PostgreSQL PaaS instance version 11.4 and most of the application 
> functionality
> testing has been over and tested successfully 
>  
> However, there is 1 process at application level which is taking approx. 10 
> mins
> in PostgreSQL and in oracle it is taking only 3 mins.
>  
> After investigating further we identified that process which is executed from
> application end contains 500 to 600 no of short SQL queries into the database.
> We tried to run the few queries individually on database and they are taking
> less than sec in Postgres Database to execute, and we noticed that in Oracle
> taking half of the time as is taking in PostgreSQL. for ex . in oracle same
> select statement is taking 300 millisecond and in PostgreSQL it is taking
> approx. 600 millisecond which over increases the execution of the process.
>  
> Oracle Database are hosted on ON- Prem DC with dedicated application server on
> OnPrem and same for PostgreSQL.

How can a database hosted with Microsoft be on your permises?

Apart from all other things, compare the network latency.  If a single request
results in 500 database queries, you will be paying 1000 times the network
latency per request.

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

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

[Tomas Vondra]

On 4/12/22 16:23, Frits Jalvingh wrote:
> You might be comparing apples and pears..
> 
> Your Oracle is running on prem while Postgres is running on Azure. Azure
> does not really have disks; it seems to have just a bunch of old people
> writing the data on paper - I/O on Azure is ridiculously slow. What
> disks/hardware does the on-prem Oracle have?
> 

Right. It'd be good to do some basic system benchmarks first, e.g. using
"fio" or similar tools, before comparing query timings. It's quite
possible this is due to Azure storage being slower than physical drives
in the on-premise system.

If that does not explain this, I suggest picking a single query and
focus on it, instead of investigating all queries at once. There's a
nice wiki page explaining what info to provide:

https://wiki.postgresql.org/wiki/Slow_Query_Questions


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Performance for SQL queries on Azure PostgreSQL PaaS instance

[Frits Jalvingh]

You might be comparing apples and pears..

Your Oracle is running on prem while Postgres is running on Azure. Azure
does not really have disks; it seems to have just a bunch of old people
writing the data on paper - I/O on Azure is ridiculously slow. What
disks/hardware does the on-prem Oracle have?

On Tue, Apr 12, 2022 at 4:16 PM Kumar, Mukesh 
wrote:

> Hi Team,
>
>
>
> Greetings !!
>
>
>
> We have recently done the migration from Oracle Database Version 12C to
> Azure PostgreSQL PaaS instance version 11.4 and most of the application
> functionality testing has been over and tested successfully
>
>
>
> However, there is 1 process at application level which is taking approx.
> 10 mins in PostgreSQL and in oracle it is taking only 3 mins.
>
>
>
> After investigating further we identified that process which is executed
> from application end contains 500 to 600 no of short SQL queries into the
> database. We tried to run the few queries individually on database and they
> are taking less than sec in Postgres Database to execute, and we noticed
> that in Oracle taking half of the time as is taking in PostgreSQL. for ex .
> in oracle same select statement is taking 300 millisecond and in PostgreSQL
> it is taking approx. 600 millisecond which over increases the execution of
> the process.
>
>
>
> Oracle Database are hosted on ON- Prem DC with dedicated application
> server on OnPrem and same for PostgreSQL.
>
> We are using below specifications for PostgreSQL
>
> PostgreSQL Azure PaaS instance -Single Server (8cvore with 1 TB storage on
> general purpose tier ) = 8 Core and 40 Gb of Memory
>
> PostgreSQL version - 11.4
>
>
>
> We have tried running maintenance Jobs like vaccum, analyze, creating
> indexes, increasing compute but no sucess
>
>
>
>
>
> I am happy to share my server parameter for PostgreSQL for more
> information.
>
>
>
> Please let us know if this is expected behavior in PostgreSQL or is there
> any way i can decrease the time for the SQL queries and make it a
> comparison with Oracle
>
>
>
> Regards,
>
> Mukesh Kumar
>
>
>
>
>

Re: Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

[Tomas Vondra]

On 3/22/22 13:57, Prajna Shetty wrote:
> ++ _pgsql-performance@postgresql.org_
> 
>  
> Hello Team,
>  
> There is change in query plan in 12.4 version and Version 13 resulting
> in performance slowness post upgrade.
>  
> 
>   * In 12.4 version, Sort Operation Group Aggregate is selected which
> results to Merge Join. Query takes ~5 seconds.
>   * In 13.5 version, optimizer wrongly estimates and due to new Disk
> Based Hash Aggregate feature, it prefers Hash Aggregate instead of
> Sort Operation which finally blocks merge-join and chooses Nested
> Loop Left Join. Query takes ~5 minutes.
> 
>  
> *_NOTE: _*Disabling Hash Aggregate on instance level forces optimizer to
> choose merge operation but such instance level modification is not
> possible in terms of Application Functionality.
>  
> This performance issue is on all over most of queries. Attached one of
> the query and its plan in both version for reference in case that helps
> for recreating the issue.
>  

It's impossible to comment those other queries, but chances are the root
cause is the same.

> Version 13 query plan has lower estimated cost than that of 12.4 which
> implies 13.5 planner thought it found a better plan, but it is running
> slower and actual cost show more.
>  
> 12.4 Version:
> "Merge Right Join  (cost=*202198.78..295729.10* rows=1 width=8) (actual
> time=1399.727..*5224.574* rows=296 loops=1)"
>  
> 13.5 version:-
> "Nested Loop Left Join  (cost=*196360.90..287890.45* rows=1 width=8)
> (actual time=3209.577..*371300.693* rows=296 loops=1)"
>  

This is not a costing issue, the problem is that we expect 1 row and
calculate the cost for that, but then get 296. And unfortunately a
nested loop degrades much faster than a merge join.

I'm not sure why exactly 12.4 picked a merge join, chances are the
costing formular changed a bit somewhere. But as I said, the problem is
in bogus row cardinality estimates - 12.4 is simply lucky.

The problem most likely stems from this part:

  ->  GroupAggregate  (cost=0.43..85743.24 rows=1830 width=72) (actual
time=1.621..3452.034 rows=282179 loops=3)
  Group Key: student_class_detail.aamc_id
  Filter: (max((student_class_detail.class_level_cd)::text) = '4'::text)
  Rows Removed by Filter: 76060
  ->  Index Scan using uk_student_class_detail_aamcid_classlevelcd on
student_class_detail  (cost=0.43..74747.61 rows=1284079 width=6) (actual
time=1.570..2723.014 rows=1272390 loops=3)
Filter: (class_level_start_dt IS NOT NULL)
Rows Removed by Filter: 160402

The filter is bound to be misestimated, and the error then snowballs.
Try replacing this part with a temporary table (with pre-aggregated
results) - you can run analyze on it, etc. I'd bet that'll make the
issue go away.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

[Justin Pryzby]

On Tue, Mar 22, 2022 at 12:57:10PM +, Prajna Shetty wrote:
> 1.  We have performed Vacuum/Analyze/Reindex post Upgrade.
> 2.  Tweaked work_mem so it does not spill to Disk. We can Disk Usage But 
> it is still using Hash Aggregate and came down from 5 minutes to 20 seconds. 
> (Expected ~5 seconds). Attached plan after modifying work_mem
> 3.  Disabled Seqcan/ nestedloop
> 4.  Tweaked random_page_cost/seq_page_cost
> 5.  Set default_statistics_target=1000 and then run 
> vacuum(analyze,verbose) on selected tables.
> 6.  We have also tested performance by increasing resources up to 4 vCPU 
> and 32 GB RAM.

Would you provide your current settings ?
https://wiki.postgresql.org/wiki/Server_Configuration

Re: performance of analytical query

[Justin Pryzby]

On Fri, Nov 12, 2021 at 09:12:38PM +0100, Jiří Fejfar wrote:
> * I know that PG is focused on OLTP rather then analytics, but we are happy
> with it at all and do not wish to use another engine for analytical
> queries... isn't somewhere some "PG analytical best practice" available?

It's a good question.  Here's some ideas:

I don't think we know what version you're using - that's important, and there's
other ideas here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions

You said that your query was slow "probably after VACUUM ANALYZE".
Is it really faster without stats ?  You can do this to see if there was really
a better plan "before":
| begin; DELETE FROM pg_statistic WHERE starelid='thetable'::regclass; explain 
analyze ...; rollback;

Try enable_nestloop=off for analytic queries;

Test whether jit=off helps you or hurts you (you said that it's already 
disabled);

You can do other things that can improve estimates, by sacrificing planning time
(which for an analytic query is a small component of the total query time, and
pays off at runtime if you can get a btter plan):
 - FKs can help with estimates since pg9.6;
 - CREATE STATISTICS;
 - ALTER SET STATISTICS or increase default_statistics_target;
 - increase from_collapse_limit and join_collapse_limit.  But I don't think it
   will help your current query plan.
 - partitioning data increases planning time, and (if done well) can allow
   improved execution plans;

You can REINDEX or maybe CLUSTER during "off hours" to optimize indexes/tables.

BRIN indexes (WITH autoanalyze) are very successful for us, here.

You can monitor your slow queries using auto_explain and/or pg_stat_statements.

You can reduce autovacuum_analyze_threshold to analyze more often.

I'd be interested to hear if others have more suggestions.

-- 
Justin

Re: performance of analytical query

[Jiří Fejfar]

On Fri, 12 Nov 2021 at 03:41, Justin Pryzby  wrote:

> On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote:
> > Hi folks,
> >
> > we have found that (probably after VACUUM ANALYZE) one analytical query
> > starts to be slow on our production DB. Moreover, more or less the same
> > plan is used on our testing data (how to restore our testing data is
> > described at the end of this email), or better to say the same problem
> > exists in both (production vs testing data) scenarios: nested loop
> scanning
> > CTE several thousand times is used due to the bad estimates:
> > https://explain.dalibo.com/plan/sER#plan/node/87 (query is included on
> > dalibo).
>
> > Do you have any idea how to get HASH JOINS in the CTE w_1p_data instead
> of
> > NESTED LOOPs?
> > * Add some statistics to not get bad estimates on "lower-level" CTEs?
>
> Do you know why the estimates are bad ?
>
> I have no clear insight at the moment... problem is probably with bad
estimates which chain along the whole tree of nodes... one bad estimate was
after aggregation for example... probably, I would need to explore
carefully whole execution plan and identify sources of unprecise estimates
and correct it with additional, more precise statistics when possible,
right?


> Index Scan using t_map_plot_cell__cell_gid__idx on cm_plot2cell_mapping
> cm_plot2cell_mapping (cost=0.29..18.59 rows=381 width=12) (actual
> time=0.015..2.373 rows=3,898 loops=1)
> Index Cond: (cm_plot2cell_mapping.estimation_cell =
> f_a_cell.estimation_cell)
> Buffers: shared hit=110
>
> I don't know, but is the estimate for this portion of the plan improved by
> doing:
> | ALTER TABLE f_a_cell ALTER estimation_cell SET STATISTICS 500; ANALYZE
> f_a_cell;
>
> this does not help to the plan as a whole... but I am thinking about
increasing this parameter (size of sample) at the DB level


> > * In a slightly more complicated function I used temporary tables to be
> > able to narrow statistics [2] but I am afraid of system table bloating
> > because of the huge amount of usage of this function on the production
> > (hundred thousand of calls by day when data are to be analyzed).
>
> I would try this for sure - I think hundreds of calls per day would be no
> problem.  If you're concerned, you could add manual calls to do (for
> example)
> VACUUM pg_attribute; after dropping the temp tables.
>
> it is hundreds of thousands of calls (10^5) ... but yes I got some hints
how to avoid bloating (basically use temp tables longer and truncate them
instead of deleting when possible)


> BTW, we disable nested loops for the our analytic report queries.  I have
> never
> been able to avoid pathological plans any other way.
>

I will think about that.

AND

we further simplified the query and get again one good execution plan
https://explain.dalibo.com/plan/tCk :-)

I have some thoughts now:

* I know that PG is focused on OLTP rather then analytics, but we are happy
with it at all and do not wish to use another engine for analytical
queries... isn't somewhere some "PG analytical best practice" available?
* It seems that the the form / style of query has great impact on execution
plans... I was very happy with writing queries as CTEs on top of other CTEs
or layering VIEWS because you can really focus on the semantics of the
problem and I hoped that planner will somehow magically "compile" my code
and get something good enough with respect to performance. Of course, I
have to not use materialized CTEs, but it was not possible with NOT
MATERIALIZED version as performance was bad and I was not able even to get
oriented in exec. plan...

Thank you for your ideas! J.

Re: performance of analytical query

[Justin Pryzby]

On Fri, Nov 12, 2021 at 10:55:53AM -0700, Michael Lewis wrote:
> On Thu, Nov 11, 2021 at 7:42 PM Justin Pryzby  wrote:
> 
> > BTW, we disable nested loops for the our analytic report queries.  I have
> > never
> > been able to avoid pathological plans any other way.
> 
> Curious, do you see any problems from that? Are there certain nodes that
> really are best suited to a nested loop like a lateral subquery?

When I first disabled it years ago, I did it for the entire database, and it
caused issues with a more interactive, non-analytic query, on a non-partitioned
table.

So my second attempt was to disable nested loops only during report queries,
and I have not looked back.  For our report queries on partitioned tables, the
overhead of hashing a handful of rows is of no significance.  Any query that
finishes in 1sec would be exceptionally fast.

BTW, Jiří's inquiry caused me to look at the source of one of our historic
mis-estimates, and to realize that it's resolved in pg14:
https://www.postgresql.org/message-id/2022173102.GI17618%40telsasoft.com

I doubt that's enough to avoid catastrophic nested loop plans in every case
(especially CTEs on top of CTEs).

There was a discussion about discouraging nested loop plans that weren't
provably "safe" (due to returning at most one row, due to a unique index).
https://www.postgresql.org/message-id/CA%2BTgmoYtWXNpj6D92XxUfjT_YFmi2dWq1XXM9EY-CRcr2qmqbg%40mail.gmail.com

-- 
Justin

Re: performance of analytical query

[Michael Lewis]

On Thu, Nov 11, 2021 at 7:42 PM Justin Pryzby  wrote:

> BTW, we disable nested loops for the our analytic report queries.  I have
> never
> been able to avoid pathological plans any other way.
>

Curious, do you see any problems from that? Are there certain nodes that
really are best suited to a nested loop like a lateral subquery?

Re: performance of analytical query

[Justin Pryzby]

On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote:
> Hi folks,
> 
> we have found that (probably after VACUUM ANALYZE) one analytical query
> starts to be slow on our production DB. Moreover, more or less the same
> plan is used on our testing data (how to restore our testing data is
> described at the end of this email), or better to say the same problem
> exists in both (production vs testing data) scenarios: nested loop scanning
> CTE several thousand times is used due to the bad estimates:
> https://explain.dalibo.com/plan/sER#plan/node/87 (query is included on
> dalibo).

> Do you have any idea how to get HASH JOINS in the CTE w_1p_data instead of
> NESTED LOOPs?
> * Add some statistics to not get bad estimates on "lower-level" CTEs?

Do you know why the estimates are bad ?

Index Scan using t_map_plot_cell__cell_gid__idx on cm_plot2cell_mapping 
cm_plot2cell_mapping (cost=0.29..18.59 rows=381 width=12) (actual 
time=0.015..2.373 rows=3,898 loops=1)
Index Cond: (cm_plot2cell_mapping.estimation_cell = 
f_a_cell.estimation_cell)
Buffers: shared hit=110

I don't know, but is the estimate for this portion of the plan improved by 
doing:
| ALTER TABLE f_a_cell ALTER estimation_cell SET STATISTICS 500; ANALYZE 
f_a_cell;

> * In a slightly more complicated function I used temporary tables to be
> able to narrow statistics [2] but I am afraid of system table bloating
> because of the huge amount of usage of this function on the production
> (hundred thousand of calls by day when data are to be analyzed).

I would try this for sure - I think hundreds of calls per day would be no
problem.  If you're concerned, you could add manual calls to do (for example)
VACUUM pg_attribute; after dropping the temp tables.

BTW, we disable nested loops for the our analytic report queries.  I have never
been able to avoid pathological plans any other way.

Re: Performance for initial copy when using pg_logical to upgrade Postgres

[Westwood, Giles]

On Fri, Sep 24, 2021 at 5:02 PM Tim  wrote:

> I'm currently doing this with a 2.2 TB database.
>
> Best way IMO is to (UPDATE pg_index SET indisready = false ... ) for non
> PK indexes for the largest tables. Then just set it back to indisready =
> true after its done and run a REINDEX CONCURRENTLY on the indexes that were
> disabled.
>
> Got about a transfer speed of 100GB per 50 minutes with this method with
> consistent results.
>

Thanks Tim, that has worked great. I'm trying to automate the whole process
but I can't see a way of seeing when the initial pglogical copy is complete
short of checking the disk space.

All I've found is:-

select * from pglogical.local_sync_status;
 sync_kind | sync_subid | sync_nspname | sync_relname | sync_status |
sync_statuslsn
---++--+--+-+
 d | 1821676733 |  |  | d   | 0/0
(1 row)

or

xxx=# select * from pg_stat_replication ;
-[ RECORD 1 ]+
pid  | 3469521
usesysid | 77668435
usename  | xxx
application_name | _snap
client_addr  | 10.44.16.83
client_hostname  |
client_port  | 52594
backend_start| 2021-10-27 12:51:17.618734+00
backend_xmin | 221892481
state| startup
sent_lsn |
write_lsn|
flush_lsn|
replay_lsn   |
write_lag|
flush_lag|
replay_lag   |
sync_priority| 0
sync_state   | async

Re: Performance for initial copy when using pg_logical to upgrade Postgres

[Tim]

I'm currently doing this with a 2.2 TB database.

Best way IMO is to (UPDATE pg_index SET indisready = false ... ) for non PK
indexes for the largest tables. Then just set it back to indisready = true
after its done and run a REINDEX CONCURRENTLY on the indexes that were
disabled.

Got about a transfer speed of 100GB per 50 minutes with this method with
consistent results.

On Fri, Sep 24, 2021 at 11:49 AM Westwood, Giles 
wrote:

>
>
>
>
> On Fri, Sep 24, 2021 at 3:39 PM Justin Pryzby 
> wrote:
>
>> On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote:
>>
>> Did you see this thread and its suggestions to 1) set bulk load
>> parameters;
>> and, 2) drop indexes and FKs ?
>>
>>
>> https://www.postgresql.org/message-id/flat/4a8efc4e-a264-457d-a8e7-ae324ed9a...@thebuild.com
>>
>>
> I did actually but I wanted to avoid getting my hands dirty with anything
> schema wise. I've found another person with another similar situation:-
>
> https://github.com/2ndQuadrant/pglogical/issues/325
>
>

Re: Performance for initial copy when using pg_logical to upgrade Postgres

[Andrew Dunstan]

On 9/24/21 10:28 AM, Westwood, Giles wrote:
> At Orcid we're trying to upgrade our Postgres database (10 to 13)
> using pg_logical for no downtime. The problem we have is how long the
> initial copy is taking for the ~500GB database. If it takes say 20days
> to complete, will we need to have 20days of WAL files to start
> catching up when it's complete?
>
> I read an earlier thread which pointed me to the tool
> pglogical_create_subscriber which does a pg_basebackup to start the
> initial replication but this is only going to be useful for logical
> clusters on the same version.
>
> I had hoped that the COPY could be parallelized more by
> "max_sync_workers_per_subscription" which is set to 2. However there's
> only a single process:-
>
> postgres 1022196  6.0 24.5 588340 491564 ?       Ds   Sep22 193:19
> postgres: main: xxx  10.xx.xx.xx(59144) COPY
>
> One of the best resources I've found of real world examples are thead
> on gitlabs own gitlab about their Postgres migrations. They discussed
> one method that might work:-
>
> 1. Setup 9.6 secondary via streaming
> 2. Turn physical secondary into logical secondary
> 3. Shutdown and upgrade secondary to 10
> 4. Turn secondary back on.
>
> In which case we would only need the time required to perform the upgrade.


If you're using the pglogical extension, the best way is often to create
the replica as a physical replica (using pg_basebackup for example), and
then using the extension's utility program pglogical_create_subscriber
to convert the physical replica to a logical replica, which you then
upgrade and switch over to.


Of course, test it out before doing this for real.


cheers


andrew

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

Re: Performance for initial copy when using pg_logical to upgrade Postgres

[Westwood, Giles]

On Fri, Sep 24, 2021 at 3:39 PM Justin Pryzby  wrote:

> On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote:
>
> Did you see this thread and its suggestions to 1) set bulk load parameters;
> and, 2) drop indexes and FKs ?
>
>
> https://www.postgresql.org/message-id/flat/4a8efc4e-a264-457d-a8e7-ae324ed9a...@thebuild.com
>
>
I did actually but I wanted to avoid getting my hands dirty with anything
schema wise. I've found another person with another similar situation:-

https://github.com/2ndQuadrant/pglogical/issues/325

Re: Performance for initial copy when using pg_logical to upgrade Postgres

[Justin Pryzby]

On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote:
> At Orcid we're trying to upgrade our Postgres database (10 to 13) using
> pg_logical for no downtime. The problem we have is how long the initial
> copy is taking for the ~500GB database. If it takes say 20days to complete,
> will we need to have 20days of WAL files to start catching up when it's
> complete?

Did you see this thread and its suggestions to 1) set bulk load parameters;
and, 2) drop indexes and FKs ?

https://www.postgresql.org/message-id/flat/4a8efc4e-a264-457d-a8e7-ae324ed9a...@thebuild.com

-- 
Justin

Re: Performance issue with thousands of calls to procedures and functions?

[Daniel Westermann (DWE)]

>In your example, the bottleneck is calling the function f1. So you need to 
>check only this function. It is not important if other functions or 
>>procedures do database lookups.

>Or if it does just one database lookup, then you can use SQL language. I 
>repeat, PL/pgSQL is not good for ultra very frequent calls (where >there is 
>minimal other overhead).

>Generally, start of function or start of query are more expensive on Postgres 
>than on Oracle. Postgres is much more dynamic, and it needs >to do some 
>rechecks. The overhead is in nanoseconds, but nanoseconds x billions are lot 
>of seconds

Thank you Pavel, for all the information. That was very helpful.

Regards
Daniel

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

pá 30. 7. 2021 v 10:12 odesílatel Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> napsal:

>
> pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) <
> daniel.westerm...@dbi-services.com> napsal:
>
> Hi,
>
> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
> know, the latest version is 12.7). The migration included a lot of PL/SQL
> code. Attached a very simplified test case. As you can see there are
> thousands, even nested calls to procedures and functions. The test case
> does not even touch any relation, in reality these functions and procedures
> perform selects, insert and updates.
>
> I've tested this on my local sandbox (Debian 11) and here are the results
> (three runs each):
>
> Head:
> Time: 97275.109 ms (01:37.275)
> Time: 103241.352 ms (01:43.241)
> Time: 104246.961 ms (01:44.247)
>
> 13.3:
> Time: 122179.311 ms (02:02.179)
> Time: 122622.859 ms (02:02.623)
> Time: 125469.711 ms (02:05.470)
>
> 12.7:
> Time: 182131.565 ms (03:02.132)
> Time: 177393.980 ms (02:57.394)
> Time: 177550.204 ms (02:57.550)
>
>
> It seems there are some optimizations in head, but 13.3 and 12.7 are
> noticeable slower.
>
>
> Question: Is it expected that this takes minutes sitting on the CPU or is
> there a performance issue? Doing the same in Oracle takes around 30
> seconds. I am not saying that this implementation is brilliant, but for the
> moment it is like it is.
>
>
> >Unfortunately yes, it is possible. PL/pgSQL is interpreted language
> without **any** compiler optimization. PL/SQL is now a fully compiled
> >language with a lot of compiler optimization. There is main overhead with
> repeated function's initialization and variable's initialization. Your
> >example is the worst case for PL/pgSQL - and I am surprised so the
> difference is only 3-4x.
>
> >Maybe (probably) Oracle does inlining of f1 function. You can get the
> same effect if you use SQL language for this function. PL/pgSQL is >bad
> language for one line functions. When I did it, then then I got 34 sec (on
> my comp against 272 sec)
>
> >and mark this function as immutable helps a lot of too - it takes 34 sec
> on my computer.
>
> Thank you, Pavel. As far as I understand the docs, I cannot use immutable
> as the "real" functions and procedures do database lookups.
>

In your example, the bottleneck is calling the function f1. So you need to
check only this function. It is not important if other functions or
procedures do database lookups.

Or if it does just one database lookup, then you can use SQL language. I
repeat, PL/pgSQL is not good for ultra very frequent calls (where there is
minimal other overhead).

Generally, start of function or start of query are more expensive on
Postgres than on Oracle. Postgres is much more dynamic, and it needs to do
some rechecks. The overhead is in nanoseconds, but nanoseconds x billions
are lot of seconds


> Regards
> Daniel
>
>
>
> Thanks for any inputs
> Regards
> Daniel
>
>

Re: Performance issue with thousands of calls to procedures and functions?

[Daniel Westermann (DWE)]

pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) 
mailto:daniel.westerm...@dbi-services.com>> 
napsal:
Hi,

we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I know, 
the latest version is 12.7). The migration included a lot of PL/SQL code. 
Attached a very simplified test case. As you can see there are thousands, even 
nested calls to procedures and functions. The test case does not even touch any 
relation, in reality these functions and procedures perform selects, insert and 
updates.

I've tested this on my local sandbox (Debian 11) and here are the results 
(three runs each):

Head:
Time: 97275.109 ms (01:37.275)
Time: 103241.352 ms (01:43.241)
Time: 104246.961 ms (01:44.247)

13.3:
Time: 122179.311 ms (02:02.179)
Time: 122622.859 ms (02:02.623)
Time: 125469.711 ms (02:05.470)

12.7:
Time: 182131.565 ms (03:02.132)
Time: 177393.980 ms (02:57.394)
Time: 177550.204 ms (02:57.550)


It seems there are some optimizations in head, but 13.3 and 12.7 are noticeable 
slower.

Question: Is it expected that this takes minutes sitting on the CPU or is there 
a performance issue? Doing the same in Oracle takes around 30 seconds. I am not 
saying that this implementation is brilliant, but for the moment it is like it 
is.

>Unfortunately yes, it is possible. PL/pgSQL is interpreted language without 
>**any** compiler optimization. PL/SQL is now a fully compiled >language with a 
>lot of compiler optimization. There is main overhead with repeated function's 
>initialization and variable's initialization. Your >example is the worst case 
>for PL/pgSQL - and I am surprised so the difference is only 3-4x.

>Maybe (probably) Oracle does inlining of f1 function. You can get the same 
>effect if you use SQL language for this function. PL/pgSQL is >bad language 
>for one line functions. When I did it, then then I got 34 sec (on my comp 
>against 272 sec)

>and mark this function as immutable helps a lot of too - it takes 34 sec on my 
>computer.

Thank you, Pavel. As far as I understand the docs, I cannot use immutable as 
the "real" functions and procedures do database lookups.

Regards
Daniel



Thanks for any inputs
Regards
Daniel

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

pá 30. 7. 2021 v 10:04 odesílatel Pavel Stehule 
napsal:

> Hi
>
> pá 30. 7. 2021 v 10:02 odesílatel Imre Samu  napsal:
>
>> Hi Daniel,
>>
>> side note:
>>
>> Maybe you can tune the "function" with some special query optimizer
>> attributes:
>>  IMMUTABLE | STABLE | VOLATILE |  PARALLEL SAFE
>>
>> so in your example:
>>  create or replace function f1(int) returns double precision as
>>
>> $$
>> declare
>> begin
>>   return 1;
>> end;
>> $$ language plpgsql *IMMUTABLE PARALLEL SAFE*;
>>
>>
> It cannot help in this case. PL/pgSQL routine (and expression
> calculations) is one CPU every time.
>

IMMUTABLE helps, surely, because it is translated to constant in this case.

Regards

Pavel


> Regards
>
> Pavel
>
>
>>
>> """  : https://www.postgresql.org/docs/13/sql-createfunction.html
>> PARALLEL SAFE :
>> * indicates that the function is safe to run in parallel mode without
>> restriction.*
>> IMMUTABLE *: indicates that the function cannot modify the database and
>> always returns the same result when given the same argument values; that
>> is, it does not do database lookups or otherwise use information not
>> directly present in its argument list. If this option is given, any call of
>> the function with all-constant arguments can be immediately replaced with
>> the function value.*
>> """
>>
>> Regards,
>>   Imre
>>
>> Daniel Westermann (DWE)  ezt írta
>> (időpont: 2021. júl. 30., P, 9:12):
>>
>>> Hi,
>>>
>>> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
>>> know, the latest version is 12.7). The migration included a lot of PL/SQL
>>> code. Attached a very simplified test case. As you can see there are
>>> thousands, even nested calls to procedures and functions. The test case
>>> does not even touch any relation, in reality these functions and procedures
>>> perform selects, insert and updates.
>>>
>>> I've tested this on my local sandbox (Debian 11) and here are the
>>> results (three runs each):
>>>
>>> Head:
>>> Time: 97275.109 ms (01:37.275)
>>> Time: 103241.352 ms (01:43.241)
>>> Time: 104246.961 ms (01:44.247)
>>>
>>> 13.3:
>>> Time: 122179.311 ms (02:02.179)
>>> Time: 122622.859 ms (02:02.623)
>>> Time: 125469.711 ms (02:05.470)
>>>
>>> 12.7:
>>> Time: 182131.565 ms (03:02.132)
>>> Time: 177393.980 ms (02:57.394)
>>> Time: 177550.204 ms (02:57.550)
>>>
>>>
>>> It seems there are some optimizations in head, but 13.3 and 12.7 are
>>> noticeable slower.
>>>
>>> Question: Is it expected that this takes minutes sitting on the CPU or
>>> is there a performance issue? Doing the same in Oracle takes around 30
>>> seconds. I am not saying that this implementation is brilliant, but for the
>>> moment it is like it is.
>>>
>>> Thanks for any inputs
>>> Regards
>>> Daniel
>>>
>>>

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

Hi

pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> napsal:

> Hi,
>
> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
> know, the latest version is 12.7). The migration included a lot of PL/SQL
> code. Attached a very simplified test case. As you can see there are
> thousands, even nested calls to procedures and functions. The test case
> does not even touch any relation, in reality these functions and procedures
> perform selects, insert and updates.
>
> I've tested this on my local sandbox (Debian 11) and here are the results
> (three runs each):
>
> Head:
> Time: 97275.109 ms (01:37.275)
> Time: 103241.352 ms (01:43.241)
> Time: 104246.961 ms (01:44.247)
>
> 13.3:
> Time: 122179.311 ms (02:02.179)
> Time: 122622.859 ms (02:02.623)
> Time: 125469.711 ms (02:05.470)
>
> 12.7:
> Time: 182131.565 ms (03:02.132)
> Time: 177393.980 ms (02:57.394)
> Time: 177550.204 ms (02:57.550)
>
>
> It seems there are some optimizations in head, but 13.3 and 12.7 are
> noticeable slower.
>

> Question: Is it expected that this takes minutes sitting on the CPU or is
> there a performance issue? Doing the same in Oracle takes around 30
> seconds. I am not saying that this implementation is brilliant, but for the
> moment it is like it is.
>

Unfortunately yes, it is possible. PL/pgSQL is interpreted language without
**any** compiler optimization. PL/SQL is now a fully compiled language with
a lot of compiler optimization. There is main overhead with repeated
function's initialization and variable's initialization. Your example is
the worst case for PL/pgSQL - and I am surprised so the difference is only
3-4x.

Maybe (probably) Oracle does inlining of f1 function. You can get the same
effect if you use SQL language for this function. PL/pgSQL is bad language
for one line functions. When I did it, then then I got 34 sec (on my comp
against 272 sec)

and mark this function as immutable helps a lot of too - it takes 34 sec on
my computer.

Regards

Pavel






> Thanks for any inputs
> Regards
> Daniel
>
>

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

Hi

pá 30. 7. 2021 v 10:02 odesílatel Imre Samu  napsal:

> Hi Daniel,
>
> side note:
>
> Maybe you can tune the "function" with some special query optimizer
> attributes:
>  IMMUTABLE | STABLE | VOLATILE |  PARALLEL SAFE
>
> so in your example:
>  create or replace function f1(int) returns double precision as
>
> $$
> declare
> begin
>   return 1;
> end;
> $$ language plpgsql *IMMUTABLE PARALLEL SAFE*;
>
>
It cannot help in this case. PL/pgSQL routine (and expression calculations)
is one CPU every time.

Regards

Pavel


>
> """  : https://www.postgresql.org/docs/13/sql-createfunction.html
> PARALLEL SAFE :
> * indicates that the function is safe to run in parallel mode without
> restriction.*
> IMMUTABLE *: indicates that the function cannot modify the database and
> always returns the same result when given the same argument values; that
> is, it does not do database lookups or otherwise use information not
> directly present in its argument list. If this option is given, any call of
> the function with all-constant arguments can be immediately replaced with
> the function value.*
> """
>
> Regards,
>   Imre
>
> Daniel Westermann (DWE)  ezt írta
> (időpont: 2021. júl. 30., P, 9:12):
>
>> Hi,
>>
>> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
>> know, the latest version is 12.7). The migration included a lot of PL/SQL
>> code. Attached a very simplified test case. As you can see there are
>> thousands, even nested calls to procedures and functions. The test case
>> does not even touch any relation, in reality these functions and procedures
>> perform selects, insert and updates.
>>
>> I've tested this on my local sandbox (Debian 11) and here are the results
>> (three runs each):
>>
>> Head:
>> Time: 97275.109 ms (01:37.275)
>> Time: 103241.352 ms (01:43.241)
>> Time: 104246.961 ms (01:44.247)
>>
>> 13.3:
>> Time: 122179.311 ms (02:02.179)
>> Time: 122622.859 ms (02:02.623)
>> Time: 125469.711 ms (02:05.470)
>>
>> 12.7:
>> Time: 182131.565 ms (03:02.132)
>> Time: 177393.980 ms (02:57.394)
>> Time: 177550.204 ms (02:57.550)
>>
>>
>> It seems there are some optimizations in head, but 13.3 and 12.7 are
>> noticeable slower.
>>
>> Question: Is it expected that this takes minutes sitting on the CPU or is
>> there a performance issue? Doing the same in Oracle takes around 30
>> seconds. I am not saying that this implementation is brilliant, but for the
>> moment it is like it is.
>>
>> Thanks for any inputs
>> Regards
>> Daniel
>>
>>

Re: Performance issue with thousands of calls to procedures and functions?

[Imre Samu]

Hi Daniel,

side note:

Maybe you can tune the "function" with some special query optimizer
attributes:
 IMMUTABLE | STABLE | VOLATILE |  PARALLEL SAFE

so in your example:
 create or replace function f1(int) returns double precision as

$$
declare
begin
  return 1;
end;
$$ language plpgsql *IMMUTABLE PARALLEL SAFE*;


"""  : https://www.postgresql.org/docs/13/sql-createfunction.html
PARALLEL SAFE :
* indicates that the function is safe to run in parallel mode without
restriction.*
IMMUTABLE *: indicates that the function cannot modify the database and
always returns the same result when given the same argument values; that
is, it does not do database lookups or otherwise use information not
directly present in its argument list. If this option is given, any call of
the function with all-constant arguments can be immediately replaced with
the function value.*
"""

Regards,
  Imre

Daniel Westermann (DWE)  ezt írta
(időpont: 2021. júl. 30., P, 9:12):

> Hi,
>
> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
> know, the latest version is 12.7). The migration included a lot of PL/SQL
> code. Attached a very simplified test case. As you can see there are
> thousands, even nested calls to procedures and functions. The test case
> does not even touch any relation, in reality these functions and procedures
> perform selects, insert and updates.
>
> I've tested this on my local sandbox (Debian 11) and here are the results
> (three runs each):
>
> Head:
> Time: 97275.109 ms (01:37.275)
> Time: 103241.352 ms (01:43.241)
> Time: 104246.961 ms (01:44.247)
>
> 13.3:
> Time: 122179.311 ms (02:02.179)
> Time: 122622.859 ms (02:02.623)
> Time: 125469.711 ms (02:05.470)
>
> 12.7:
> Time: 182131.565 ms (03:02.132)
> Time: 177393.980 ms (02:57.394)
> Time: 177550.204 ms (02:57.550)
>
>
> It seems there are some optimizations in head, but 13.3 and 12.7 are
> noticeable slower.
>
> Question: Is it expected that this takes minutes sitting on the CPU or is
> there a performance issue? Doing the same in Oracle takes around 30
> seconds. I am not saying that this implementation is brilliant, but for the
> moment it is like it is.
>
> Thanks for any inputs
> Regards
> Daniel
>
>

Re: Performance of lateral join

[Simen Andreas Andreassen Lønsethagen]

>Easy first question: is the temp table analyzed before being used in a 
> join ?

No, I haven't done that. Today, I tried to run 

ANALYZE records_to_filter_on;

on the same sample data set (3.75 million rows) before the join, and it did not 
seem to make much of a difference in terms of time (new output of EXPLAIN 
ANALYZE at https://explain.dalibo.com/plan/YZu - it seems very similar to me). 

Not sure if it is relevant, but I did some experimentation with smaller 
samples, and for those, there was a significant speedup. Could there be some 
size threshold on the temp table after which running ANALYZE does not yield any 
speedup?

>I think the attachment is missing.

Added now.

Simen




explain_analyze.json
Description: explain_analyze.json


pg_settings.conf
Description: pg_settings.conf

Re: Performance of lateral join

[Justin Pryzby]

On Tue, Jul 27, 2021 at 09:08:49AM +, Simen Andreas Andreassen Lønsethagen 
wrote:
> >Easy first question: is the temp table analyzed before being used in a 
> > join ?
> 
> No, I haven't done that. Today, I tried to run 
> 
>   ANALYZE records_to_filter_on;
> 
> on the same sample data set (3.75 million rows) before the join, and it did 
> not seem to make much of a difference in terms of time (new output of EXPLAIN 
> ANALYZE at https://explain.dalibo.com/plan/YZu - it seems very similar to 
> me). 

If the "shape" of the plan didn't change, then ANALYZE had no effect.

I think you'd see an improvement if both tables were ordered by foedselsnummer.
It might be that that's already somewhat/partially true (?)

I suggest to create an index on the temp table's r.foedselsnummer, CLUSTER on
that index, and then ANALYZE the table.  The index won't be useful for this
query, it's just for clustering (unless you can instead populate the temp table
in order).

Check if there's already high correlation of dpd_bis_foedselsnummer (over 0.9):
| SELECT tablename, attname, inherited, null_frac, n_distinct, correlation FROM 
pg_stats WHERE attname='dpd_bis_foedselsnummer' AND tablename='...';

If not, consider clustering on the existing "unique_descending" index and then
analyzing that table, too.

This would also affect performance of other queries - hopefully improving
several things at once.

-- 
Justin

Re: Performance of lateral join

[Justin Pryzby]

On Mon, Jul 26, 2021 at 01:56:54PM +, Simen Andreas Andreassen Lønsethagen 
wrote:
> To create the subsets, I (or rather my application) will receive lists of 
> records which should be matched according to some business logic. Each of 
> these lists will be read into a temporary table:

Easy first question: is the temp table analyzed before being used in a join ?
(This is unrelated to "explain analyze").

> I am running PostgreSQL 11.9 on x86_64-pc-linux-gnu using AWS Aurora on a 
> db.t3.large instance (https://aws.amazon.com/rds/instance-types/). The output 
> of 
> FROM pg_settings
> is attached in pg_settings.conf.

I think the attachment is missing.

-- 
Justin

Re: Performance Issue on a table

[Justin Pryzby]

On Thu, Jul 22, 2021 at 01:54:25PM +, Manoj Kumar wrote:
> Recently we have noticed that in one of our DB instances there is a potential 
> delay in querying a table from java code. could you please check the attached 
> log and help understand what is the problem and which direction should be 
> look into solving this delay of 4 odd mins ?

I'm not familiar with the log, but it looks like the delay is in query parsing
(ParseComplete).  Which seems weird.  You might try running wireshark to verify
that.  Or check postgres logs, and make sure the query isn't being blocked by
DDL commands.  Make sure these are enabled:

log_lock_waits = 'on'
deadlock_timeout = '1s'

> 4:25:00 PM ... execute FINEST:   simple execute, 
> handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@13e344d, 
> maxRows=0, fetchSize=0, flags=1
> 4:25:00 PM ... sendSimpleQuery FINEST:  FE=> SimpleQuery(query="SAVEPOINT 
> PGJDBC_AUTOSAVE")
> 4:25:00 PM ... sendParse FINEST:  FE=> Parse(stmt=null,query="SELECT RECID 
> FROM TAFJ_HASHLOCKS WHERE RECID = $1 FOR UPDATE NOWAIT ",oids={1043})
> 4:25:00 PM ... sendBind FINEST:  FE=> 
> Bind(stmt=null,portal=null,$1=<'256292129'>,type=VARCHAR)
> 4:25:00 PM ... sendDescribePortal FINEST:  FE=> Describe(portal=null)
> 4:25:00 PM ... sendExecute FINEST:  FE=> Execute(portal=null,limit=0)
> 4:25:00 PM ... sendSync FINEST:  FE=> Sync
> 4:25:00 PM ... receiveCommandStatus FINEST:  <=BE CommandStatus(RELEASE)
> 4:25:00 PM ... receiveRFQ FINEST:  <=BE ReadyForQuery(T)
> 4:25:00 PM ... receiveCommandStatus FINEST:  <=BE CommandStatus(SAVEPOINT)
> 4:25:00 PM ... receiveRFQ FINEST:  <=BE ReadyForQuery(T)
> 4:29:20 PM ... processResults FINEST:  <=BE ParseComplete [null]
> 4:29:20 PM ... processResults FINEST:  <=BE BindComplete [unnamed]
> 4:29:20 PM ... receiveFields FINEST:  <=BE RowDescription(1)

Re: Performance benchmark of PG

[Manish Lad]

Thanks a lot.

On Mon, 19 Jul 2021, 22:18 Ninad Shah,  wrote:

> As Thomas rightly pointed about the feasibility of benchmarking. You may
> still compare performance of queries on both Exadata as well as PostgreSQL.
> IMO, it may not be on par, but it must be acceptable.
>
> In the contemporary world, 60TB isn't really a huge database. So, I hardly
> think you should find any performance issues on PostgreSQL.
>
> All the best.
>
>
> Regards,
> Ninad Shah
>
>
> On Mon, 19 Jul 2021 at 16:54, Manish Lad  wrote:
>
>> Thank you all for your swift response.
>>
>> Thank you again.
>>
>> Manish
>>
>> On Mon, 19 Jul 2021, 15:39 Manish Lad,  wrote:
>>
>>> Dear all,

>>> We are planning to migrate Oracle exadata database to postgresql and db
>>> size ranges from 1 tb to 60 TB.
>>>
>>> Will the PG support this with the performance matching to that of
>>> exadata applince?
>>> If anyone could point me in the right direction where i xan get the
>>> benchmarking done for these two databases either on prime or any cloud
>>> would be great.
>>>
>>> Thanks all in advance.
>>>
>>> Manish
>>>

Re: Performance benchmark of PG

[Ninad Shah]

As Thomas rightly pointed about the feasibility of benchmarking. You may
still compare performance of queries on both Exadata as well as PostgreSQL.
IMO, it may not be on par, but it must be acceptable.

In the contemporary world, 60TB isn't really a huge database. So, I hardly
think you should find any performance issues on PostgreSQL.

All the best.


Regards,
Ninad Shah


On Mon, 19 Jul 2021 at 16:54, Manish Lad  wrote:

> Thank you all for your swift response.
>
> Thank you again.
>
> Manish
>
> On Mon, 19 Jul 2021, 15:39 Manish Lad,  wrote:
>
>> Dear all,
>>>
>> We are planning to migrate Oracle exadata database to postgresql and db
>> size ranges from 1 tb to 60 TB.
>>
>> Will the PG support this with the performance matching to that of exadata
>> applince?
>> If anyone could point me in the right direction where i xan get the
>> benchmarking done for these two databases either on prime or any cloud
>> would be great.
>>
>> Thanks all in advance.
>>
>> Manish
>>
>>>

Re: Performance benchmark of PG

[Manish Lad]

Thank you all for your swift response.

Thank you again.

Manish

On Mon, 19 Jul 2021, 15:39 Manish Lad,  wrote:

> Dear all,
>>
> We are planning to migrate Oracle exadata database to postgresql and db
> size ranges from 1 tb to 60 TB.
>
> Will the PG support this with the performance matching to that of exadata
> applince?
> If anyone could point me in the right direction where i xan get the
> benchmarking done for these two databases either on prime or any cloud
> would be great.
>
> Thanks all in advance.
>
> Manish
>
>>

Re: Performance benchmark of PG

[Thomas Kellerer]

Manish Lad schrieb am 19.07.2021 um 12:09:
> We are planning to migrate Oracle exadata database to postgresql and
> db size ranges from 1 tb to 60 TB.
>
> Will the PG support this with the performance matching to that of
> exadata applince? If anyone could point me in the right direction
> where i xan get the benchmarking done for these two databases either
> on prime or any cloud would be great.


As already pointed out, you won't find such a benchmark.

You will have to run such a benchmark yourself. Ideally with a workload
that represents your use case. Or maybe with something like HammerDB.

But Exadata isn't only software, it's also hardware especially designed
to work together with Oracle's enterprise edition.

So if you want to get any reasonable results, you will at least have to
buy hardware that matches the Exadata HW specifications.

So if you run your own tests, make sure you buy comparable HW for
Postgres as well (lots of RAM and many fast server grade NVMes)

Re: Performance benchmark of PG

[Hüseyin Demir]

Hi,

The question can not be answered in a proper way. Because, in PostgreSQL,
performance(response time in query execution events) depends on

1. Your disk/storage hardware. The performance can vary between SSD and HDD
for example.
2. Your PostgreSQL configurations. In other words, configuration parameters
can change your performance metrics. But you have to define your
queries,data size that a query can SELECT each time and queries that
INSERTS/UPDATES to database.
3. Your CPU and MEMORY hardwares can also change your performance metrics.
You have to compare your hardware infrastructure with Exadata appliances.
4. You also have to consider the connection pooling part in your
application part. PostgreSQL can suffer from performance problems because
of lack of connection pooling.

Regards.


Manish Lad , 19 Tem 2021 Pzt, 14:09 tarihinde şunu
yazdı:

> Yes you are right. I also experienced same in one such migration from db2
> to PG which had read faster but the write was not meeting the need.
>
> We then noticed the differences in disk types.
>
> Once changed it matched the source.
>
> Thanks and Regards
>
> Manish
>
> On Mon, 19 Jul 2021, 16:34 Laurenz Albe,  wrote:
>
>> On Mon, 2021-07-19 at 15:39 +0530, Manish Lad wrote:
>> > We are planning to migrate Oracle exadata database to postgresql and db
>> size ranges from 1 tb to 60 TB.
>> >
>> > Will the PG support this with the performance matching to that of
>> exadata applince?
>> > If anyone could point me in the right direction where i xan get the
>> benchmarking done
>> >  for these two databases either on prime or any cloud would be great.
>>
>> You won't find any trustworthy benchmarks anywhere, because Oracle
>> expressedly
>> forbids publishing of benchmark results in its license, unless Oracle has
>> given
>> its permission.
>>
>> The question cannot be answered, because performance depends on your
>> workload,
>> configuration, software and hardware.  Perhaps PostgreSQL will be faster,
>> perhaps not.
>>
>> Test and see.
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>>

-- 
Hüseyin Demir

Senior Database Platform Engineer

Twitter:  https://twitter.com/d3rh5n
Linkedin: hseyindemir

Github: https://github.com/hseyindemir
Gitlab: https://gitlab.com/demirhuseyinn.94
Medium: https://demirhuseyinn-94.medium.com/

Re: Performance benchmark of PG

[Manish Lad]

Yes you are right. I also experienced same in one such migration from db2
to PG which had read faster but the write was not meeting the need.

We then noticed the differences in disk types.

Once changed it matched the source.

Thanks and Regards

Manish

On Mon, 19 Jul 2021, 16:34 Laurenz Albe,  wrote:

> On Mon, 2021-07-19 at 15:39 +0530, Manish Lad wrote:
> > We are planning to migrate Oracle exadata database to postgresql and db
> size ranges from 1 tb to 60 TB.
> >
> > Will the PG support this with the performance matching to that of
> exadata applince?
> > If anyone could point me in the right direction where i xan get the
> benchmarking done
> >  for these two databases either on prime or any cloud would be great.
>
> You won't find any trustworthy benchmarks anywhere, because Oracle
> expressedly
> forbids publishing of benchmark results in its license, unless Oracle has
> given
> its permission.
>
> The question cannot be answered, because performance depends on your
> workload,
> configuration, software and hardware.  Perhaps PostgreSQL will be faster,
> perhaps not.
>
> Test and see.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Performance benchmark of PG

[Laurenz Albe]

On Mon, 2021-07-19 at 15:39 +0530, Manish Lad wrote:
> We are planning to migrate Oracle exadata database to postgresql and db size 
> ranges from 1 tb to 60 TB. 
> 
> Will the PG support this with the performance matching to that of exadata 
> applince? 
> If anyone could point me in the right direction where i xan get the 
> benchmarking done
>  for these two databases either on prime or any cloud would be great. 

You won't find any trustworthy benchmarks anywhere, because Oracle expressedly
forbids publishing of benchmark results in its license, unless Oracle has given
its permission.

The question cannot be answered, because performance depends on your workload,
configuration, software and hardware.  Perhaps PostgreSQL will be faster, 
perhaps not.

Test and see.

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

Re: Performance issues related to left join and order by

[David Rowley]

On Tue, 2 Mar 2021 at 21:53, Liu, Xinyu  wrote:
> *Expected Behavior
>
> Since these two queries are semantically equivalent, we were hoping that 
> PostgreSQL would evaluate them in roughly the same amount of time.
> It looks to me that there is a missing optimization rule related to pushing 
> the sort operator (i.e., order and limit) through the left join.
> Given the significant query execution time difference, I was wondering if it 
> is worth adding such a rule to make the system evaluate the first query more 
> efficiently.
> It would also be helpful if you could comment on if there is a standard 
> practice to evaluate the tradeoff associated with adding such a rule in 
> Postgresql.

We currently don't attempt to push down LIMIT clauses into subqueries.
Before we did that we'd need to get much better at figuring out how
joins duplicate rows so that we could be sure that we're not limiting
the subquery more than the number of records that the outer query will
need to reach its limit.

If you want some advice, you're likely to get more people on your side
and possible support for making improvements to the query planner if
you provide examples that look remotely like real-world queries.   In
the other emails that I've read from you on this list [1], it seems
you're example queries are all completely bogus.  I suspect that the
queries are generated by some fuzz testing tool.  I very much imagine
that really don't need help with these at all.  With respect, it seems
to me that there's about zero chance that you genuinely need the
results of this query more quickly and you've come for help with that.

Because PostgreSQL does not proactively cache query plans, ad-hoc
queries are always parsed, planned then executed.  This means that
it's often not practical to spend excessive amounts of time planning a
query that gets executed just once. Adding new optimisations to the
query planner means they either have to be very cheap to detect, or
they must pay off in many cases.

If you happen to think there's a genuine case for having the query
planner do a better job of doing LIMIT pushdowns into subqueries, then
you're welcome to submit a patch to implement that.  You'll also need
to carefully document exactly which cases the LIMIT can be pushed down
and when it cannot.  That's the hard part. The actual pushing down of
the clause is dead easy.  If you're going to do that, then I'd suggest
you come up with better examples than this one. I don't think many
people will get on board with your newly proposed optimisations when
the queries are obviously not real.  It's hard to imagine the
optimisation being useful to any queries with a query that's so
obviously not a real one.

David

[1] 
https://www.postgresql.org/message-id/BN7PR07MB52024B973EAB075F4DF6C19ACD999%40BN7PR07MB5202.namprd07.prod.outlook.com

Re: Performance issue when we use policies for Row Level Security along with functions

[Gopisetty, Ramesh]

Hi,

Thanks for providing the details.  But things didn't work out even after 
changing the functions to STABLE/IMMUTABLE.   If i don't use the function it 
works for RLS.  If i use functions it doesn't work.

I tried with both IMMUTABLE and STABLE.  Both didn't work.Is there a way to 
use function in RLS to have the index scan rather than the seq scan.   Please 
help me out if that works or not.

Currently, we are in the processes of converting oracle to postgres.  Under 
oracle we have used functions and there exists a lot of logic in it.

Thank you.

Function

drop function f_sel_1;
CREATE OR REPLACE FUNCTION f_sel_1(key character varying)
 RETURNS character varying
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
Declare
v_status boolean;
key_ctx varchar(4000);
BEGIN

   SELECT INTO key_ctx current_setting('key_header' || '.'||'ctx_key_fil');

if key = key_ctx then
return key_ctx;
end if;
return '';
exception when undefined_object then
return '';

END;
$function$
;



drop policy policy_sel on test1;
create policy policy_sel on test1 FOR
SELECT
to sch USING  ( key =
f_sel_1(key)
);

explain analyze select * from test1;
  QUERY PLAN
---
 Seq Scan on test1  (cost=0.00..1555.61 rows=25 width=555) (actual 
time=35.124..35.124 rows=0 loops=1)
   Filter: ((key)::text = (f_sel_1(key))::text)
   Rows Removed by Filter: 4909
 Planning Time: 0.070 ms
 Execution Time: 35.142 ms
(5 rows)



drop policy policy_sel on test1;
create policy policy_sel on test1 FOR
SELECT
to sch USING  (
 key =
(
current_setting('key_header'|| '.' || 'ctx_key_fil')
)
  );


explain analyze select * from test1;
  QUERY PLAN
---
 Bitmap Heap Scan on test1  (cost=9.78..270.01 rows=193 width=555) (actual 
time=0.040..0.069 rows=193 loops=1)
   Recheck Cond: ((key)::text = current_setting('key_header.ctx_key_fil'::text))
   Heap Blocks: exact=13
   ->  Bitmap Index Scan on test1_pkey  (cost=0.00..9.73 rows=193 width=0) 
(actual time=0.030..0.030 rows=193 loops=1)
 Index Cond: ((key)::text = 
current_setting('key_header.ctx_key_fil'::text))
 Planning Time: 0.118 ms
 Execution Time: 0.094 ms
(7 rows)


CREATE TABLE sch.test1 (
key varchar(50) NOT NULL,
id varchar(32) NOT NULL,
begin_date date NOT NULL,
eff_date_end date NULL,
code varchar(100) NULL,
CONSTRAINT test1_pkey PRIMARY KEY (vpd_key, id, begin_date)
);


Thank you.

Regards,
Ramesh G

From: Tom Lane 
Sent: Wednesday, September 16, 2020 10:17 AM
To: Gopisetty, Ramesh 
Cc: pgsql-performance@lists.postgresql.org 

Subject: Re: Performance issue when we use policies for Row Level Security 
along with functions

"Gopisetty, Ramesh"  writes:
> Policy
> create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in 
> (f_sel_policy_test(testkey))  );
> Going to a Sequential scan instead of index scan.  Hence, performance issue.

> If i replace the policy with stright forward without function then it chooses 
> the index.   Not sure how i can implement with the function.
> create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in 
> ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).

" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition.  So there's no fixed value that the
index can search on.

If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?

As Luis noted, there's also the problem that an indexable condition
can't be volatile.  I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.

regards, tom lane

Re: Performance issue when we use policies for Row Level Security along with functions

[Tom Lane]

"Gopisetty, Ramesh"  writes:
> Policy
> create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in 
> (f_sel_policy_test(testkey))  );
> Going to a Sequential scan instead of index scan.  Hence, performance issue.

> If i replace the policy with stright forward without function then it chooses 
> the index.   Not sure how i can implement with the function.
> create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in 
> ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).

" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition.  So there's no fixed value that the
index can search on.

If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?

As Luis noted, there's also the problem that an indexable condition
can't be volatile.  I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.

regards, tom lane

Re: Performance issue when we use policies for Row Level Security along with functions

[luis . roberto]

De: "Gopisetty, Ramesh"  
Para: "pgsql-performance"  
Enviadas: Quarta-feira, 16 de setembro de 2020 0:39:08 
Assunto: Performance issue when we use policies for Row Level Security along 
with functions 





BQ_BEGIN

Hi, 

I'm seeing a strange behavior when we implement policies (for RLS - Row level 
security) using functions. 

table test consists of columns testkey,oid,category,type,description... 

Policy 

create policy policy_sel on test FOR SELECT to ram1 USING ( testkey in 
(f_sel_policy_test(testkey)) ); 

Going to a Sequential scan instead of index scan. Hence, performance issue. 

pgwfc01q=> explain analyze select * from test; 
QUERY PLAN 

 
Seq Scan on test (cost=0.00..25713.12 rows=445 width=712) (actual 
time=1849.592..1849.592 rows=0 loops=1) 
Filter: (( testkey )::text = (f_sel_policy_test( testkey ))::text) 
Rows Removed by Filter: 88930 
Planning Time: 0.414 ms 
Execution Time: 1849.614 ms 
(5 rows) 


The function is 

CREATE OR REPLACE FUNCTION vpd_sec_usr.f_sel_policy_test(testkey character 
varying) 
RETURNS character varying 
LANGUAGE plpgsql 
AS $function$ 
Declare 
v_status character varying; 
BEGIN 

if vpd_key = 'COMMON' then 
return ''' COMMON '''; 
elsif vpd_key = (' COMMON_ ' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')) then 
return ''' COMMON_ ' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')||; 
elsif vpd_key = SYS_CONTEXT('ctx_ng_vpd', 'ctx_key_fil') then 
return '''co'','''||SYS_CONTEXT('ctx_ng', 'ctx_testkey_fil')||; 
end if; 
return 'false'; 
exception when undefined_object then 
return 'failed'; 
END; 
$function$ 
; 


If i replace the policy with stright forward without function then it chooses 
the index. Not sure how i can implement with the function. 

create policy policy_sel on test FOR SELECT to ram1 USING ( testkey in 
('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil'))); 

QUERY PLAN 


 
- 
Bitmap Heap Scan on test (cost=396.66..2966.60 rows=13396 width=712) (actual 
time=0.693..2.318 rows=13159 loops=1) 
Recheck Cond: (( testkey )::text = ANY ((ARRAY['COMMON'::character varying, 
(current_setting('ctx_vpd.ctx_key_fil'::text))::character varying])::text[])) 
Heap Blocks: exact=373 
-> Bitmap Index Scan on test_pkey (cost=0.00..393.31 rows=13396 width=0) 
(actual time=0.653..0.653 rows=13159 l 
oops=1) 
Index Cond: (( testkey )::text = ANY ((ARRAY['COMMON'::character varying, 
(current_setting('ctx_vpd.ctx 
_key_fil'::text))::character varying])::text[])) 
Planning Time: 0.136 ms 
Execution Time: 2.843 ms 
(7 rows) 


If i replace the policy with stright forward without function then it chooses 
the index. Not sure how i can implement with the function. I thought of 
creating the policy with a lot of business logic in the function. If i have the 
function then i notice going for full table scan instead of index. 

Please help me if i miss anything in writing a function or how to use functions 
in the policy. 

Thank you. 


Regards, 
Ramesh G 


BQ_END


You could try seeting the function as immutable. By default it is volatile. 

Re: Performance Issue (Not using Index when joining two tables).

[Gopisetty, Ramesh]

mp_position as i 
observed here.

fyi.,

Running as a root user.

pgwfc01q=> explain analyze select 
cep.HOME_DEPT_OID,ctc.oid,ctc.category,ctc.code from .chr_emp_position cep 
inner join wfnsch001.chr_Simple_Val ctc on ctc.oid=cep.HOME_DEPT_OID and 
(ctc.vpd_key='COMMON' or ctc.vpd_key=cep.vpd_key) and cep.vpd_key='xx';

QUERY PLAN


---
 Hash Join  (cost=5503.95..6742.82 rows=453 width=42) (actual 
time=131.241..154.201 rows=228 loops=1)
   Hash Cond: ((cep.home_dept_oid)::text = (ctc.oid)::text)
   Join Filter: (((ctc.vpd_key)::text = 'NG_COMMON'::text) OR 
((ctc.vpd_key)::text = (cep.vpd_key)::text))
   Rows Removed by Join Filter: 19770
   ->  Bitmap Heap Scan on chr_emp_position cep  (cost=10.05..362.25 rows=228 
width=28) (actual time=0.056..0.253 ro
ws=228 loops=1)
 Recheck Cond: ((vpd_key)::text = 'xx'::text)
 Heap Blocks: exact=26
 ->  Bitmap Index Scan on uq1_chr_emp_position  (cost=0.00..9.99 
rows=228 width=0) (actual time=0.041..0.041
 rows=228 loops=1)
   Index Cond: ((vpd_key)::text = 'xx'::text)
   ->  Hash  (cost=3600.29..3600.29 rows=88929 width=48) (actual 
time=130.826..130.826 rows=88929 loops=1)
 Buckets: 65536 (originally 65536)  Batches: 4 (originally 2)  Memory 
Usage: 3585kB
 ->  Seq Scan on chr_simple_val ctc  (cost=0.00..3600.29 rows=88929 
width=48) (actual time=0.005..33.356 row
s=88929 loops=1)
 Planning Time: 3.977 ms
 Execution Time: 154.535 ms
(14 rows)

pgwfc01q=> select count(*) from wfnsch001.chr_emp_position;
 count
---
  3923
(1 row)

pgwfc01q=> select count(*) from wfnsch001.chr_Simple_Val;
 count
---
 88929
(1 row)



I'm not sure if i'm thinking in the right way or not. (As of safety purpose, i 
have  rebuilded indexes, analyzed, did vaccum on those tables).   Sorry for the 
lengthy email and i'm trying to explain my best on this.

Thank you.

Regards,
Ramesh G



From: Michael Lewis 
Sent: Sunday, September 13, 2020 10:51 PM
To: Tom Lane 
Cc: Tomas Vondra ; Gopisetty, Ramesh 
; pgsql-performance@lists.postgresql.org 

Subject: Re: Performance Issue (Not using Index when joining two tables).

Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND 
f_sel_policy_all(vpd_key, 'CH
R_EMP_POSITION'::character varying) AND 
f_sel_policy_prod_locale((cep.*)::character varying, prod_locale_code))

This looks like some stuff for row level security perhaps. My understanding is 
limited, but perhaps those restrictions are influencing the planners access or 
reliance on stats.

Also, it would seem like you need the entire table since you don't have an 
explicit where clause. Why would scanning an index and then also visiting every 
row in the table be faster than just going directly to the table?

Re: Performance Issue (Not using Index when joining two tables).

[Michael Lewis]

Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[]))
AND f_sel_policy_all(vpd_key, 'CH
R_EMP_POSITION'::character varying) AND
f_sel_policy_prod_locale((cep.*)::character
varying, prod_locale_code))

This looks like some stuff for row level security perhaps. My understanding
is limited, but perhaps those restrictions are influencing the planners
access or reliance on stats.

Also, it would seem like you need the entire table since you don't have an
explicit where clause. Why would scanning an index and then also visiting
every row in the table be faster than just going directly to the table?

Re: Performance Issue (Not using Index when joining two tables).

[Tom Lane]

Tomas Vondra  writes:
> Most of the time (3460ms) is spent in the sequential scan on
> chr_simple_val, and the seqscan on chr_emp_position is taking ~330ms).
> Combined that's 3790ms out of 3797ms, so the join is pretty much
> irrelevant.

> Either the seqscans are causing a lot of I/O, or maybe the f_sel_*
> functions in the filter are expensive. Judging by how few rows are in
> the tables (not sure how large the tables are), I'd guess it's the
> latter ... Hard to say without knowing what the functions do etc.

I think the OP is wishing that the filter functions for the larger table
would be postponed till after the join condition is applied.  I'm a
little dubious that that's going to save anything meaningful; but maybe
increasing the cost attributed to those functions would persuade the
planner to try it that way.

First though, does forcing a nestloop plan (turn off enable_hashjoin,
and enable_mergejoin too if needed) produce the shape of plan you
want?  And if so, is it actually faster?  Only if those things are
true is it going to be worth messing with costing parameters.

regards, tom lane

Re: Performance Issue (Not using Index when joining two tables).

[Tomas Vondra]

On Sun, Sep 13, 2020 at 02:58:15PM +, Gopisetty, Ramesh wrote:

Hi,

Good Morning!

Postgres Version :  11.6  (AWS Native Postgres/AWS Aurora  tried on both 
flavours).

When i'm joining two tables the primary index is not being used.  While is use  
in clause with values then the index is being used.  I have reindexed all the 
tables,  run the auto vaccum as well.


pgwfc01q=> select count(*) from chr_simple_val;
count
---
13158
(1 row)

pgwfc01q=> select count(*) from chr_emp_position;
count
---
  228
(1 row)


The primary key for the table chr_Simple_val  contains OID.   Still not using 
the index.

I'm sharing the explain plan over here..

pgwfc01q=> explain analyze select 
cep.HOME_DEPT_OID,ctc.oid,ctc.category,ctc.code from chr_emp_position cep inner 
join chr_Simple_Val ctc on ctc.oid=cep.HOME_DEPT_OID;

QUERY P
LAN


Hash Join  (cost=49299.91..51848.83 rows=651 width=42) (actual 
time=3512.692..3797.583 rows=228 loops=1)
  Hash Cond: ((cep.home_dept_oid)::text = (ctc.oid)::text)
  ->  Seq Scan on chr_emp_position cep  (cost=0.00..2437.77 rows=436 width=11) 
(actual time=44.713..329.435 rows=22
8 loops=1)
Filter: ((("current_user"())::text <> ANY 
('{wfnadmin,skipvpd}'::text[])) AND f_sel_policy_all(vpd_key, 'CH
R_EMP_POSITION'::character varying) AND 
f_sel_policy_prod_locale((cep.*)::character varying, prod_locale_code))
Rows Removed by Filter: 3695
  ->  Hash  (cost=49176.40..49176.40 rows=9881 width=31) (actual 
time=3467.907..3467.908 rows=13158 loops=1)
Buckets: 16384  Batches: 1  Memory Usage: 1031kB
->  Seq Scan on chr_simple_val ctc  (cost=0.00..49176.40 rows=9881 
width=31) (actual time=2.191..3460.929 r
ows=13158 loops=1)
  Filter: ((("current_user"())::text <> ANY 
('{wfnadmin,skipvpd}'::text[])) AND f_sel_policy_ty_static(
vpd_key) AND f_sel_policy_prod_locale((ctc.*)::character varying, 
prod_locale_code))
  Rows Removed by Filter: 75771
Planning Time: 0.297 ms
Execution Time: 3797.768 ms
(12 rows)



Most of the time (3460ms) is spent in the sequential scan on
chr_simple_val, and the seqscan on chr_emp_position is taking ~330ms).
Combined that's 3790ms out of 3797ms, so the join is pretty much
irrelevant.

Either the seqscans are causing a lot of I/O, or maybe the f_sel_*
functions in the filter are expensive. Judging by how few rows are in
the tables (not sure how large the tables are), I'd guess it's the
latter ... Hard to say without knowing what the functions do etc.

regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Performance issue

[Justin Pryzby]

On Sun, Jun 14, 2020 at 10:45:52PM +, Nagaraj Raj wrote:
> My PostgreSQL server 10.11 running on windows which are running very slow. DB 
> has two tables with ~200Mil records in each. user queries are very slow even 
> explain analyze also taking a longer.
> 
> Could you please help me to tune this query and any suggestions to improve 
> system performance?

> CREATE TABLE test1
> (
> individual_entity_proxy_id bigint NOT NULL,
...
> CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
> 
> );
> CREATE INDEX indx_prospect_indv_entty_id ON test1 USING btree 
> (individual_entity_proxy_id )

This index is redundant with the primary key, which implicitly creates a unique
index.

The table structure seems strange: you have two tables with the same PK column,
which is how they're being joined.  It seems like that's better expressed as a
single table with all the columns rather than separate tables (but see below).

> explain analyze select COUNT(*) as "DII_1"
>   from ( select distinct table0."individual_entity_proxy_id" as 
> "INDIVIDUAL_ENTITY_PROXY_ID"
> from test1 table0
> inner join test2 table1

I think this may be better written as something like:

| SELECT COUNT(id) FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.id=t0.id AND 
...) AND ...

It's guaranteed to be distinct since it's a PK column, so it doesn't need a
"Unique" node.

I think it might prefer an seq scan on t0, which might be good since it seems
to be returning over 10% of records.

> Explain Analyze :
> 
> "Aggregate  (cost=5345632.91..5345632.92 rows=1 width=8) (actual 
> time=442688.462..442688.462 rows=1 loops=1)"
> "  ->  Unique  (cost=150.13..4943749.39 rows=32150682 width=8) (actual 
> time=0.022..439964.214 rows=32368180 loops=1)"
> "->  Merge Join  (cost=150.13..4863372.68 rows=32150682 width=8) 
> (actual time=0.021..435818.276 rows=32368180 loops=1)"
> "  Merge Cond: (table0.individual_entity_proxy_id = 
> table1.individual_entity_proxy_id)"
> "  ->  Index Scan using indx_prospect_indv_entty_id on test1 
> table0  (cost=0.56..2493461.92 rows=32233405 width=8) (actual 
> time=0.011..63009.551 rows=32368180 loops=1)"
> "Filter: ((direct_mail_preference IS NULL) AND 
> ((last_contacted_dm_datetime IS NULL) OR (last_contacted_dm_datetime < 
> '2020-03-15 00:00:00'::timestamp without time zone)) AND 
> (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 
> 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND 
> (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND 
> (has_first_name = 'Y'::bpchar))"
> "Rows Removed by Filter: 7709177"
> "  ->  Index Scan using pk_entity_proxy_id on test2 table1  
> (cost=0.56..1867677.94 rows=40071417 width=8) (actual time=0.008..363534.437 
> rows=40077727 loops=1)"
> "Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND 
> (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 
> 'Prospect'::text))"
> "Rows Removed by Filter: 94756"

It might help to show explain(ANALYZE,BUFFERS).

It looks like test2/table1 index scan is a lot slower than table0.
Maybe table1 gets lots of updates, so isn't clustered on its primary key, so
the index scan is highly random.  You could check the "correlation" of its PK
ID column:
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram

If true, that would be a good reason to have separate tables.

> vCore: 32

Possibly it would be advantageous to use parallel query.
A better query+plan might allow that.

-- 
Justin

Re: Performance issue

[David Rowley]

On Mon, 15 Jun 2020 at 10:46, Nagaraj Raj  wrote:
> CREATE TABLE test1
> (
...

> CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
>
> );

> CREATE TABLE test2
> (
...

> CONSTRAINT pk_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
> );
>
>
> User query:
>
> explain analyze select COUNT(*) as "DII_1"
>   from ( select distinct table0."individual_entity_proxy_id" as 
> "INDIVIDUAL_ENTITY_PROXY_ID"
> from test1 table0
> inner join test2 table1
> on table0."individual_entity_proxy_id" = 
> table1."individual_entity_proxy_id"

Why do you use "select distinct". It seems to me that you're putting a
distinct clause on the primary key of test1 and joining to another
table in a way that cannot cause duplicates.

I imagine dropping that distinct will speed up the query quite a bit.

David

Re: Performance tunning

[Jeff Janes]

On Sat, May 30, 2020 at 3:37 AM sugnathi hai  wrote:

> Hi ,
>
> Can you help to tune the below plan
>


It looks like your query (which you should show us) has something like

  ORDER BY modifieddate LIMIT 100

It thinks it can walk the index in order, then stop once it collects 100
qualifying rows.  But since almost all rows are removed by the join
conditions, it ends up walking a large chunk of the index before finding
100 of them which qualify.

You could try forcing it out of this plan by doing:

  ORDER BY modifieddate + interval '0 second' LIMIT 100

 Cheers,

Jeff

Re: Performance tunning

[Justin Pryzby]

On Sat, May 30, 2020 at 09:43:43AM +0200, Pavel Stehule wrote:
> so 30. 5. 2020 v 9:37 odesílatel sugnathi hai  napsal:
> > Can you help to tune the below plan

Could you also send it so line breaks aren't lost, as seen here:
https://www.postgresql.org/message-id/975278223.51863.1590824209351%40mail.yahoo.com

Probably best to send a link to the plan at https://explain.depesz.com/

https://wiki.postgresql.org/wiki/Slow_Query_Questions
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

-- 
Justin

Re: Performance tunning

[Pavel Stehule]

Hi

so 30. 5. 2020 v 9:37 odesílatel sugnathi hai  napsal:

> Hi ,
>
> Can you help to tune the below plan
>
> Limit (cost=0.87..336777.92 rows=100 width=57) (actual
> time=599302.173..599481.552 rows=100 loops=1) Buffers: shared hit=78496066
> -> Nested Loop (cost=0.87..11005874.67 rows=3268 width=57) (actual
> time=599302.170..599481.506 rows=100 loops=1) Buffers: shared hit=78496066
> -> Index Scan using inx_callprocessingstatus_modifieddate on
> callprocessingstatus contactsta1_ (cost=0.44..2008486.89 rows=15673696
> width=16) (actual time=0.356..66774.105 rows=15651059 loops=1) Index Cond:
> (modifieddate < now()) Filter: ((overallstatus)::text = 'COMPLETED'::text)
> Rows Removed by Filter: 275880 Buffers: shared hit=15803632 -> Index Scan
> using "INX_callinfo_Callid" on callinfo contact0_ (cost=0.43..0.57 rows=1
> width=49) (actual time=0.033..0.033 rows=0 loops=15651059) Index Cond:
> (callid = contactsta1_.callid) Filter: ((combinationkey IS NULL) AND
> (mod(callid, '2'::bigint) = 0)) Rows Removed by Filter: 1 Buffers: shared
> hit=62692434 Planning Time: 1.039 ms Execution Time: 599481.758 ms
>

Can you show a query related to this plan?

Re: performance degredation after upgrade from 9.6 to 12

[Pavel Stehule]

po 16. 12. 2019 v 14:02 odesílatel Mariel Cherkassky <
mariel.cherkas...@gmail.com> napsal:

> I see, thank u !
> Maybe I didnt see big difference because most of my tables arent so big.
> My db`s size is 17GB and the largest table contains about 20M+ records.
>

Postgres 12 has enabled JIT by default.

Pavel


> Thanks again !
>

Re: performance degredation after upgrade from 9.6 to 12

[Mariel Cherkassky]

I see, thank u !
Maybe I didnt see big difference because most of my tables arent so big. My
db`s size is 17GB and the largest table contains about 20M+ records.

Thanks again !

RE: performance degredation after upgrade from 9.6 to 12

[Andrew Zakharov]

Hi there –

I have no idea why this happening. But I suspect the parallel requires more 
internal machine resources like CPU etc because you can faster retrieve the 
disk data from the one hand but you ought to spend more resources for 
maintaining several threads and theirs coordination (px coordinator process in 
Oracle terms) from another one. Thus there could be more serious hardware 
requirements even just to keep performance the same. I believe that the real 
benefit of the parallel will be shown when you have pair of large and wide 
tables (30M or more each) with hash join (typical task for mart construction) 
but such class of databases is supposed to be big and required enough resources 
initially.

 

 

From: Mariel Cherkassky  
Sent: Monday, December 16, 2019 2:48 PM
To: Jeff Janes 
Cc: Andrew Zakharov ; pgsql-performance@lists.postgresql.org
Subject: Re: performance degredation after upgrade from 9.6 to 12

 

Hey Jeff,Andrew,

I continued testing the 12version vs the 96 version and it seems that there is 
almost non diff and in some cases pg96 is faster than 12. I compared the 
content of pg_stat_statements after each test that I have done and it seems 
that the db time is almost the same and sometimes 96 is faster by 5%.

 

Any idea why there isnt any improvement even when I enabled the parallel params 
in 12 ?

I can add a few examples if needed..

Re: performance degredation after upgrade from 9.6 to 12

[Mariel Cherkassky]

Hey Jeff,Andrew,
I continued testing the 12version vs the 96 version and it seems that there
is almost non diff and in some cases pg96 is faster than 12. I compared the
content of pg_stat_statements after each test that I have done and it seems
that the db time is almost the same and sometimes 96 is faster by 5%.

Any idea why there isnt any improvement even when I enabled the parallel
params in 12 ?
I can add a few examples if needed..

>

Re: performance degredation after upgrade from 9.6 to 12

[Jeff Janes]

On Sun, Nov 24, 2019 at 1:05 PM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:

> Hey Jeff,
> This example was only used to show that pg96  had better perfomance than
> pg12 in a very simple case.
>

OK, but do you agree that a 15% slow down is more realistic than 3 fold
one?  Or are you still getting 3 fold slow down with more careful testing
and over a wide variety of queries?

I find that the main regression (about 15%) in your example occurs in major
version 10, at the following commit:

commit b8d7f053c5c2bf2a7e8734fe3327f6a8bc711755
Author: Andres Freund 
Date:   Tue Mar 14 15:45:36 2017 -0700

Faster expression evaluation and targetlist projection.

It is disappointing that this made this case slower rather than faster, and
that the "future work" alluded to either hasn't happened, or wasn't
effective for this example.  I also tested the same example, only 100 times
more rows, and still see the regression at about 16%.  This is a major
infrastructure change patch which has been extensively built on since then,
the chances of reverting it are very small.  It is making an omelette, and
your example is one of the eggs that got broken.

Performance changes in a large body of queries are usually not all due to
the same thing.  Are you a position to custom compile your own PostgreSQL?
It would be nice to test this commit against the one before it, and see how
much of the change in your real queries is explained by this one thing (or
whether any of it is)


>  In all the tests that I run most of the queries took less time on 9.6`s
> version.  I dont know why, but as you can see after disabling the parameter
> the simple test that I did showed different results.
>

I can't see--You didn't post results for that.  And running your test on my
own system doesn't show that at all.  In your test case,
max_parallel_workers_per_gather makes no difference.  With 100 times more
rows, setting it to 0 actually slows things down, as at that size
parallelization is useful and disabling it hurts.

Of course parallel query might be hurting some of the other queries, but
for the one example you show you will have to show something more
convincing for me to believe that that is what caused it.

It is easy to benchmark with something like:

PGOPTIONS="-c max_parallel_workers_per_gather=0" pgbench -T30 -f <(echo
"select count(*) from test1") -p 9912 -n -M prepared

If it is other queries where mpwpg is making a difference, than one issue
could be that your settings of parallel_setup_cost and/or
parllel_tuple_cost are too low (although I usually find the default
settings too high, not too low); or you are running your test concurrently
already and so don't need parallel query to fully load the CPUs and trying
to use parallel query just increases the overhead; or your machine doesn't
have the number of truly effective CPUs you think it does.

Cheers,

Jeff

>

Re: performance degredation after upgrade from 9.6 to 12

[John Felix]

Op 24-11-2019 om 19:05 schreef Mariel Cherkassky:

Hey Jeff,
This example was only used to show that pg96  had better perfomance 
than pg12 in a very simple case.
 In all the tests that I run most of the queries took less time on 
9.6`s version.  I dont know why, but as you can see after 
disabling the parameter the simple test that I did showed different 
results.  I intend to test this theory tomorrow. I'm going to disable 
the parameter and run the same application flows that I have on both 
machines (9.6 vs 12 with zero value for the param).


I didnt send this mail after doing just one simple test, I have more 
than 100 queries that work better on 9.6 . If u have any explanation I 
will be happy to hear.

I'll update tomorrow once I'll have the results..

I've had the same experience with parallel query. By default parallel 
query is disabled in 9.6. When we upgraded from 9.6 to 10 is was 
significant slower till I disabled parallel query.


We have a very small database (40 tables and all together max 4GB) and 
we have no long running queries (the largest queries run max 2 to 3 
seconds). That's when parallel query gives a performance degrade in my 
opinion.


Best regards,

John Felix

Re: performance degredation after upgrade from 9.6 to 12

[Jeff Janes]

On Sun, Nov 24, 2019 at 8:52 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:

> Hey Andrew,
> It seems that changing this parameter worked for me.
> Setting it to zero means that there wont be any parallel workers for one
> query right ?
> Is it something familiar this problem with the gatherers ?
>

Your example would not be using parallel workers anyway, regardless of the
setting of max_parallel_workers_per_gather, so I don't see how changing
this could have worked for you.  Unless you mean it worked in your full
test, rather than in your test case. I doubt your test case benchmarking
was very reliable to start with, you only show a single execution and
didn't indicate you had more unshown ones.

If I do more credible benchmarking, I do get a performance regression but
it closer is to 16% than to 3 fold.  And it doesn't depend on the setting
of max_parallel_workers_per_gather.  I doubt a regression of this size is
even worth investigating.

pgbench -T300 -P5 -f <(echo "select count(*) from test1") -p 9912 -n -M
prepared

Cheers,

Jeff

Re: performance degredation after upgrade from 9.6 to 12

[Mariel Cherkassky]

Hey Andrew,
It seems that changing this parameter worked for me.
Setting it to zero means that there wont be any parallel workers for one
query right ?
Is it something familiar this problem with the gatherers ?

Re: performance degredation after upgrade from 9.6 to 12

[Andrew Zakharov]

Hi there - 
I have same feelings. Try set max_parallel_workers_per_gather to zero. I don't 
think that comparison non-parallel and parallel versions is correct (don't say 
anything about parallel in 9.6 pls) 
What explain says? I suppose you will have different exec plans. Optimizer 
stranges of 11 and 12 ver have been discussed. Look thru the archive, but I 
didn't remember the problem status - resolved or not.
Andrew. 

24 ноября 2019 г. 15:53:19 GMT+03:00, Mariel Cherkassky 
 пишет:
>Hey all,
>I'm testing performance of two identical machines one in 9.6 and the
>second
>one is in 12. The second machine is a clone of the first one + db
>upgrade
>to 12 beta 3 (Yes I'm aware 12.1 was released).
>
>machine stats :
>32gb ram
>8 cpu
>regular hd (not ssd)
>
>my postgresql.confg settings:
>
>max_wal_size = 2GB
>min_wal_size = 1GB
>wal_buffers = 16MB
>checkpoint_completion_target = 0.9
>checkpoint_timeout = 30min
>log_checkpoints = on
>log_lock_waits = on
>log_temp_files = 1024
>log_min_duration_statement = 1000
>log_autovacuum_min_duration = 5000
>autovacuum_max_workers = 4
>autovacuum_vacuum_cost_limit = 800
>autovacuum_vacuum_cost_delay = 10ms
>standard_conforming_strings = off
>max_locks_per_transaction = 5000
>max_connections = 500
>log_line_prefix = '%t %d %p  '
>random_page_cost = 2.0
>deadlock_timeout = 5s
>shared_preload_libraries = 'pg_stat_statements'
>track_activity_query_size = 32764
>maintenance_work_mem = 250MB
>work_mem = 32MB
>shared_buffers = 8058MB
>effective_cache_size = 16116MB
>
>in 12v I also added the following settings :
>log_directory = 'pg_log'
>enable_partitionwise_join = on
>enable_partitionwise_aggregate = on
>max_worker_processes = 8   # (change requires restart)
>max_parallel_workers_per_gather = 4# taken from
>max_parallel_workers
>max_parallel_workers = 8   # maximum number of
>max_worker_pr
>
>I tested a few applications flows and I saw that the 9.6 version is
>faster.
>I also did a few simple tests (enabled \timing) :
>
>12v :
>postgres=# create table test1 as select generate_series(1,1);
>SELECT 1
>Time: 35.099 ms
>
>postgres=# select count(*) from test1;
> count
>---
> 1
>(1 row)
>
>Time: 4.819 ms
>
>9.6v :
>postgres=# create table test1 as select generate_series(1,1);
>SELECT 1
>Time: 19.962 ms
>
>postgres=# select count(*) from test1;
> count
>---
> 1
>(1 row)
>
>Time: 1.541 ms
>
>Any idea what can cause it ? What can I check?
>This degredation is visible in many queries that we use ..
>
>After the upgrade to 12v version I run analyze on all tables..
>
>Thanks.

--
С уважением,
Андрей Захаров

Re: performance degredation after upgrade from 9.6 to 12

[Thomas Poty]

Hello,
did you run ananlyze on your db?

Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky 
a écrit :

> Hey all,
> I'm testing performance of two identical machines one in 9.6 and the
> second one is in 12. The second machine is a clone of the first one + db
> upgrade to 12 beta 3 (Yes I'm aware 12.1 was released).
>
> machine stats :
> 32gb ram
> 8 cpu
> regular hd (not ssd)
>
> my postgresql.confg settings:
>
> max_wal_size = 2GB
> min_wal_size = 1GB
> wal_buffers = 16MB
> checkpoint_completion_target = 0.9
> checkpoint_timeout = 30min
> log_checkpoints = on
> log_lock_waits = on
> log_temp_files = 1024
> log_min_duration_statement = 1000
> log_autovacuum_min_duration = 5000
> autovacuum_max_workers = 4
> autovacuum_vacuum_cost_limit = 800
> autovacuum_vacuum_cost_delay = 10ms
> standard_conforming_strings = off
> max_locks_per_transaction = 5000
> max_connections = 500
> log_line_prefix = '%t %d %p  '
> random_page_cost = 2.0
> deadlock_timeout = 5s
> shared_preload_libraries = 'pg_stat_statements'
> track_activity_query_size = 32764
> maintenance_work_mem = 250MB
> work_mem = 32MB
> shared_buffers = 8058MB
> effective_cache_size = 16116MB
>
> in 12v I also added the following settings :
> log_directory = 'pg_log'
> enable_partitionwise_join = on
> enable_partitionwise_aggregate = on
> max_worker_processes = 8   # (change requires restart)
> max_parallel_workers_per_gather = 4# taken from max_parallel_workers
> max_parallel_workers = 8   # maximum number of max_worker_pr
>
> I tested a few applications flows and I saw that the 9.6 version is
> faster. I also did a few simple tests (enabled \timing) :
>
> 12v :
> postgres=# create table test1 as select generate_series(1,1);
> SELECT 1
> Time: 35.099 ms
>
> postgres=# select count(*) from test1;
>  count
> ---
>  1
> (1 row)
>
> Time: 4.819 ms
>
> 9.6v :
> postgres=# create table test1 as select generate_series(1,1);
> SELECT 1
> Time: 19.962 ms
>
> postgres=# select count(*) from test1;
>  count
> ---
>  1
> (1 row)
>
> Time: 1.541 ms
>
> Any idea what can cause it ? What can I check?
> This degredation is visible in many queries that we use ..
>
> After the upgrade to 12v version I run analyze on all tables..
>
> Thanks.
>

Re: Re: performance bottlenecks on lock transactionid

[Laurenz Albe]

王若楠 wrote:
> I want to find a way to reduce the lock waiting and improve the
> performance.

You either have to make the transactions shorter, or you let the
different clients modify different rows, so that they don't lock each
other.

That concurrent writers on the same data lock each other is
unavoidable, and all database management systems I know do it the same
way.

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

Re: performance bottlenecks on lock transactionid

[Laurenz Albe]

王若楠 wrote:
> We used benchmarksql 4.1.0 to test the performance of PG12 beta TPCC.
> We found performance bottlenecks on lock transactionid.

You included an attachment with results from the "pg_locks" view
where "granted" is FALSE for all entries.

I'll assume that these are not *all* the entries in the view, right?

Since the locks are waiting for different transaction IDs, I'd
assume that this is just a case of contention: many transactions are
trying to modify the same rows concurrently.

This is to be expected.
Perhaps your benchmark is running with too many connections on
too few table rows?

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

Re: Performance regressions found using sqlfuzz

[Jung, Jinho]

Hi Andres:

Could you please share your thoughts on QUERY 3?

The performance impact of this regression increases *linearly* on larger 
databases. We concur with Andrew in that this is related to the lack of a 
Materialize node and mis-costing of the Nested Loop Anti-Join.

We found more than 20 regressions related to this commit. We have shared two 
illustrative examples (QUERIES 3A and 3B) below.

- Commit: 77cd477 (Enable parallel query by default.)

- Summary: Execution Time (milliseconds)

When we increased the scale-factor of TPC-C to 300 (~30 GB), this query ran 
three times slower on v11 (24 seconds) in comparison to v9.5 (7 seconds). We 
also found more than 15 regressions related to the same commit and share a 
couple of them below.

+---++-+-+---+
|   | scale1 | scale10 | scale50 | scale 300 |
+---++-+-+---+
| Query 3 (v9.5)| 28 | 248 |1231 |  7265 |
| Query 3 (v11) | 74 | 677 |3345 | 24581 |
+---++-+-+---+
| Query 3A (v9.5)   | 88 | 937 |4721 | 27241 |
| Query 3A (v11)|288 |2822 |   13838 | 85081 |
+---++-+-+---+
| Query 3B (v9.5)   |101 | 934 |4824 | 29363 |
| Query 3B (v11)|200 |2331 |   12327 | 74110 |
+---++-+-+---+


## QUERY 3:

select
 cast(ref_1.ol_i_id as int4) as c0
from
 public.stock as ref_0
   left join public.order_line as ref_1
   on (ref_1.ol_number is not null)
where ref_1.ol_number is null


## QUERY 3A:

select
  ref_0.ol_delivery_d as c1
from
  public.order_line as ref_0
where EXISTS (
  select
ref_1.i_im_id as c0
from
public.item as ref_1
where ref_0.ol_d_id <=  ref_1.i_im_id
  )

 Execution plan:

[OLD version]
Nested Loop Semi Join  (cost=0.00..90020417940.08 rows=30005835 width=8) 
(actual time=0.034..24981.895 rows=90017507 loops=1)
  Join Filter: (ref_0.ol_d_id <= ref_1.i_im_id)
  ->  Seq Scan on order_line ref_0  (cost=0.00..2011503.04 rows=90017504 
width=12) (actual time=0.022..7145.811 rows=90017507 loops=1)
  ->  Materialize  (cost=0.00..2771.00 rows=10 width=4) (actual 
time=0.000..0.000 rows=1 loops=90017507)
->  Seq Scan on item ref_1  (cost=0.00..2271.00 rows=10 width=4) 
(actual time=0.006..0.006 rows=1 loops=1)

Planning time: 0.290 ms
Execution time: 27241.239 ms

[NEW version]
Gather  (cost=1000.00..88047487498.82 rows=30005835 width=8) (actual 
time=0.265..82355.289 rows=90017507 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop Semi Join  (cost=0.00..88044485915.32 rows=12502431 width=8) 
(actual time=0.033..68529.259 rows=30005836 loops=3)
Join Filter: (ref_0.ol_d_id <= ref_1.i_im_id)
->  Parallel Seq Scan on order_line ref_0  (cost=0.00..1486400.93 
rows=37507293 width=12) (actual time=0.023..2789.901 rows=30005836 loops=3)
->  Seq Scan on item ref_1  (cost=0.00..2271.00 rows=10 width=4) 
(actual time=0.001..0.001 rows=1 loops=90017507)

Planning Time: 0.319 ms
Execution Time: 85081.158 ms


## QUERY 3B:


select
  ref_0.ol_i_id as c0
from
  public.order_line as ref_0
where EXISTS (
  select
ref_0.ol_delivery_d as c0
from
public.order_line as ref_1
where ref_1.ol_d_id <= cast(nullif(ref_1.ol_o_id, ref_0.ol_i_id) as int4))

Execution plan:

[OLD version]
Nested Loop Semi Join  (cost=0.00..115638730740936.53 rows=30005835 width=4) 
(actual time=0.017..27009.302 rows=90017507 loops=1)
  Join Filter: (ref_1.ol_d_id <= NULLIF(ref_1.ol_o_id, ref_0.ol_i_id))
  Rows Removed by Join Filter: 11557
  ->  Seq Scan on order_line ref_0  (cost=0.00..2011503.04 rows=90017504 
width=4) (actual time=0.009..7199.540 rows=90017507 loops=1)
  ->  Materialize  (cost=0.00..2813221.56 rows=90017504 width=8) (actual 
time=0.000..0.000 rows=1 loops=90017507)
->  Seq Scan on order_line ref_1  (cost=0.00..2011503.04 rows=90017504 
width=8) (actual time=0.001..0.002 rows=14 loops=1)

Planning time: 0.252 ms
Execution time: 29363.737 ms

[NEW version]
Gather  (cost=1000.00..84060490326155.39 rows=30005835 width=4) (actual 
time=0.272..71712.491 rows=90017507 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop Semi Join  (cost=0.00..84060487324571.89 rows=12502431 
width=4) (actual time=0.046..60153.472 rows=30005836 loops=3)
Join Filter: (ref_1.ol_d_id <= NULLIF(ref_1.ol_o_id, ref_0.ol_i_id))
Rows Removed by Join Filter: 1717
->  Parallel Seq Scan on order_line ref_0  (cost=0.00..1486400.93 
rows=37507293 width=4) (actual time=0.023..2819.361 rows=30005836 loops=3)
->  Seq Scan on order_line ref_1  (cost=0.00..2011503.04 rows=90017504 
width=8) (actual time=0.001..0.001 rows=1 loops=90017507)

Re: Performance regressions found using sqlfuzz

[Jung, Jinho]

eq Scan on order_line ref_0  (cost=0.00..1486400.93 
rows=37507293 width=4) (actual time=0.023..2819.361 rows=30005836 loops=3)
->  Seq Scan on order_line ref_1  (cost=0.00..2011503.04 rows=90017504 
width=8) (actual time=0.001..0.001 rows=1 loops=90017507)
Planning Time: 0.334 ms
Execution Time: 74110.942 ms

### QUERY 4:

As Tom pointed out, this regression is not present in DEV head. We will not 
report regressions related to NULLIF in the future.

### QUERY 1:

This regression is also not present in DEV head. We will validate our 
regressions on DEV head in the future report.

Best,

Jinho Jung



From: Tom Lane 
Sent: Saturday, February 16, 2019 5:37:49 PM
To: Andres Freund
Cc: Jung, Jinho; Jeff Janes; pgsql-performa...@postgresql.org
Subject: Re: Performance regressions found using sqlfuzz

Andres Freund  writes:
> On 2019-02-14 17:27:40 +, Jung, Jinho wrote:
>> - Our analysis: We believe that this regression has to do with two factors: 
>> 1) conditional expression (e.g., LEAST or NULLIF) are not reduced to 
>> constants unlike string functions (e.g., CHAR_LENGTH)  2) change in the cost 
>> estimation function for bitmap scan. Execution time grows by 3 orders of 
>> magnitude.  We note that this regression is only observed on large databases 
>> (e.g., scale factor of 50).

> Hm. The primary problem here is that the estimation both before and
> after are really bad. So I don't think the commit you point out here is
> really to blame.  I'm not that bothered by the query not being great,
> given the weird construction with LEAST(), but we probably could fix
> that pretty easily.

We already did:

Author: Tom Lane 
Branch: master [6f19a8c41] 2018-12-30 13:42:04 -0500

Teach eval_const_expressions to constant-fold LEAST/GREATEST expressions.

Doing this requires an assumption that the invoked btree comparison
function is immutable.  We could check that explicitly, but in other
places such as contain_mutable_functions we just assume that it's true,
so we may as well do likewise here.  (If the comparison function's
behavior isn't immutable, the sort order in indexes built with it would
be unstable, so it seems certainly wrong for it not to be so.)

Vik Fearing

Discussion: 
https://postgr.es/m/c6e8504c-4c43-35fa-6c8f-3c0b80a91...@2ndquadrant.com

BTW, const-folding NULLIF would not be a similarly tiny fix, because
it would need to check for immutability of the underlying operator
(since it is possibly a cross-type comparison, we can't get
away with just assuming it's immutable).  I'm not convinced that
case is worth carrying extra code for.

regards, tom lane

Re: Performance regressions found using sqlfuzz

[Tom Lane]

Andres Freund  writes:
> On 2019-02-14 17:27:40 +, Jung, Jinho wrote:
>> - Our analysis: We believe that this regression has to do with two factors: 
>> 1) conditional expression (e.g., LEAST or NULLIF) are not reduced to 
>> constants unlike string functions (e.g., CHAR_LENGTH)  2) change in the cost 
>> estimation function for bitmap scan. Execution time grows by 3 orders of 
>> magnitude.  We note that this regression is only observed on large databases 
>> (e.g., scale factor of 50).

> Hm. The primary problem here is that the estimation both before and
> after are really bad. So I don't think the commit you point out here is
> really to blame.  I'm not that bothered by the query not being great,
> given the weird construction with LEAST(), but we probably could fix
> that pretty easily.

We already did:

Author: Tom Lane 
Branch: master [6f19a8c41] 2018-12-30 13:42:04 -0500

Teach eval_const_expressions to constant-fold LEAST/GREATEST expressions.

Doing this requires an assumption that the invoked btree comparison
function is immutable.  We could check that explicitly, but in other
places such as contain_mutable_functions we just assume that it's true,
so we may as well do likewise here.  (If the comparison function's
behavior isn't immutable, the sort order in indexes built with it would
be unstable, so it seems certainly wrong for it not to be so.)

Vik Fearing

Discussion: 
https://postgr.es/m/c6e8504c-4c43-35fa-6c8f-3c0b80a91...@2ndquadrant.com

BTW, const-folding NULLIF would not be a similarly tiny fix, because
it would need to check for immutability of the underlying operator
(since it is possibly a cross-type comparison, we can't get
away with just assuming it's immutable).  I'm not convinced that
case is worth carrying extra code for.

regards, tom lane

Re: Performance regressions found using sqlfuzz

[Andres Freund]

Hi,

On 2019-02-14 17:27:40 +, Jung, Jinho wrote:
> ### QUERY 2:
> 
>   select distinct
> ref_0.i_im_id as c0,
> ref_1.ol_dist_info as c1
>   from
>   public.item as ref_0 right join
>   public.order_line as ref_1
>   on (ref_0.i_id = 5)
> 
> - Commit: 84f9a35 (Improve estimate of distinct values in 
> estimate_num_groups())
> 
> - Our analysis: We believe that this regression is related to the new logic 
> for estimating the number of distinct values in the optimizer. This is 
> affecting even queries with point lookups (ref_0.i_id = 5) in the TPC-C 
> benchmark.
> 
> - Query Execution Plans
> 
>   [OLD version]
>   HashAggregate  (cost=11972.38..12266.20 rows=29382 width=29) (actual 
> time=233.543..324.973 rows=300144 loops=1)
> Group Key: ref_0.i_im_id, ref_1.ol_dist_info
> ->  Nested Loop Left Join  (cost=0.29..10471.64 rows=300148 width=29) 
> (actual time=0.012..114.955 rows=300148 loops=1)
>   ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
> width=25) (actual time=0.004..25.061 rows=300148 loops=1)
>   ->  Materialize  (cost=0.29..8.32 rows=1 width=4) (actual 
> time=0.000..0.000 rows=1 loops=300148)
> ->  Index Scan using item_pkey on item ref_0  
> (cost=0.29..8.31 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
>   Index Cond: (i_id = 10)
>   Planning time: 0.267 ms
>   Execution time: 338.027 ms
> 
> 
>   [NEW version]
>   Unique  (cost=44960.08..47211.19 rows=300148 width=29) (actual 
> time=646.545..885.502 rows=300144 loops=1)
> ->  Sort  (cost=44960.08..45710.45 rows=300148 width=29) (actual 
> time=646.544..838.933 rows=300148 loops=1)
>   Sort Key: ref_0.i_im_id, ref_1.ol_dist_info
>   Sort Method: external merge  Disk: 11480kB
>   ->  Nested Loop Left Join  (cost=0.29..10471.64 rows=300148 
> width=29) (actual time=0.016..111.889 rows=300148 loops=1)
> ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 
> rows=300148 width=25) (actual time=0.004..24.612 rows=300148 loops=1)
> ->  Materialize  (cost=0.29..8.32 rows=1 width=4) (actual 
> time=0.000..0.000 rows=1 loops=300148)
>   ->  Index Scan using item_pkey on item ref_0  
> (cost=0.29..8.31 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)
> Index Cond: (i_id = 10)
>   Planning Time: 0.341 ms
>   Execution Time: 896.662 ms

This seems perfectly alright - the old version used more memory than
work_mem actually should have allowed. I'd bet you get the performance
back if you set work mem to a bigger value.


> ### QUERY 3:
> 
>   select
> cast(ref_1.ol_i_id as int4) as c0
>   from
> public.stock as ref_0
>   left join public.order_line as ref_1
>   on (ref_1.ol_number is not null)
>   where ref_1.ol_number is null
> 
> - Commit: 77cd477 (Enable parallel query by default.)
> 
> - Our analysis: We believe that this regression is due to parallel queries 
> being enabled by default. Surprisingly, we found that even on a larger TPC-C 
> database (scale factor of 50, roughly 4GB), parallel scan is still slower 
> than the non-parallel one in the old version, when the query is not returning 
> any tuples.
> 
> - Query Execution Plans
> 
>   [OLD version]
>   Nested Loop Anti Join  (cost=0.00..18006.81 rows=1 width=4) (actual 
> time=28.689..28.689 rows=0 loops=1)
> ->  Seq Scan on stock ref_0  (cost=0.00..5340.00 rows=10 width=0) 
> (actual time=0.028..15.722 rows=10 loops=1)
> ->  Materialize  (cost=0.00..9385.22 rows=300148 width=4) (actual 
> time=0.000..0.000 rows=1 loops=10)
>   ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
> width=4) (actual time=0.004..0.004 rows=1 loops=1)
> Filter: (ol_number IS NOT NULL)
>   Planning time: 0.198 ms
>   Execution time: 28.745 ms
> 
>   [NEW version]
>   Gather  (cost=1000.00..15164.93 rows=1 width=4) (actual time=91.022..92.634 
> rows=0 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> ->  Nested Loop Anti Join  (cost=0.00..14164.83 rows=1 width=4) (actual 
> time=88.889..88.889 rows=0 loops=3)
>   ->  Parallel Seq Scan on stock ref_0  (cost=0.00..4756.67 
> rows=41667 width=0) (actual time=0.025..7.331 rows=3 loops=3)
>   ->  Seq Scan on order_line ref_1  (cost=0.00..6711.48 rows=300148 
> width=4) (actual time=0.002..0.002 rows=1 loops=10)
> Filter: (ol_number IS NOT NULL)
>   Planning Time: 0.258 ms
>   Execution Time: 92.699 ms

I'm not particularly bothered - this is a pretty small difference. Most
of the time here is likely spent starting the workers, the cost of which
is hard to predict/model accurately.


> ### QUERY 4:
> 
>   select
> ref_0.s_dist_06 as c0
>   from
> public.stock as ref_0
>   where (ref_0.s_w_id < cast(least(0, 1) as int8))
> 
> - Commit: 5edc63b (Account for the effect of lossy pages when 

Re: Performance regressions found using sqlfuzz

[Jung, Jinho]

ps=10)
Filter: (ol_number IS NOT NULL)
  Planning Time: 0.258 ms
  Execution Time: 92.699 ms


### QUERY 4:

  select
ref_0.s_dist_06 as c0
  from
public.stock as ref_0
  where (ref_0.s_w_id < cast(least(0, 1) as int8))

- Commit: 5edc63b (Account for the effect of lossy pages when costing bitmap 
scans)

- Our analysis: We believe that this regression has to do with two factors: 1) 
conditional expression (e.g., LEAST or NULLIF) are not reduced to constants 
unlike string functions (e.g., CHAR_LENGTH)  2) change in the cost estimation 
function for bitmap scan. Execution time grows by 3 orders of magnitude.  We 
note that this regression is only observed on large databases (e.g., scale 
factor of 50).

- Query Execution Plans

  [OLD version]
  Bitmap Heap Scan on stock ref_0  (cost=31201.11..273173.13 rows=168 
width=25) (actual time=0.005..0.005 rows=0 loops=1)
Recheck Cond: (s_w_id < (LEAST(0, 1))::bigint)
->  Bitmap Index Scan on stock_pkey  (cost=0.00..30784.44 rows=168 
width=0) (actual time=0.005..0.005 rows=0 loops=1)
  Index Cond: (s_w_id < (LEAST(0, 1))::bigint)
  Planning time: 0.228 ms
  Execution time: 0.107 ms

  [NEW version]
  Seq Scan on stock ref_0  (cost=0.00..304469.17 rows=113 width=25) (actual 
time=716.397..716.397 rows=0 loops=1)
Filter: (s_w_id < (LEAST(0, 1))::bigint)
Rows Removed by Filter: 500
  Planning Time: 0.221 ms
  Execution Time: 716.513 ms


### QUERY 1:

  select
ref_0.o_d_id as c0
  from
public.oorder as ref_0
  where EXISTS (
select
  1
from
  (select distinct
  ref_0.o_entry_d as c0,
  ref_1.c_credit as c1
   from
  public.customer as ref_1
   where (false)
  ) as subq_1
  );

- Commit: bf6c614 (Do execGrouping.c via expression eval machinery, take two)

- Our analysis: We are not sure about the root cause of this regression. This 
might have to do with grouping logic.

- Query Execution Plans

  [OLD version]
  Seq Scan on oorder ref_0  (cost=0.00..77184338.54 rows=15022 width=4) (actual 
time=34.173..34.173 rows=0 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 30044
SubPlan 1
  ->  Subquery Scan on subq_1  (cost=2569.01..2569.03 rows=1 width=0) 
(actual time=0.001..0.001 rows=0 loops=30044)
->  HashAggregate  (cost=2569.01..2569.02 rows=1 width=3) (actual 
time=0.000..0.000 rows=0 loops=30044)
  Group Key: ref_0.o_entry_d, ref_1.c_credit
  ->  Result  (cost=0.00..2569.00 rows=1 width=3) (actual 
time=0.000..0.000 rows=0 loops=30044)
One-Time Filter: false
->  Seq Scan on customer ref_1  (cost=0.00..2569.00 
rows=1 width=3) (never executed)
  Planning time: 0.325 ms
  Execution time: 34.234 ms

  [NEW version]
  Seq Scan on oorder ref_0  (cost=0.00..1152.32 rows=15022 width=4) (actual 
time=74.799..74.799 rows=0 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 30044
SubPlan 1
  ->  Subquery Scan on subq_1  (cost=0.00..0.02 rows=1 width=0) (actual 
time=0.002..0.002 rows=0 loops=30044)
->  HashAggregate  (cost=0.00..0.01 rows=1 width=20) (actual 
time=0.000..0.000 rows=0 loops=30044)
  Group Key: ref_0.o_entry_d, c_credit
  ->  Result  (cost=0.00..0.00 rows=0 width=20) (actual 
time=0.000..0.000 rows=0 loops=30044)
One-Time Filter: false
  Planning Time: 0.350 ms
  Execution Time: 79.237 ms


From: Jeff Janes 
Sent: Tuesday, February 12, 2019 1:03 PM
To: Jung, Jinho
Cc: pgsql-performa...@postgresql.org
Subject: Re: Performance regressions found using sqlfuzz

On Tue, Feb 12, 2019 at 4:23 AM Jung, Jinho 
mailto:jinho.j...@gatech.edu>> wrote:


Hello,

We are developing a tool called sqlfuzz for automatically finding performance 
regressions in PostgreSQL. sqlfuzz performs mutational fuzzing to generate SQL 
queries that take more time to execute on the latest version of PostgreSQL 
compared to prior versions. We hope that these queries would help further 
increase the utility of the regression test suite.

We would greatly appreciate feedback from the community regarding the queries 
found by the tool so far. We have already incorporated prior feedback from the 
community in the latest version of sqlfuzz.

This approach doesn't seem very exciting to me as-is, because optimization is a 
very pragmatic endeavor.  We make decisions all the time that might make some 
queries better and others worse.  If the queries that get better are 
natural/common ones, and the ones that get worse are weird/uncommon ones (like 
generated by a fuzzer), then making that change is an improvement even if there 
are some performance (as opposed to correctness) regressions.

I would be more interested in investigating some of these if the report would:

1) include the exact commit in which the

Re: Performance regressions found using sqlfuzz

[Jeff Janes]

On Tue, Feb 12, 2019 at 4:23 AM Jung, Jinho  wrote:

>
> Hello,
>
> We are developing a tool called sqlfuzz for automatically finding
> performance regressions in PostgreSQL. sqlfuzz performs mutational fuzzing
> to generate SQL queries that take more time to execute on the latest
> version of PostgreSQL compared to prior versions. We hope that these
> queries would help further increase the utility of the regression test
> suite.
>
> We would greatly appreciate feedback from the community regarding the
> queries found by the tool so far. We have already incorporated prior
> feedback from the community in the latest version of sqlfuzz.
>

This approach doesn't seem very exciting to me as-is, because optimization
is a very pragmatic endeavor.  We make decisions all the time that might
make some queries better and others worse.  If the queries that get better
are natural/common ones, and the ones that get worse are weird/uncommon
ones (like generated by a fuzzer), then making that change is an
improvement even if there are some performance (as opposed to correctness)
regressions.

I would be more interested in investigating some of these if the report
would:

1) include the exact commit in which the regression was introduced (i.e.
automate "git bisect").
2) verify that the regression still exists in the dev HEAD and report which
commit it was verified in (since HEAD changes frequently).
3) report which queries (if any) in your corpus were made better by the
same commit which made the victim query worse.

Cheers,

Jeff

>

Re: Performance regressions found using sqlfuzz

[Christoph Berg]

Re: Jung, Jinho 2019-02-11 

> We are developing a tool called sqlfuzz for automatically finding performance 
> regressions in PostgreSQL. sqlfuzz performs mutational fuzzing to generate 
> SQL queries that take more time to execute on the latest version of 
> PostgreSQL compared to prior versions. We hope that these queries would help 
> further increase the utility of the regression test suite.

Hi,

is sqlfuzz available anywhere? Does it have anything to do with
sqlsmith? The query formatting looks very much like sqlsmith's.

Christoph

Re: Performance impact of updating target columns with unchanged values ON CONFLICT

[Abi Noda]

I take that question back – someone helped me on StackExchange and
addressed it:

*> It appears that Postgres is smart enough to identify cases where indexed
columns are not changed , and perform HOT updates; thus , there is no
difference between having or not having key columns in update statement
from performance point of view. The only thing that matters it whether
actual value changed. Surely, this behaviour is limited to B-Tree indexes. *

https://dba.stackexchange.com/questions/223231/performance-impact-of-updating-target-columns-with-same-values-on-conflict

On Fri, Nov 23, 2018 at 7:44 PM Abi Noda  wrote:

> Thanks Justin. Do you know if Postgres treats an UPDATE that sets the
> indexed columns set to the same previous values as a change? Or does it
> only count it as "changed" if the values are different. This is ambiguous
> to me.
>
> *> HOT solves this problem for a restricted but useful special case where
> a tuple is repeatedly updated in ways that do not change its indexed
> columns.*
>
> *> With HOT, a new tuple placed on the same page and with all indexed
> columns the same as its parent row version does not get new index entries.*
>
> *> [HOT] will create a new physical heap tuple when inserting, and not a
> new index tuple, if and only if the update did not affect indexed columns.*
>
>
>
> On Thu, Nov 22, 2018 at 2:40 PM Justin Pryzby 
> wrote:
>
>> On Thu, Nov 22, 2018 at 01:31:10PM -0800, Abi Noda wrote:
>> > In other words, is Postgres smart enough to not actually write to disk
>> any
>> > columns that haven’t changed value or update indexes based on those
>> columns?
>>
>> You're asking about what's referred to as Heap only tuples:
>>
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=HEAD
>> https://wiki.postgresql.org/wiki/Index-only_scans#Interaction_with_HOT
>>
>> Note, if you're doing alot of updates, you should consider setting a
>> lower the
>> table fillfactor, since HOT is only possible if the new tuple (row
>> version) is
>> on the same page as the old tuple.
>>
>> |With HOT, a new tuple placed on the same page and with all indexed
>> columns the
>> |same as its parent row version does not get new index entries."
>>
>> And check pg_stat_user_tables to verify that's working as intended.
>>
>> Justin
>>
>