Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Kashif Zeeshan]

Hi Jyoti

Make sure you have done the following repo setup

sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
--fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Create the repository configuration file:
sudo sh -c 'echo "deb
[signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc]
https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" >
/etc/apt/sources.list.d/pgdg.list'


Also once the above is done then execute following
apt-get update

then execute the following command and it shd install the latest version of
PG in this case PG17.
sudo apt -y install postgresql

Regards
Kashif Zeeshan

On Mon, Aug 26, 2024 at 11:52 AM Jyoti Saxena 
wrote:

> Hi,
>
> I’m encountering an issue while trying to install PostgreSQL 17 on my
> Ubuntu 22.04 (Jammy) system. I followed the installation instructions from
> the official PostgreSQL website
> https://www.postgresql.org/download/linux/ubuntu/.
>
> When I run the command sudo apt -y install postgresql-17, I receive the
> following message:
>
> Reading package lists... DoneBuilding dependency tree... DoneReading state 
> information... DonePackage postgresql-17 is not available, but is referred to 
> by another package.This may mean that the package is missing, has been 
> obsoleted, or is only available from another source.
>
> Here’s what I’ve already tried:
>
>  1. Updated the package list with sudo apt update.
>
>  2. Verified that the PostgreSQL Apt Repository is added correctly with
>
> deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main.
>
> 3. Checked available packages using apt-cache search postgresql-17.
>
>  4. Despite these steps, I’m unable to install PostgreSQL 17. Could you
> please advise on how to resolve this issue or if there's something I might
> be missing?
>
> Thank you in advance for your help!
>
> Best regards,
>
> Jyoti
>

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Jyoti Saxena]

Hi Kashif,

Thank you for the instructions. I followed the steps provided to set up the
repository and install PostgreSQL. However, after completing the setup, the
system installed PostgreSQL 16.4 instead of the expected PostgreSQL 17. The
version installed is psql (PostgreSQL) 16.4 (Ubuntu 16.4-1.pgdg22.04+1).

Here are the steps I followed:


   1. Installed curl and ca-certificates.
   2. Set up the repository key and configuration as instructed.
   3. Ran apt-get update.
   4. Executed sudo apt -y install postgresql.

The repository seems to have installed the latest available version as
PostgreSQL 16.4. Could you please advise if there is a different repository
I should use for PostgreSQL 17, or if additional steps are needed to
specifically target version 17?

Thank you for your assistance.

Best regards,
Jyoti

On Mon, Aug 26, 2024 at 12:35 PM Kashif Zeeshan 
wrote:

> Hi Jyoti
>
> Make sure you have done the following repo setup
>
> sudo apt install curl ca-certificates
> sudo install -d /usr/share/postgresql-common/pgdg
> sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
> --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
>
> # Create the repository configuration file:
> sudo sh -c 'echo "deb
> [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc]
> https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" >
> /etc/apt/sources.list.d/pgdg.list'
>
>
> Also once the above is done then execute following
> apt-get update
>
> then execute the following command and it shd install the latest version
> of PG in this case PG17.
> sudo apt -y install postgresql
>
> Regards
> Kashif Zeeshan
>
> On Mon, Aug 26, 2024 at 11:52 AM Jyoti Saxena 
> wrote:
>
>> Hi,
>>
>> I’m encountering an issue while trying to install PostgreSQL 17 on my
>> Ubuntu 22.04 (Jammy) system. I followed the installation instructions from
>> the official PostgreSQL website
>> https://www.postgresql.org/download/linux/ubuntu/.
>>
>> When I run the command sudo apt -y install postgresql-17, I receive the
>> following message:
>>
>> Reading package lists... DoneBuilding dependency tree... DoneReading state 
>> information... DonePackage postgresql-17 is not available, but is referred 
>> to by another package.This may mean that the package is missing, has been 
>> obsoleted, or is only available from another source.
>>
>> Here’s what I’ve already tried:
>>
>>  1. Updated the package list with sudo apt update.
>>
>>  2. Verified that the PostgreSQL Apt Repository is added correctly with
>>
>> deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main.
>>
>> 3. Checked available packages using apt-cache search postgresql-17.
>>
>>  4. Despite these steps, I’m unable to install PostgreSQL 17. Could you
>> please advise on how to resolve this issue or if there's something I might
>> be missing?
>>
>> Thank you in advance for your help!
>>
>> Best regards,
>>
>> Jyoti
>>
>

Re: Problem with a Query

[Siraj G]

Thanks Tom. Collecting full stats on the tables involved corrected the
execution.

On Tue, Aug 13, 2024 at 9:57 AM Tom Lane  wrote:

> Siraj G  writes:
> > We migrated a PgSQL database from Cloud SQL to compute engine and since
> > then there is a SQL we observed taking a long time. After some study, I
> > found that the SQL is using NESTED LOOP where the cost is too high.
>
> The core of your problem seems to be here:
>
> >  ->  Index Scan using marketing_a_cancel__55_idx
> on
> > marketing_app_leadhistory w0  (cost=0.57..4274.30 rows=1 width=8) (actual
> > time=46.678..51.232 rows=44 loops=1)
> >Index Cond: ((cancel_event_id IS NOT NULL) AND
> > (cancel_event_type = 1))
> >Filter: ((status_id = 93) AND
> > ((followup_date)::date >= '2024-08-01'::date) AND ((followup_date)::date
> <=
> > '2024-08-07'::date))
> >Rows Removed by Filter: 22268
> >Buffers: shared hit=9170 read=19
>
> If the planner had estimated 40-some rows out of this step, rather
> than one, it would certainly not have chosen to use nestloop joins
> atop this.  So the big problem to focus on is making that estimate
> better.
>
> A secondary problem is that the choice of index seems poor: the
> index itself is selecting 44+22268 = 22312 rows and then the filter
> condition is throwing away 99.8% of those rows.  Probably, using
> an index on (status_id, followup_date) would have worked better.
>
> I suspect that both of these things are tied to the non-normalization
> of your "cancel" condition.  The planner probably believes that
> "cancel_event_id IS NOT NULL" is statistically independent of
> "cancel_event_type = 1"; but I'll bet it isn't, and thus the index
> condition selects many more rows than the planner guessed.  You might
> be able to improve that estimate by creating extended stats on both of
> those columns, but really a better idea would be to take a step back
> and figure out if those two columns can't be merged into one.
>
> regards, tom lane
>

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Kashif Zeeshan]

On Mon, Aug 26, 2024 at 3:19 PM Jyoti Saxena 
wrote:

> Hi Kashif,
>
> Thank you for the instructions. I followed the steps provided to set up
> the repository and install PostgreSQL. However, after completing the setup,
> the system installed PostgreSQL 16.4 instead of the expected PostgreSQL 17.
> The version installed is psql (PostgreSQL) 16.4 (Ubuntu 16.4-1.pgdg22.04+1).
>
> Here are the steps I followed:
>
>
>1. Installed curl and ca-certificates.
>2. Set up the repository key and configuration as instructed.
>3. Ran apt-get update.
>4. Executed sudo apt -y install postgresql.
>
> The repository seems to have installed the latest available version as
> PostgreSQL 16.4. Could you please advise if there is a different repository
> I should use for PostgreSQL 17, or if additional steps are needed to
> specifically target version 17?
>

So the last version available is PG16, which means PG17 is still not there.
If you require PG17 beta for Testing purposes then you can install it from
code, which is available on following

https://www.postgresql.org/ftp/source/v17beta3/

>
> Thank you for your assistance.
>
> Best regards,
> Jyoti
>
> On Mon, Aug 26, 2024 at 12:35 PM Kashif Zeeshan 
> wrote:
>
>> Hi Jyoti
>>
>> Make sure you have done the following repo setup
>>
>> sudo apt install curl ca-certificates
>> sudo install -d /usr/share/postgresql-common/pgdg
>> sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
>> --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
>>
>> # Create the repository configuration file:
>> sudo sh -c 'echo "deb
>> [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc]
>> https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" >
>> /etc/apt/sources.list.d/pgdg.list'
>>
>>
>> Also once the above is done then execute following
>> apt-get update
>>
>> then execute the following command and it shd install the latest version
>> of PG in this case PG17.
>> sudo apt -y install postgresql
>>
>> Regards
>> Kashif Zeeshan
>>
>> On Mon, Aug 26, 2024 at 11:52 AM Jyoti Saxena 
>> wrote:
>>
>>> Hi,
>>>
>>> I’m encountering an issue while trying to install PostgreSQL 17 on my
>>> Ubuntu 22.04 (Jammy) system. I followed the installation instructions from
>>> the official PostgreSQL website
>>> https://www.postgresql.org/download/linux/ubuntu/.
>>>
>>> When I run the command sudo apt -y install postgresql-17, I receive the
>>> following message:
>>>
>>> Reading package lists... DoneBuilding dependency tree... DoneReading state 
>>> information... DonePackage postgresql-17 is not available, but is referred 
>>> to by another package.This may mean that the package is missing, has been 
>>> obsoleted, or is only available from another source.
>>>
>>> Here’s what I’ve already tried:
>>>
>>>  1. Updated the package list with sudo apt update.
>>>
>>>  2. Verified that the PostgreSQL Apt Repository is added correctly with
>>>
>>> deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main.
>>>
>>> 3. Checked available packages using apt-cache search postgresql-17.
>>>
>>>  4. Despite these steps, I’m unable to install PostgreSQL 17. Could you
>>> please advise on how to resolve this issue or if there's something I might
>>> be missing?
>>>
>>> Thank you in advance for your help!
>>>
>>> Best regards,
>>>
>>> Jyoti
>>>
>>

Problem with a query

[Siraj G]

Hello!

We have a couple of queries that all of a sudden became rather slow. I
took explain analyze from one of the SQLs as bdlow. Can you please check
and suggest if anything can be done?

'-> Table scan on   (actual time=0.019..71.526 rows=38622
loops=1)\n
-> Aggregate using temporary table  (actual time=33891741.611..33891815.749
rows=38622 loops=1)\n
-> Nested loop inner join  (cost=155705096.99 rows=0) (actual
time=33872496.362..33891210.133 rows=38622 loops=1)\n
-> Filter: (SES.IS_SERVER_ID <> (-(1)))  (cost=371424.15
rows=1803523) (actual time=0.036..3921.142 rows=3651493 loops=1)\n

-> Index scan on SES using IS_SESSION_IDX4  (cost=371424.15 rows=3607044)
(actual time=0.034..3600.114 rows=3651493 loops=1)\n
-> Index lookup on DST using  (IS_SESSION_ID=SES.IS_SESSION_ID)
 (actual time=0.004..0.004 rows=0 loops=3651493)\n
-> Materialize  (cost=0.00..0.00 rows=0) (actual
time=33886497.639..33886608.008 rows=38622 loops=1)\n
-> Table scan on   (actual time=0.018..51.715 rows=38622
loops=1)\n
-> Aggregate using temporary table  (actual time=33872191.430..33872246.080
rows=38622 loops=1)\n
-> Nested loop left join  (cost=1025850971.42 rows=1242668643) (actual
time=910.618..33869299.956 rows=38622 loops=1)\n

-> Nested loop left join  (cost=124916205.44 rows=1242668643) (actual
time=910.606..33868869.982 rows=38622 loops=1)\n

-> Nested loop left join  (cost=649328.22 rows=949) (actual
time=21.155..4387.994 rows=38622 loops=1)\n

-> Nested loop left join  (cost=648884.46 rows=949) (actual
time=21.144..4189.892 rows=38622 loops=1)\n

-> Nested loop inner join  (cost=648552.34 rows=949) (actual
time=21.127..3847.460 rows=38622 loops=1)\n

-> Inner hash join (MD.REP_ID = P.IS_REPOSITORY_ID)  (cost=0.96 rows=0)
(actual time=0.045..0.054 rows=1 loops=1)\n

-> Table scan on MD  (cost=7.00 rows=2) (actual time=0.007..0.013 rows=2
loops=1)\n
-> Hash\n
-> Nested loop inner join  (cost=0.70 rows=0) (actual time=0.024..0.027
rows=1 loops=1)\n

-> Filter: (LU.IS_PROJ_ID is not null)  (cost=0.35 rows=1) (actual
time=0.010..0.012 rows=1 loops=1)\n

-> Table scan on LU  (cost=0.35 rows=1) (actual time=0.009..0.011 rows=1
loops=1)\n
-> Filter: (P.IS_PROJ_GUID = LU.IS_PROJ_GUID)  (cost=0.26 rows=0) (actual
time=0.014..0.014 rows=1 loops=1)\n

-> Single-row index lookup on P using PRIMARY (IS_PROJ_ID=LU.IS_PROJ_ID)
 (cost=0.26 rows=1) (actual time=0.012..0.012 rows=1 loops=1)\n

-> Filter: ((DS.REPOSITORYID = MD.REP_GUID) and (DS.PROJECTID =
LU.IS_PROJ_GUID) and (DS.RECORDTIME >= LU.IS_WIN_BEGIN) and (DS.RECORDTIME
< LU.IS_WIN_END))  (cost=11300581.57 rows=18978) (actual
time=21.080..3837.717 rows=38622 loops=1)\n

-> Index range scan on DS (re-planned for each iteration)
 (cost=11300581.57 rows=17084027) (actual time=21.071..3653.945 rows=39790
loops=1)\n
-> Filter: (MD.REP_ID = U.IS_REPOSITORY_ID)  (cost=0.25 rows=1) (actual
time=0.008..0.008 rows=1 loops=38622)\n

-> Single-row index lookup on U using EM_USER_PK (EM_USER_GUID=DS.USERID,
IS_REPOSITORY_ID=MD.REP_ID)  (cost=0.25 rows=1) (actual time=0.007..0.007
rows=1 loops=38622)\n
-> Single-row index lookup on D using IS_DOC_PK (IS_DOC_GUID=DS.DOCUMENTID,
IS_PROJ_ID=LU.IS_PROJ_ID)  (cost=0.37 rows=1) (actual time=0.004..0.004
rows=1 loops=38622)\n
-> Nested loop inner join  (cost=23163414.56 rows=1309557) (actual
time=714.186..876.817 rows=1 loops=38622)\n

-> Table scan on LU1  (cost=0.01 rows=1) (actual time=0.007..0.011 rows=1
loops=38622)\n
-> Filter: ((SS01.PROJECTID = LU1.IS_PROJ_GUID) and (SS01.SCHEDULEID =
DS.JOBID) and (SS01.SESSIONID = DS.SESSIONID) and (SS01.RECORDTIME >=
LU1.IS_WIN_BEGIN) and (SS01.RECORDTIME < LU1.IS_WIN_END))  (cost=24410.22
rows=1309557) (actual time=714.176..876.804 rows=1 loops=38622)\n

-> Index lookup on SS01 using IS_SCHEDULE_STATS_IDX1 (SCHEDULETYPE=1)
 (cost=24410.22 rows=1309557) (actual time=0.035..522.644 rows=1360349
loops=38622)\n
-> Index lookup on S using IS_SCHED_PK (IS_SCHED_GUID=SS01.TRIGGERID)
 (cost=0.63 rows=1) (actual time=0.009..0.010 rows=1 loops=38622)\n'

Regards
Siraj

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Kashif Zeeshan]

To compile PG from code follow the below instructions.
https://www.postgresql.org/docs/current/install-make.html

Thanks
Kashif Zeeshan

On Mon, Aug 26, 2024 at 3:30 PM Kashif Zeeshan 
wrote:

>
>
> On Mon, Aug 26, 2024 at 3:19 PM Jyoti Saxena 
> wrote:
>
>> Hi Kashif,
>>
>> Thank you for the instructions. I followed the steps provided to set up
>> the repository and install PostgreSQL. However, after completing the setup,
>> the system installed PostgreSQL 16.4 instead of the expected PostgreSQL 17.
>> The version installed is psql (PostgreSQL) 16.4 (Ubuntu 16.4-1.pgdg22.04+1).
>>
>> Here are the steps I followed:
>>
>>
>>1. Installed curl and ca-certificates.
>>2. Set up the repository key and configuration as instructed.
>>3. Ran apt-get update.
>>4. Executed sudo apt -y install postgresql.
>>
>> The repository seems to have installed the latest available version as
>> PostgreSQL 16.4. Could you please advise if there is a different repository
>> I should use for PostgreSQL 17, or if additional steps are needed to
>> specifically target version 17?
>>
>
> So the last version available is PG16, which means PG17 is still not there.
> If you require PG17 beta for Testing purposes then you can install it from
> code, which is available on following
>
> https://www.postgresql.org/ftp/source/v17beta3/
>
>>
>> Thank you for your assistance.
>>
>> Best regards,
>> Jyoti
>>
>> On Mon, Aug 26, 2024 at 12:35 PM Kashif Zeeshan 
>> wrote:
>>
>>> Hi Jyoti
>>>
>>> Make sure you have done the following repo setup
>>>
>>> sudo apt install curl ca-certificates
>>> sudo install -d /usr/share/postgresql-common/pgdg
>>> sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
>>> --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
>>>
>>> # Create the repository configuration file:
>>> sudo sh -c 'echo "deb
>>> [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc]
>>> https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main"
>>> > /etc/apt/sources.list.d/pgdg.list'
>>>
>>>
>>> Also once the above is done then execute following
>>> apt-get update
>>>
>>> then execute the following command and it shd install the latest version
>>> of PG in this case PG17.
>>> sudo apt -y install postgresql
>>>
>>> Regards
>>> Kashif Zeeshan
>>>
>>> On Mon, Aug 26, 2024 at 11:52 AM Jyoti Saxena 
>>> wrote:
>>>
 Hi,

 I’m encountering an issue while trying to install PostgreSQL 17 on my
 Ubuntu 22.04 (Jammy) system. I followed the installation instructions from
 the official PostgreSQL website
 https://www.postgresql.org/download/linux/ubuntu/.

 When I run the command sudo apt -y install postgresql-17, I receive the
 following message:

 Reading package lists... DoneBuilding dependency tree... DoneReading state 
 information... DonePackage postgresql-17 is not available, but is referred 
 to by another package.This may mean that the package is missing, has been 
 obsoleted, or is only available from another source.

 Here’s what I’ve already tried:

  1. Updated the package list with sudo apt update.

  2. Verified that the PostgreSQL Apt Repository is added correctly with

 deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main.

 3. Checked available packages using apt-cache search postgresql-17.

  4. Despite these steps, I’m unable to install PostgreSQL 17. Could you
 please advise on how to resolve this issue or if there's something I might
 be missing?

 Thank you in advance for your help!

 Best regards,

 Jyoti

>>>

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Ray O'Donnell]

On 26/08/2024 07:52, Jyoti Saxena wrote:


Hi,

I’m encountering an issue while trying to install PostgreSQL 17 on my 
Ubuntu 22.04 (Jammy) system. I followed the installation instructions 
from the official PostgreSQL website 
https://www.postgresql.org/download/linux/ubuntu/.




I don't know what's currently available in the PG apt repo, but 
PostgreSQL 17 hasn't been released yet. Maybe you need to ask for a beta 
package or something?


Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Jyoti Saxena]

Hello,

Thank you for the suggestion to compile PostgreSQL from source. However, I
am currently looking for a way to install PostgreSQL 17 through a package
manager or a precompiled version that can be more seamlessly integrated and
managed in my environment.

Could you please confirm if PostgreSQL 17 is available through the
PostgreSQL APT repository for Ubuntu 22.04, or if there will be a delay in
its availability? Additionally, if you have any other recommendations for
installing PostgreSQL 17 without compiling from source, such as through
third-party repositories or Docker, I would appreciate your guidance.

Also, could you please provide information on where I can find the binary
installation packages for PostgreSQL 17 for Windows? This would be
extremely helpful for setting up PostgreSQL on my Windows system.

Thank you once again for your support.

Best regards,

Jyoti

On Mon, Aug 26, 2024 at 4:02 PM Kashif Zeeshan 
wrote:

> To compile PG from code follow the below instructions.
> https://www.postgresql.org/docs/current/install-make.html
>
> Thanks
> Kashif Zeeshan
>
> On Mon, Aug 26, 2024 at 3:30 PM Kashif Zeeshan 
> wrote:
>
>>
>>
>> On Mon, Aug 26, 2024 at 3:19 PM Jyoti Saxena 
>> wrote:
>>
>>> Hi Kashif,
>>>
>>> Thank you for the instructions. I followed the steps provided to set up
>>> the repository and install PostgreSQL. However, after completing the setup,
>>> the system installed PostgreSQL 16.4 instead of the expected PostgreSQL 17.
>>> The version installed is psql (PostgreSQL) 16.4 (Ubuntu 16.4-1.pgdg22.04+1).
>>>
>>> Here are the steps I followed:
>>>
>>>
>>>1. Installed curl and ca-certificates.
>>>2. Set up the repository key and configuration as instructed.
>>>3. Ran apt-get update.
>>>4. Executed sudo apt -y install postgresql.
>>>
>>> The repository seems to have installed the latest available version as
>>> PostgreSQL 16.4. Could you please advise if there is a different repository
>>> I should use for PostgreSQL 17, or if additional steps are needed to
>>> specifically target version 17?
>>>
>>
>> So the last version available is PG16, which means PG17 is still not
>> there.
>> If you require PG17 beta for Testing purposes then you can install it
>> from code, which is available on following
>>
>> https://www.postgresql.org/ftp/source/v17beta3/
>>
>>>
>>> Thank you for your assistance.
>>>
>>> Best regards,
>>> Jyoti
>>>
>>> On Mon, Aug 26, 2024 at 12:35 PM Kashif Zeeshan 
>>> wrote:
>>>
 Hi Jyoti

 Make sure you have done the following repo setup

 sudo apt install curl ca-certificates
 sudo install -d /usr/share/postgresql-common/pgdg
 sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
 --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

 # Create the repository configuration file:
 sudo sh -c 'echo "deb
 [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc]
 https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main"
 > /etc/apt/sources.list.d/pgdg.list'


 Also once the above is done then execute following
 apt-get update

 then execute the following command and it shd install the latest
 version of PG in this case PG17.
 sudo apt -y install postgresql

 Regards
 Kashif Zeeshan

 On Mon, Aug 26, 2024 at 11:52 AM Jyoti Saxena <
 jyotisaxena@gmail.com> wrote:

> Hi,
>
> I’m encountering an issue while trying to install PostgreSQL 17 on my
> Ubuntu 22.04 (Jammy) system. I followed the installation instructions from
> the official PostgreSQL website
> https://www.postgresql.org/download/linux/ubuntu/.
>
> When I run the command sudo apt -y install postgresql-17, I receive
> the following message:
>
> Reading package lists... DoneBuilding dependency tree... DoneReading 
> state information... DonePackage postgresql-17 is not available, but is 
> referred to by another package.This may mean that the package is missing, 
> has been obsoleted, or is only available from another source.
>
> Here’s what I’ve already tried:
>
>  1. Updated the package list with sudo apt update.
>
>  2. Verified that the PostgreSQL Apt Repository is added correctly
> with
>
> deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main.
>
> 3. Checked available packages using apt-cache search postgresql-17.
>
>  4. Despite these steps, I’m unable to install PostgreSQL 17. Could
> you please advise on how to resolve this issue or if there's something I
> might be missing?
>
> Thank you in advance for your help!
>
> Best regards,
>
> Jyoti
>

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Ray O'Donnell]

On 26/08/2024 11:43, Ray O'Donnell wrote:

On 26/08/2024 07:52, Jyoti Saxena wrote:


Hi,

I’m encountering an issue while trying to install PostgreSQL 17 on my 
Ubuntu 22.04 (Jammy) system. I followed the installation instructions 
from the official PostgreSQL website 
https://www.postgresql.org/download/linux/ubuntu/.




I don't know what's currently available in the PG apt repo, but 
PostgreSQL 17 hasn't been released yet. Maybe you need to ask for a 
beta package or something?


PS - there are more instructions at https://apt.postgresql.org, 
including a link to this page on the wiki which will hopefully help:


    https://wiki.postgresql.org/wiki/Apt/FAQ#Development_snapshots

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Kashif Zeeshan]

On Mon, Aug 26, 2024 at 3:54 PM Jyoti Saxena 
wrote:

> Hello,
>
> Thank you for the suggestion to compile PostgreSQL from source. However, I
> am currently looking for a way to install PostgreSQL 17 through a package
> manager or a precompiled version that can be more seamlessly integrated and
> managed in my environment.
>
> Could you please confirm if PostgreSQL 17 is available through the
> PostgreSQL APT repository for Ubuntu 22.04, or if there will be a delay in
> its availability? Additionally, if you have any other recommendations for
> installing PostgreSQL 17 without compiling from source, such as through
> third-party repositories or Docker, I would appreciate your guidance.
>
Hi
If the package is not available on repo then you cant install it using it.
The other option is to compile using code.
As far as PG17 is concerned then its Beta 3 is released, maybe someone else
can guide on how to install it.

Regards
Kashif Zeeshan

> Also, could you please provide information on where I can find the binary
> installation packages for PostgreSQL 17 for Windows? This would be
> extremely helpful for setting up PostgreSQL on my Windows system.
>
 For windows follow the link

https://www.postgresql.org/download/windows/

> Thank you once again for your support.
>
> Best regards,
>
> Jyoti
>
> On Mon, Aug 26, 2024 at 4:02 PM Kashif Zeeshan 
> wrote:
>
>> To compile PG from code follow the below instructions.
>> https://www.postgresql.org/docs/current/install-make.html
>>
>> Thanks
>> Kashif Zeeshan
>>
>> On Mon, Aug 26, 2024 at 3:30 PM Kashif Zeeshan 
>> wrote:
>>
>>>
>>>
>>> On Mon, Aug 26, 2024 at 3:19 PM Jyoti Saxena 
>>> wrote:
>>>
 Hi Kashif,

 Thank you for the instructions. I followed the steps provided to set up
 the repository and install PostgreSQL. However, after completing the setup,
 the system installed PostgreSQL 16.4 instead of the expected PostgreSQL 17.
 The version installed is psql (PostgreSQL) 16.4 (Ubuntu 
 16.4-1.pgdg22.04+1).

 Here are the steps I followed:


1. Installed curl and ca-certificates.
2. Set up the repository key and configuration as instructed.
3. Ran apt-get update.
4. Executed sudo apt -y install postgresql.

 The repository seems to have installed the latest available version as
 PostgreSQL 16.4. Could you please advise if there is a different repository
 I should use for PostgreSQL 17, or if additional steps are needed to
 specifically target version 17?

>>>
>>> So the last version available is PG16, which means PG17 is still not
>>> there.
>>> If you require PG17 beta for Testing purposes then you can install it
>>> from code, which is available on following
>>>
>>> https://www.postgresql.org/ftp/source/v17beta3/
>>>

 Thank you for your assistance.

 Best regards,
 Jyoti

 On Mon, Aug 26, 2024 at 12:35 PM Kashif Zeeshan <
 kashi.zees...@gmail.com> wrote:

> Hi Jyoti
>
> Make sure you have done the following repo setup
>
> sudo apt install curl ca-certificates
> sudo install -d /usr/share/postgresql-common/pgdg
> sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
> --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
>
> # Create the repository configuration file:
> sudo sh -c 'echo "deb
> [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc]
> https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg
> main" > /etc/apt/sources.list.d/pgdg.list'
>
>
> Also once the above is done then execute following
> apt-get update
>
> then execute the following command and it shd install the latest
> version of PG in this case PG17.
> sudo apt -y install postgresql
>
> Regards
> Kashif Zeeshan
>
> On Mon, Aug 26, 2024 at 11:52 AM Jyoti Saxena <
> jyotisaxena@gmail.com> wrote:
>
>> Hi,
>>
>> I’m encountering an issue while trying to install PostgreSQL 17 on my
>> Ubuntu 22.04 (Jammy) system. I followed the installation instructions 
>> from
>> the official PostgreSQL website
>> https://www.postgresql.org/download/linux/ubuntu/.
>>
>> When I run the command sudo apt -y install postgresql-17, I receive
>> the following message:
>>
>> Reading package lists... DoneBuilding dependency tree... DoneReading 
>> state information... DonePackage postgresql-17 is not available, but is 
>> referred to by another package.This may mean that the package is 
>> missing, has been obsoleted, or is only available from another source.
>>
>> Here’s what I’ve already tried:
>>
>>  1. Updated the package list with sudo apt update.
>>
>>  2. Verified that the PostgreSQL Apt Repository is added correctly
>> with
>>
>> deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main.
>>
>> 3. Checked availa

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Jyoti Saxena]

Hi Ray,

I appreciate the clarification regarding the release status of PostgreSQL
17. Understanding that it is currently in the beta phase, I am eager to
integrate this version into my development and testing workflows.

Could you please assist me by providing the following information:

   - Instructions on how to access and install the beta version of
   PostgreSQL 17 via the PostgreSQL APT repository for Ubuntu.
   - Details on where I can find the binary installation files for
   PostgreSQL 17 beta for Windows.

Thank you once again for your continued support.

Best regards,

Jyoti

On Mon, Aug 26, 2024 at 4:13 PM Ray O'Donnell  wrote:

> On 26/08/2024 07:52, Jyoti Saxena wrote:
> >
> > Hi,
> >
> > I’m encountering an issue while trying to install PostgreSQL 17 on my
> > Ubuntu 22.04 (Jammy) system. I followed the installation instructions
> > from the official PostgreSQL website
> > https://www.postgresql.org/download/linux/ubuntu/.
> >
>
> I don't know what's currently available in the PG apt repo, but
> PostgreSQL 17 hasn't been released yet. Maybe you need to ask for a beta
> package or something?
>
> Ray.
>
>
>
> --
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie
>
>

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Ray O'Donnell]

On 26/08/2024 12:33, Jyoti Saxena wrote:


Hi Ray,

I appreciate the clarification regarding the release status of 
PostgreSQL 17. Understanding that it is currently in the beta phase, I 
am eager to integrate this version into my development and testing 
workflows.


Could you please assist me by providing the following information:

  * Instructions on how to access and install the beta version of
PostgreSQL 17 via the PostgreSQL APT repository for Ubuntu.
  * Details on where I can find the binary installation files for
PostgreSQL 17 beta for Windows.



Hi Jyoti,

I don't use Windows myself so I can't help you with that, but if you 
look at my other email to the list, there's a link to instructions on 
the wiki.


HTH,

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Problem with a Query

[Ron Johnson]

Aggressive autoanalyze and autovacuum settings solve most query problems.
These are my settings:
default_statistics_target = 5000
autovacuum_vacuum_scale_factor = 0.015
autovacuum_vacuum_threshold = 250
autovacuum_analyze_scale_factor = 0.015
autovacuum_analyze_threshold = 250

Such a high default_statistics_target value is controversial, but works for
our databases, and resetting it to 100 doesn't noticably speed up slow
parse/optimize on queries that take a long time to parse/optimize any more
than the 5000 value.

On Mon, Aug 26, 2024 at 6:30 AM Siraj G  wrote:

> Thanks Tom. Collecting full stats on the tables involved corrected the
> execution.
>
> On Tue, Aug 13, 2024 at 9:57 AM Tom Lane  wrote:
>
>> Siraj G  writes:
>> > We migrated a PgSQL database from Cloud SQL to compute engine and since
>> > then there is a SQL we observed taking a long time. After some study, I
>> > found that the SQL is using NESTED LOOP where the cost is too high.
>>
>> The core of your problem seems to be here:
>>
>> >  ->  Index Scan using
>> marketing_a_cancel__55_idx on
>> > marketing_app_leadhistory w0  (cost=0.57..4274.30 rows=1 width=8)
>> (actual
>> > time=46.678..51.232 rows=44 loops=1)
>> >Index Cond: ((cancel_event_id IS NOT NULL)
>> AND
>> > (cancel_event_type = 1))
>> >Filter: ((status_id = 93) AND
>> > ((followup_date)::date >= '2024-08-01'::date) AND
>> ((followup_date)::date <=
>> > '2024-08-07'::date))
>> >Rows Removed by Filter: 22268
>> >Buffers: shared hit=9170 read=19
>>
>> If the planner had estimated 40-some rows out of this step, rather
>> than one, it would certainly not have chosen to use nestloop joins
>> atop this.  So the big problem to focus on is making that estimate
>> better.
>>
>> A secondary problem is that the choice of index seems poor: the
>> index itself is selecting 44+22268 = 22312 rows and then the filter
>> condition is throwing away 99.8% of those rows.  Probably, using
>> an index on (status_id, followup_date) would have worked better.
>>
>> I suspect that both of these things are tied to the non-normalization
>> of your "cancel" condition.  The planner probably believes that
>> "cancel_event_id IS NOT NULL" is statistically independent of
>> "cancel_event_type = 1"; but I'll bet it isn't, and thus the index
>> condition selects many more rows than the planner guessed.  You might
>> be able to improve that estimate by creating extended stats on both of
>> those columns, but really a better idea would be to take a step back
>> and figure out if those two columns can't be merged into one.
>>
>> regards, tom lane
>>
>

-- 
Death to America, and butter sauce.
Iraq lobster!

Re: Issue Installing PostgreSQL 17 on Ubuntu 22.04 (Jammy)

[Adrian Klaver]

On 8/26/24 04:33, Jyoti Saxena wrote:

Hi Ray,

I appreciate the clarification regarding the release status of 
PostgreSQL 17. Understanding that it is currently in the beta phase, I 
am eager to integrate this version into my development and testing 
workflows.


Could you please assist me by providing the following information:

  * Instructions on how to access and install the beta version of
PostgreSQL 17 via the PostgreSQL APT repository for Ubuntu.


https://wiki.postgresql.org/wiki/Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release


  * Details on where I can find the binary installation files for
PostgreSQL 17 beta for Windows.


Not released yet. Check the link below for release:

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads



Thank you once again for your continued support.

Best regards,

Jyoti




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: On exclusion constraints and validity dates

[Paul Jungwirth]

On 8/22/24 11:13, Justin Giacobbi wrote:

I have an issue that on the surface seems orthogonal to existing functionality. I’m trying to 
dynamically update validity ranges as new s replace old s.


In a nutshell the problem looks like this:

psqlprompt=# select * from rangetest;
id |  rangecol
+-
   0 | empty
   0 | ["2024-05-05 00:00:00+00","2024-05-06 00:00:00+00")
   0 | ["2024-05-06 00:00:00+00","-03-31 00:00:00+00")
   1 | ["2024-05-06 00:00:00+00",)

psqlprompt=# insert into rangetest values (1, '["2024-06-07 00:00:00+0",)') on conflict on 
constraint rangetest_id_rangecol_excl do update rangecol = concat('[', lower(rangetest.rangecol),',', lower(excluded.rangecol),')')::tstzrange;


ERROR:  ON CONFLICT DO UPDATE not supported with exclusion constraints

So I’m not sure if I’m after a feature request, a workaround or contribution advice. Maybe someone 
can point me in the right direction.


 1. A ‘currently valid’ item that becomes invalid and is replaced by a new 
‘currently valid’ item
seems like such a real-world use case that there should be explicit support 
for it.
 1. Unfortunately, the temporal tables extensions seem too immature for my 
needs currently.
 2. Barring that an exclusion constraint arbiter would be a lovely solution.
 3. Barring either of those at least a ‘select all conflicts’ type feature that 
at least makes it
easy to pair the offending rows.

Currently I’m looking at working around this in the application or in a stored procedure/insert 
trigger that is essentially the same logic. Whichever seems easier to maintain.


Advice on how to submit a feature request, or maybe a better workaround that I haven’t discovered 
would be most welcome. What would be even more welcome is someone with insight into these pieces of 
the program that can tell me if I’d be biting off more than I can chew (or violating a principle) 
trying to submit one of the three options above as a feature.


Your example looks a bit like UPDATE FOR PORTION OF (from SQL:2011). That would 
give you this result:

1 | ["2024-05-06 00:00:00+00","2024-06-07 00:00:00+00")
1 | ["2024-06-07 00:00:00+00",)

We update the range you targeted, and we preserve untargeted range(s) 
before/after that.

I have submitted a patch for that,[0] but if you need it today you might be able to use the periods 
extension (although that is built on start/end columns, not ranges).[1]


An example that better fits ON CONFLICT DO UPDATE would be inserting `(1, '["2024-01-01 
00:00:00+0",)')`. That range is *wider* than what you already have in your table. Probably you want 
get this:


1 | ["2024-01-01 00:00:00+00","2024-05-06 00:00:00+00")
1 | ["2024-05-06 00:00:00+00",)

In other words: fill in the empty gaps and update what is already there.

This is similar to what Tom Johnston calls "temporal merge" in *Bitemporal Data: Theory and 
Practice* (building on his ideas for "whenever insert" and "whenever update"), pages 179-184.


I'm not sure we have enough information to do the right thing for an arbitrary exclusion constraint, 
but once we have primary keys and unique constraints with WITHOUT OVERLAPS (also in my submitted 
patches), the semantics should be specific enough that Postgres could implement it. It's on my list 
of things to do once we finish supporting SQL:2011. Maybe it would use the ON CONFLICT DO UPDATE 
syntax, or perhaps MERGE---I'm not sure yet.


[0] https://commitfest.postgresql.org/49/4308/
[1] https://github.com/xocolatl/periods

Yours,

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Postgresql Code of Conduct Committee Update

[Chris Travers]

The Code of Conduct Committee is currently operating at reduced capacity
due to multiple departures this year, and therefore acting with the minimum
membership allowed by the code of conduct.

Our current membership is noted at
https://www.postgresql.org/about/policies/coc_committee/

Alexandra Abramova, Sergei Kim, Simon Pain, and Chris Travers currently sit
on the committee.
Chris Travers is currently acting as chair.

More information about those on the committee can be found at the above
link.

Best Wishes,
Chris Travers
Interim Chair, Code of Conduct Committee

Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

[Adrian Klaver]

On 8/26/24 14:49, nikhil raj wrote:

Hi All,

I've encountered a noticeable difference in execution time and query 
execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when 
running a query on |information_schema| tables. Surprisingly, PostgreSQL 
16 is performing slower than PostgreSQL 13.


Did you run ANALYZE on the Postgres 16 instance?

*4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by 
gcc 11.4.0, 64-bit)*
Execution plan: PG13.14 Execution Plan 



*PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by 
gcc 11.4.0, 64-bit)*
Execution plan: PG16.4 Execution Plan 




Use:

https://explain.depesz.com/

It is easier to follow it's output.




Has anyone else experienced similar behavior or could provide insights 
into why PostgreSQL 16 might be slower for this query? Any advice or 
suggestions for optimization would be greatly appreciated.


Yes when ANALYZE was not run on a new instance.



Thank you!

NOTE:-  PFA the raw file of explain and analyze below.






--
Adrian Klaver
adrian.kla...@aklaver.com

Code of Conduct Committee Volunteer Drive

[Chris Travers]

This message is being sent from the Community Code of Conduct Committee,
with the approval of the Core Team.

As part of the Community CoC policy, the Committee membership is to be
refreshed on an annual basis. We are seeking up to 3 volunteers to serve on
the Committee for the coming year, October 1, 2024 - September 30, 2024.

We are seeking people who reflect the diversity of the PostgreSQL
community, with the goal to have members from multiple countries and varied
demographics.

The time commitment for Committee involvement varies, based on internal
administrative work and the number of active investigations. We estimate an
average of 5 to 10 hours per month, but that could increase if there is an
increase in the number of incident reports.

If you are interested, please complete the questionnaire below, and email
your responses to the Committee at c...@postgresql.org no later than
September 15, 2024 at 05:00 PM UTC.

The Questionnaire

Your name:

Current employer:

Current country of residence:

(We ask for employer and residence because one of the goals of the
Committee is to have representation from a variety of geographical areas.
We also want to avoid a concentration of members from one company.)

1. What interests you about being on the CoC Committee?

2. Have you been on another CoC Committee, or had a similar role at another
organization? (Prior experience is not required, it's just helpful to know
everyone's background.)

3. What else do you want to tell us about yourself that is helpful for us
to know about your potential involvement with the CoC Committee?

Please be sure to send your reply to the CoC email listed above. Thank you!

Regards,

Chris Travers

Acting Chair

PostgreSQL Community Code of Conduct Committee

Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

[nikhil raj]

Hi Adrian,

Thanks for the quick response.

I've already performed a vacuum, reindex, and analyze on the entire
database, but the issue persists. As you can see from the execution plan,
the time difference in PostgreSQL 16 is still significantly higher, even
after all maintenance activities have been completed.
It seems there might be a bug in PostgreSQL 16 where the performance of
queries on *information_schema* tables is degraded. As both the tables are
postgres system tables

https://explain.depesz.com/s/bdO6b  :-PG13

https://explain.depesz.com/s/bpAU  :- PG16


On Tue 27 Aug, 2024, 3:40 AM Adrian Klaver, 
wrote:

> On 8/26/24 14:49, nikhil raj wrote:
> > Hi All,
> >
> > I've encountered a noticeable difference in execution time and query
> > execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when
> > running a query on |information_schema| tables. Surprisingly, PostgreSQL
> > 16 is performing slower than PostgreSQL 13.
>
> Did you run ANALYZE on the Postgres 16 instance?
>
> > *4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by
> > gcc 11.4.0, 64-bit)*
> > Execution plan: PG13.14 Execution Plan
> > 
> >
> > *PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by
> > gcc 11.4.0, 64-bit)*
> > Execution plan: PG16.4 Execution Plan
> > 
>
>
> Use:
>
> https://explain.depesz.com/
>
> It is easier to follow it's output.
>
> >
> >
> > Has anyone else experienced similar behavior or could provide insights
> > into why PostgreSQL 16 might be slower for this query? Any advice or
> > suggestions for optimization would be greatly appreciated.
>
> Yes when ANALYZE was not run on a new instance.
>
> >
> > Thank you!
> >
> > NOTE:-  PFA the raw file of explain and analyze below.
> >
> >
> >
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

[Adrian Klaver]

On 8/26/24 15:41, nikhil raj wrote:

Hi Adrian,

Thanks for the quick response.

I've already performed a vacuum, reindex, and analyze on the entire 
database, but the issue persists. As you can see from the execution 
plan, the time difference in PostgreSQL 16 is still significantly 
higher, even after all maintenance activities have been completed.


It seems there might be a bug in PostgreSQL 16 where the performance of 
queries on *information_schema* tables is degraded. As both the tables 
are postgres system tables


https://explain.depesz.com/s/bdO6b   
:-PG13 


https://explain.depesz.com/s/bpAU   
:- PG16 


What I see is Postgres 13:

Nested Loop (cost=9.54..119.02 rows=1 width=128) (actual 
time=1.038..288.777 rows=1 loops=1)


Join Filter: (("*SELECT* 1".constraint_name)::name = "*SELECT* 
1_1".conname)

Rows Removed by Join Filter: 935
Buffers: shared hit=34,675

vs Postgres 16

Nested Loop (cost=62.84..538.22 rows=1 width=128) (actual 
time=1,905.153..14,006.921 rows=1 loops=1)


Join Filter: ("*SELECT* 1".conname = ("*SELECT* 
1_1".constraint_name)::name)

Rows Removed by Join Filter: 997
Buffers: shared hit=5,153,054


So either switching this

("*SELECT* 1".constraint_name)::name = "*SELECT* 1_1".conname

to

"*SELECT* 1".conname = ("*SELECT* 1_1".constraint_name)::name

is more of a change then I would expect.

Or

Buffers: shared hit=34,675

vs

Buffers: shared hit=5,153,054

indicates a hardware/configuration difference.

Are both instances running on the same machine?

Is the configuration for both the same?



On Tue 27 Aug, 2024, 3:40 AM Adrian Klaver, > wrote:


On 8/26/24 14:49, nikhil raj wrote:
 > Hi All,
 >
 > I've encountered a noticeable difference in execution time and query
 > execution plan row counts between PostgreSQL 13 and PostgreSQL 16
when
 > running a query on |information_schema| tables. Surprisingly,
PostgreSQL
 > 16 is performing slower than PostgreSQL 13.

Did you run ANALYZE on the Postgres 16 instance?

 > *4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu,
compiled by
 > gcc 11.4.0, 64-bit)*
 > Execution plan: PG13.14 Execution Plan
 > >
 >
 > *PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu,
compiled by
 > gcc 11.4.0, 64-bit)*
 > Execution plan: PG16.4 Execution Plan
 > >


Use:

https://explain.depesz.com/ 

It is easier to follow it's output.

 >
 >
 > Has anyone else experienced similar behavior or could provide
insights
 > into why PostgreSQL 16 might be slower for this query? Any advice or
 > suggestions for optimization would be greatly appreciated.

Yes when ANALYZE was not run on a new instance.

 >
 > Thank you!
 >
 > NOTE:-  PFA the raw file of explain and analyze below.
 >
 >
 >
 >

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Code of Conduct Committee Volunteer Drive

[Chris Travers]

On Tue, Aug 27, 2024, 5:09 AM Chris Travers  wrote:

> This message is being sent from the Community Code of Conduct Committee,
> with the approval of the Core Team.
>
> As part of the Community CoC policy, the Committee membership is to be
> refreshed on an annual basis. We are seeking up to 3 volunteers to serve on
> the Committee for the coming year, October 1, 2024 - September 30, 2024.
>
Correction.  The term ends on 30 September 2025.

> We are seeking people who reflect the diversity of the PostgreSQL
> community, with the goal to have members from multiple countries and varied
> demographics.
>
> The time commitment for Committee involvement varies, based on internal
> administrative work and the number of active investigations. We estimate an
> average of 5 to 10 hours per month, but that could increase if there is an
> increase in the number of incident reports.
>
> If you are interested, please complete the questionnaire below, and email
> your responses to the Committee at c...@postgresql.org no later than
> September 15, 2024 at 05:00 PM UTC.
>
> The Questionnaire
>
> Your name:
>
> Current employer:
>
> Current country of residence:
>
> (We ask for employer and residence because one of the goals of the
> Committee is to have representation from a variety of geographical areas.
> We also want to avoid a concentration of members from one company.)
>
> 1. What interests you about being on the CoC Committee?
>
> 2. Have you been on another CoC Committee, or had a similar role at
> another organization? (Prior experience is not required, it's just helpful
> to know everyone's background.)
>
> 3. What else do you want to tell us about yourself that is helpful for us
> to know about your potential involvement with the CoC Committee?
>
> Please be sure to send your reply to the CoC email listed above. Thank you!
>
> Regards,
>
> Chris Travers
>
> Acting Chair
>
> PostgreSQL Community Code of Conduct Committee
>
>

Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

[Tom Lane]

nikhil raj  writes:
> I've encountered a noticeable difference in execution time and query
> execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when
> running a query on information_schema tables. Surprisingly, PostgreSQL 16
> is performing slower than PostgreSQL 13.

Yeah, it looks like that condition on "table_name" is not getting
pushed down to the scan level anymore.  I'm not sure why not,
but will look closer tomorrow.

regards, tom lane

Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

[David Rowley]

On Tue, 27 Aug 2024 at 13:40, Tom Lane  wrote:
> Yeah, it looks like that condition on "table_name" is not getting
> pushed down to the scan level anymore.  I'm not sure why not,
> but will look closer tomorrow.

I was looking for the offending commit as at first I thought it might
be related to Memoize. It does not seem to be.

I get the following up until 2489d76c, and from then on, it's a subquery filter.

 ->  Index Scan using pg_class_relname_nsp_index on pg_class r_2
(cost=0.27..8.30 rows=1 width=8) (actual time=0.004..0.004 rows=0
loops=1)
Index Cond: (relname = 't_c56ng1_repository'::name)
Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND
pg_has_role(relowner, 'USAGE'::text))

So looks like it was the "Make Vars be outer-join-aware." commit that
changed this.

David

Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

[Tom Lane]

David Rowley  writes:
> On Tue, 27 Aug 2024 at 13:40, Tom Lane  wrote:
>> Yeah, it looks like that condition on "table_name" is not getting
>> pushed down to the scan level anymore.  I'm not sure why not,
>> but will look closer tomorrow.

> So looks like it was the "Make Vars be outer-join-aware." commit that
> changed this.

Yeah, I got that same result by bisecting.  It seems like it's
somehow related to the cast to information_schema.sql_identifier:
we are able to get rid of that normally but seem to fail to do so
in this query.

There was a smaller increase in the runtime at dfb75e478 "Add primary
keys and unique constraints to system catalogs", but that seems to
just be due to there being more rows in the relevant catalogs.
(That's from testing the query in an empty database; probably the
effect of dfb75e478 would be swamped in a production DB anyway.)

regards, tom lane

Re: Does a partition key need to be part of a composite index for the planner to take advantage of it? (PG 16.3+)

[Laurenz Albe]

On Tue, 2024-08-20 at 12:35 -0400, William Kaper wrote:
> We have a set of operational tables that are all partitioned by organization 
> ID
> (customer ID) in the 100M row range. We also have 3-4 composite indexes on 
> these
> tables that currently do not include the organization ID. Any queries that
> reference these tables always provide the organization ID as a discriminator. 
> 
> We recently started noticing that the query planner sequence scanning the 
> correct
> partitions, but is not using the indexes. So we decided to run a test by 
> creating
> a new set of composite indexes that mirror the existing ones but include
> organization_id as the first column in the composite index. When we create the
> composite index to include organization ID in the first position, then the 
> planner
> both selects the correct partitions, AND index scans those partitions. 
> 
> Is that expected behavior and it is appropriate to include any partition keys
> as leading columns in any indexes on a partitioned table?

I think it is hard to reason about this without seeing a concrete example and
the EXPLAIN (ANALYZE, BUFFERS) output for it.

Yours,
Laurenz Albe

Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

[Justin Clift]

On 2024-08-27 11:50, David Rowley wrote:

On Tue, 27 Aug 2024 at 13:40, Tom Lane  wrote:

Yeah, it looks like that condition on "table_name" is not getting
pushed down to the scan level anymore.  I'm not sure why not,
but will look closer tomorrow.


I was looking for the offending commit as at first I thought it might
be related to Memoize. It does not seem to be.


As a general thought, seeing that this might be an actual problem
should some kind of automated testing be added that checks for
performance regressions like this?

Regards and best wishes,

Justin Clift

logical replication - who is managing replication slots created automatically during initial sync

[Avi Weinberg]

Hi Experts

I have seen that logical replication slots created automatically by Postgres 
during initial sync (a slot per table), are marked as "wal_status = lost" and 
"active = false".
1.  Who is responsible for removing those faulty replication slots?
2.  Can a slot with "wal_status = lost" recover from this state?
3.  Do I need to drop the subscription in such a case?
4.  Are those replication slots that synchronize a single table each, use a 
connection from "max_connections" and replication slot from 
"max_logical_replication_slots"?
5.  If I sync many tables will it be better to increase the number of 
max_logical_replication_slots or to have some of the tables "wait" for other 
tables to complete and release the replication slot for them to use.

I'm using Postgres 15.2
slot_name   plugin  slot_type   datoid  databasetemporary   active  
active_pid  xmincatalog_xminrestart_lsn confirmed_flush_lsn wal_status  
safe_wal_size   two_phase
pg_8034820_sync_8033089_7371741997992267844 pgoutputlogical 16707   aaa_db  
FALSE   FALSE   NULLNULL295098502   NULLD9/EB7317B0 lostNULL
FALSE
pg_6839631_sync_6837833_7371741997992267844 pgoutputlogical 16707   aaa_db  
FALSE   FALSE   NULLNULL288349892   NULLD2/80068A78 lostNULL
FALSE

Thanks!

IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.