On Mon, 2025-02-17 at 07:55 +, 馬 騰飛 wrote:
> I am reaching out to seek your technical assistance regarding a performance
> issue we encountered after upgrading our PostgreSQL version from 12.19 to
> 16.3.
> We have noticed a significant performance problem with a specific SQL query on
> one of
Hi PostgreSQL Community,
I am currently exploring the behavior of pg_prewarm and encountered an
issue related to role
access rights that I was hoping you could help clarify.
Here is the scenario I observed:
postgres=# CREATE ROLE alpha;
CREATE ROLE
postgres=# GRANT SELECT ON pg_class TO alpha;
GR
On Monday, February 17, 2025, Ayush Vatsa wrote:
> postgres=# CREATE ROLE alpha;
>
> CREATE ROLE
> postgres=# GRANT SELECT ON pg_class TO alpha;
>
This is pointless, everyone (i.e. the PUBLIC pseudo-role) can already read
pg_class.
1. Can a role have access rights to a table without having acces
Jan Behrens writes:
> I wonder if it is guaranteed that when calling a set-returning function
> in a non-set-expecting context, the used row is guaranteed to be the
> first row returned.
In general, I'd expect either we'd use the first row or throw an
error. We're not 100% consistent about which
On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 wrote:
> Dear PostgreSQL Community,
>
> I hope this message finds you well. I am reaching out to seek your
> technical assistance regarding a performance issue we encountered after
> upgrading our PostgreSQL version from 12.19 to 16.3.
> We have noticed a sign
post installing, did you run analyze verbose?
-Doron
On Mon, Feb 17, 2025 at 4:23 PM Greg Sabino Mullane
wrote:
> On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 wrote:
>
>> Interestingly, when we isolate the problematic SQL statement and replace
>> its parameters with actual values, it executes in just
On 2/16/25 23:55, 馬 騰飛 wrote:
Dear PostgreSQL Community,
I hope this message finds you well. I am reaching out to seek your technical
assistance regarding a performance issue we encountered after upgrading our
PostgreSQL version from 12.19 to 16.3.
We have noticed a significant performance pro
Hi,
I wonder if it is guaranteed that when calling a set-returning function
in a non-set-expecting context, the used row is guaranteed to be the
first row returned.
I.e. if I have the following function definition
CREATE FUNCTION foo() RETURNS INTEGER RETURN generate_series(1, 10);
is it then g
On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 wrote:
> Interestingly, when we isolate the problematic SQL statement and replace
> its parameters with actual values, it executes in just a few seconds in
> pgAdmin.
> However, when we run the same SQL query through our application using
> Npgsql, it takes ov
> This is pointless, everyone (i.e. the PUBLIC pseudo-role) can already
read pg_class.
True, Just checked that.
> It fails because AFAICS there is no way for it to work on an index, only
tables.
pg_prewarm extension works on index if we have right (SELECT) privileges
postgres=# CREATE TABLE x(id I
On Monday, February 17, 2025, Tom Lane wrote:
> Ayush Vatsa writes:
> > postgres=> SELECT pg_prewarm('pg_class_oid_index');
> > ERROR: permission denied for index pg_class_oid_index
>
> You'd really have to take that up with the author of pg_prewarm.
This is our contrib module so this seems l
Ayush Vatsa writes:
> postgres=> SELECT pg_prewarm('pg_class_oid_index');
> ERROR: permission denied for index pg_class_oid_index
You'd really have to take that up with the author of pg_prewarm.
It's not apparent to me why checking SQL access permissions is
the right mechanism for limiting use o
Hi Michael,
I first use initdb, and set wal_log_hints=off, data_checksums=off, and
full_page_writes=on. Starting pg and running for a while.
Then switch over happened, I used the following commands:
1. Old master postgresql.conf set wal_log_hints=on, then start and stop pg.
2. using pg_rewind --
PG 9.6.24 and PG 14.15, if it matters.
(Yes, 9.6 is really EOL. I don't control that.)
(I could use pg_prewarm, but the table is much bigger than RAM, and
last_block value only has the newest record if data has never been
deleted. The oldest records regularly get deleted, and then the table is
v
## Ron Johnson (ronljohnso...@gmail.com):
> Loading 200K rows is more than 200MB. I expected this "prewarm" statement
> to take much longer than 1/2 second. Am I still in the dark ages of
> computer speed, or is this statement not doing what I hope it's doing?
>
> $ time psql -h foo bar -Xc "DO
Ron Johnson writes:
> The bigint "id" column in "mytbl" is populated from a sequence, and so is
> monotonically increasing: the newest records will have the biggest id
> values.
> The table also has a bytea column that averages about 100KB.
> Loading 200K rows is more than 200MB. I expected this
On Mon, Feb 17, 2025 at 4:36 PM Tom Lane wrote:
> Ron Johnson writes:
> > The bigint "id" column in "mytbl" is populated from a sequence, and so is
> > monotonically increasing: the newest records will have the biggest id
> > values.
> > The table also has a bytea column that averages about 100K
Thank you for your help, Tom.
You are right. I added an index on employee.name (by making it unique), and
then postgres can visit employee table in a pre-sorted manner, and can exit
early without joining more rows.
Just sharing the tweak I did to the example, if anyone else is interested
in a qu
On Mon, 2025-02-17 at 23:31 +0530, Ayush Vatsa wrote:
> postgres=> SELECT pg_prewarm('pg_class_oid_index');
> ERROR: permission denied for index pg_class_oid_index
> postgres=> RESET ROLE;
> RESET
>
> postgres=# GRANT SELECT ON pg_class_oid_index TO alpha;
> ERROR: "pg_class_oid_index" is an ind
On Mon, Feb 17, 2025 at 4:51 PM Tom Lane wrote:
> Ron Johnson writes:
> > On Mon, Feb 17, 2025 at 4:36 PM Tom Lane wrote:
> >> It's not pulling in the TOAST storage where the bytea column lives.
> >> (pg_prewarm wouldn't have either, without special pushups.)
>
> > Puzzling, since I ran "PERFOR
Ron Johnson writes:
> On Mon, Feb 17, 2025 at 4:36 PM Tom Lane wrote:
>> It's not pulling in the TOAST storage where the bytea column lives.
>> (pg_prewarm wouldn't have either, without special pushups.)
> Puzzling, since I ran "PERFORM *". What if I explicitly mentioned the
> bytea column's na
On Mon, Feb 17, 2025 at 2:41 PM Ron Johnson wrote:
> On Mon, Feb 17, 2025 at 4:36 PM Tom Lane wrote:
>
>> Ron Johnson writes:
>> > The bigint "id" column in "mytbl" is populated from a sequence, and so
>> is
>> > monotonically increasing: the newest records will have the biggest id
>> > values.
"David G. Johnston" writes:
> On Monday, February 17, 2025, Tom Lane wrote:
>> You'd really have to take that up with the author of pg_prewarm.
> This is our contrib module so this seems like the expected place to ask
> such a question. It’s neither a bug nor a topic for -hackers. FTR, Robert
23 matches
Mail list logo
