public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedhow to switch user in postgres
6+ messages / 6 participants
[nested] [flat]
* how to switch user in postgres
@ 2026-04-11 17:55 ikramuddin <ikram.amani815@gmail.com>
2026-04-11 18:00 ` Re: how to switch user in postgres Pavel Stehule <pavel.stehule@gmail.com>
2026-04-11 18:33 ` Re: how to switch user in postgres David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: ikramuddin @ 2026-04-11 17:55 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
hi team,
As i was working through postgresql user and role privileges i faced an
unexpected behavious when we revoke connect on database from a user then
why still we need to revoke usage on schema basis and then revoke select ,
insert , update privileges at table table. second when i did all this one
by one and then tried to connect to same database using \c database_name
user_name, it failed that is perfect which i expected but when i tried to
connect through postgres as a superuser it connect still it is fine. but
then i write the command set role simon it connected even i revoked the
connect privileges from the role . plz guide it is a bug or this
behavious left intentionally.
finance=> SET ROLE postgres;
SET
finance=# REVOKE CONNECT ON DATABASE finance FROM simon;
REVOKE
finance=# set role simon;
SET
finance=> SELECT * FROM accounting.invoices;
invoice_id | invoice_date | amount
------------+--------------+--------
1 | 2024-03-15 | 250.50
2 | 2024-01-20 | 110.99
3 | 2024-03-29 | 1000
(3 rows)
finance=> SET ROLE postgres;
SET
finance=# REVOKE CONNECT ON DATABASE finance FROM simon; REVOKE USAGE ON
SCHEMA accounting FROM simon; REVOKE SELECT, INSERT, UPDATE, DELETE ON
accounting.invoices FROM simon;
REVOKE
REVOKE
REVOKE
finance=# CREATE ROLE accounting_ro NOLOGIN; GRANT CONNECT ON DATABASE
finance TO accounting_ro; GRANT USAGE ON SCHEMA accounting TO
accounting_ro; GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO
accounting_ro;
CREATE ROLE
GRANT
GRANT
GRANT
finance=# CREATE TABLE accounting.customers( customer_id serial PRIMARY
KEY, name TEXT, address TEXT );
CREATE TABLE
finance=# SET ROLE simon;
SET
finance=> select current_user;
current_user
--------------
simon
(1 row)
finance=> reset role'
finance'> ;
finance'> ';
ERROR: syntax error at or near "'
;
'"
LINE 1: reset role'
^
finance=> reset role;
RESET
finance=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype
| Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+--------------------------
finance | postgres | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | | =Tc/postgres +
| | | | |
| | | postgres=CTc/postgres +
| | | | |
| | | accounting_ro=c/postgres
postgres | postgres | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | | =c/postgres +
| | | | |
| | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | | =c/postgres +
| | | | |
| | | postgres=CTc/postgres
(4 rows)
finance=# revoke connect on database finance from simon;
REVOKE
finance=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# revoke connect on database finance from simon;
REVOKE
postgres=# \c finance simon;
connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed:
FATAL: Peer authentication failed for user "simon"
Previous connection kept
postgres=# \c finance
You are now connected to database "finance" as user "postgres".
finance=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \c finance postgres
You are now connected to database "finance" as user "postgres".
finance=#
finance=# set role simon
finance-# ;
SET
finance=> select current_role;
current_role
--------------
simon
(1 row)
finance=> SELECT * FROM accounting.invoices;
ERROR: permission denied for schema accounting
LINE 1: SELECT * FROM accounting.invoices;
^
finance=> ^C
thanks and regards
Ikramuddin Database lead.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to switch user in postgres
2026-04-11 17:55 how to switch user in postgres ikramuddin <ikram.amani815@gmail.com>
@ 2026-04-11 18:00 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2026-04-11 18:00 UTC (permalink / raw)
To: ikramuddin <ikram.amani815@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi
so 11. 4. 2026 v 19:55 odesílatel ikramuddin <ikram.amani815@gmail.com>
napsal:
> hi team,
> As i was working through postgresql user and role privileges i faced an
> unexpected behavious when we revoke connect on database from a user then
> why still we need to revoke usage on schema basis and then revoke select ,
> insert , update privileges at table table. second when i did all this one
> by one and then tried to connect to same database using \c database_name
> user_name, it failed that is perfect which i expected but when i tried to
> connect through postgres as a superuser it connect still it is fine. but
> then i write the command set role simon it connected even i revoked the
> connect privileges from the role . plz guide it is a bug or this
> behavious left intentionally.
> finance=> SET ROLE postgres;
> SET
> finance=# REVOKE CONNECT ON DATABASE finance FROM simon;
> REVOKE
> finance=# set role simon;
> SET
> finance=> SELECT * FROM accounting.invoices;
> invoice_id | invoice_date | amount
> ------------+--------------+--------
> 1 | 2024-03-15 | 250.50
> 2 | 2024-01-20 | 110.99
> 3 | 2024-03-29 | 1000
> (3 rows)
>
> finance=> SET ROLE postgres;
> SET
> finance=# REVOKE CONNECT ON DATABASE finance FROM simon; REVOKE USAGE ON
> SCHEMA accounting FROM simon; REVOKE SELECT, INSERT, UPDATE, DELETE ON
> accounting.invoices FROM simon;
> REVOKE
> REVOKE
> REVOKE
> finance=# CREATE ROLE accounting_ro NOLOGIN; GRANT CONNECT ON DATABASE
> finance TO accounting_ro; GRANT USAGE ON SCHEMA accounting TO
> accounting_ro; GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO
> accounting_ro;
> CREATE ROLE
> GRANT
> GRANT
> GRANT
> finance=# CREATE TABLE accounting.customers( customer_id serial PRIMARY
> KEY, name TEXT, address TEXT );
> CREATE TABLE
> finance=# SET ROLE simon;
> SET
> finance=> select current_user;
> current_user
> --------------
> simon
> (1 row)
>
> finance=> reset role'
> finance'> ;
> finance'> ';
> ERROR: syntax error at or near "'
>
there is no RESET role command
you can use `SET ROLE TO DEFAULT` instead
Regards
Pavel
;
> '"
> LINE 1: reset role'
> ^
> finance=> reset role;
> RESET
> finance=# \l
> List of databases
> Name | Owner | Encoding | Locale Provider | Collate |
> Ctype | Locale | ICU Rules | Access privileges
>
> -----------+----------+----------+-----------------+-------------+-------------+--------+-----------+--------------------------
> finance | postgres | UTF8 | libc | en_US.UTF-8 |
> en_US.UTF-8 | | | =Tc/postgres +
> | | | | |
> | | | postgres=CTc/postgres +
> | | | | |
> | | | accounting_ro=c/postgres
> postgres | postgres | UTF8 | libc | en_US.UTF-8 |
> en_US.UTF-8 | | |
> template0 | postgres | UTF8 | libc | en_US.UTF-8 |
> en_US.UTF-8 | | | =c/postgres +
> | | | | |
> | | | postgres=CTc/postgres
> template1 | postgres | UTF8 | libc | en_US.UTF-8 |
> en_US.UTF-8 | | | =c/postgres +
> | | | | |
> | | | postgres=CTc/postgres
> (4 rows)
>
> finance=# revoke connect on database finance from simon;
> REVOKE
> finance=# \c postgres
> You are now connected to database "postgres" as user "postgres".
> postgres=# revoke connect on database finance from simon;
> REVOKE
> postgres=# \c finance simon;
> connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed:
> FATAL: Peer authentication failed for user "simon"
> Previous connection kept
> postgres=# \c finance
> You are now connected to database "finance" as user "postgres".
> finance=# \c postgres
> You are now connected to database "postgres" as user "postgres".
> postgres=# \c finance postgres
> You are now connected to database "finance" as user "postgres".
> finance=#
> finance=# set role simon
> finance-# ;
> SET
> finance=> select current_role;
> current_role
> --------------
> simon
> (1 row)
>
> finance=> SELECT * FROM accounting.invoices;
> ERROR: permission denied for schema accounting
> LINE 1: SELECT * FROM accounting.invoices;
> ^
> finance=> ^C
>
> thanks and regards
> Ikramuddin Database lead.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to switch user in postgres
2026-04-11 17:55 how to switch user in postgres ikramuddin <ikram.amani815@gmail.com>
@ 2026-04-11 18:33 ` David G. Johnston <david.g.johnston@gmail.com>
2026-04-11 19:29 ` Re: how to switch user in postgres Tom Lane <tgl@sss.pgh.pa.us>
2026-04-27 15:52 ` Query performance Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>
1 sibling, 2 replies; 6+ messages in thread
From: David G. Johnston @ 2026-04-11 18:33 UTC (permalink / raw)
To: ikramuddin <ikram.amani815@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
This seems quite misplaced on the -performance mailing list.
On Sat, Apr 11, 2026 at 10:55 AM ikramuddin <ikram.amani815@gmail.com>
wrote:
>
> As i was working through postgresql user and role privileges i faced an
> unexpected behavious when we revoke connect on database from a user then
> why still we need to revoke usage on schema basis and then revoke select ,
> insert , update privileges at table table. second when i did all this one
> by one and then tried to connect to same database using \c database_name
> user_name, it failed that is perfect which i expected but when i tried to
> connect through postgres as a superuser it connect still it is fine. but
> then i write the command set role simon it connected even i revoked the
> connect privileges from the role .
>
> plz guide it is a bug or this behavious left intentionally.
>
Intentional, every object has its own permissions that are granted to roles
independently of others.
> finance=> reset role'
> finance'> ;
> finance'> ';
> ERROR: syntax error at or near "'
> ;
> '"
> LINE 1: reset role'
>
^
> finance=> reset role;
> RESET
>
You had a typo in the first attempt...
For everything else I really don't feel like trying to read your mind. If
you have questions about specific outcomes or whatnot please ask them
directly. But there are no known bugs here and I presume you haven't
demonstrated any - everything is working as intended.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: how to switch user in postgres
2026-04-11 17:55 how to switch user in postgres ikramuddin <ikram.amani815@gmail.com>
2026-04-11 18:33 ` Re: how to switch user in postgres David G. Johnston <david.g.johnston@gmail.com>
@ 2026-04-11 19:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2026-04-11 19:29 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: ikramuddin <ikram.amani815@gmail.com>; pgsql-performance@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> This seems quite misplaced on the -performance mailing list.
Indeed.
> On Sat, Apr 11, 2026 at 10:55 AM ikramuddin <ikram.amani815@gmail.com>
> wrote:
>> plz guide it is a bug or this behavious left intentionally.
> Intentional, every object has its own permissions that are granted to roles
> independently of others.
Also, "REVOKE CONNECT ON DATABASE finance FROM simon" is probably a
no-op, because nobody ever did "GRANT CONNECT ON DATABASE finance TO
simon". Rather, the reason simon can connect is that there's a
default "GRANT CONNECT ... TO public". If you want to restrict
CONNECT privileges, you have to revoke that and then hand out
the privilege selectively to users that should have it.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Query performance
2026-04-11 17:55 how to switch user in postgres ikramuddin <ikram.amani815@gmail.com>
2026-04-11 18:33 ` Re: how to switch user in postgres David G. Johnston <david.g.johnston@gmail.com>
@ 2026-04-27 15:52 ` Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>
2026-04-28 05:33 ` Re: Query performance Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 6+ messages in thread
From: Dirschel, Steve-CW @ 2026-04-27 15:52 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Aurora Postgres version 17.4.
Table in question:
\d poslog_publisher_rms_stage
Table "public.poslog_publisher_rms_stage"
Column | Type | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
stage_id | uuid | | not null |
status | character varying(100) | | |
message_body | text | | not null |
error_code | character varying(100) | | |
error_category | character varying(100) | | |
error_message | text | | |
error_retry_count | integer | | | 0
create_date | timestamp with time zone | | not null | now()
modified_date | timestamp with time zone | | not null | now()
Indexes:
"poslog_publisher_rms_stage_pkey" PRIMARY KEY, btree (stage_id)
"idx_poslog_publisher_stage_create_date_col" btree (create_date)
"idx_poslog_publisher_stage_status_error_retry_count_modi_date_c" btree (status, error_retry_count, modified_date)
Referenced by:
TABLE "poslog_publisher_rms_detail" CONSTRAINT "fk_poslog_publisher_detail_stage_id" FOREIGN KEY (stage_id) REFERENCES poslog_publisher_rms_stage(stage_id)
Publications:
“sashpsrms_publication"
The table is constantly getting loaded into. Rows are inserted with a status ready and then there is a different process looking for that status and will update to processed after processing the row. We have multiple tables like this.
Then every 2 hours a different process runs this query looking for failed or unprocessed rows:
select
ppse.stage_id as stageId,
ppse.status as status,
ppse.message_body as messageBody
from
poslog_publisher_rms_stage ppse
where
ppse.status in ('UNPROCESSED','FAILED')
and ppse.error_retry_count < 3
order by
ppse.create_date
limit 100;
If I run that query with explain it is doing a ton of work to find 0 rows. The index it uses seems appropriate for the query.
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.823..66566.824 rows=0 loops=1)
Buffers: shared hit=1509768 read=2011479
I/O Timings: shared read=79792.017
-> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.821..66566.821 rows=0 loops=1)
Sort Key: create_date
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1509768 read=2011479
I/O Timings: shared read=79792.017
-> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=66566.761..66566.761 rows=0 loops=1)
Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
Buffers: shared hit=1509765 read=2011479
I/O Timings: shared read=79792.017
Planning:
Buffers: shared hit=195 read=1
I/O Timings: shared read=1.038
Planning Time: 2.909 ms
Execution Time: 66581.498 ms
The query did 3.5 million block reads when scanning the index of which 1.5 million were in memory and 2 million were from disk. 5 seconds later I ran the exact same query again:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=23.589..23.591 rows=0 loops=1)
Buffers: shared hit=18736
-> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=23.588..23.589 rows=0 loops=1)
Sort Key: create_date
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=18736
-> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=23.583..23.584 rows=0 loops=1)
Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
Buffers: shared hit=18736
Planning Time: 0.118 ms
Execution Time: 23.628 ms
Now it only did 18k block reads all in memory. It used the same index, it also returned 0 rows. Between those 2 runs I looked at pg_stat_user_tables and could see the n_tup_ins increased by 13, n_tup_del increased by 13, n_live_tup increased by 13, and n_dead_tup increased by 13. n_live_tup was 96 million and n_dead_tup was 8.4 million. 18k logical reads to find 0 rows is still high but I believe that is most likely caused by the 8.4 million n_dead_tups.
15 minutes later I ran the query again:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=59431.795..59431.796 rows=0 loops=1)
Buffers: shared hit=1286676 read=1734000
I/O Timings: shared read=70153.578
-> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=59431.794..59431.794 rows=0 loops=1)
Sort Key: create_date
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1286676 read=1734000
I/O Timings: shared read=70153.578
-> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=59431.789..59431.790 rows=0 loops=1)
Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
Buffers: shared hit=1286676 read=1734000
I/O Timings: shared read=70153.578
Planning Time: 0.114 ms
Execution Time: 59431.839 ms
Total blocks reads increased from 18k to 3 million. n_tup_ins, n_tup_del, n_live_tup, and n_dead_tup all increased by 10,320 over that 15 minute period of time.
What is going on here where this query has to do 3+ million block reads to find 0 rows? And how is it possible when I run the query 2 times in a row the logical reads from the 2nd run comes down significantly? Is this somehow related to determining if rows are visible or something like that? When I waited 15 minutes between runs the inserted/updated rows only increased by 10.3k yet total block reads increased by 3 million.
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance
2026-04-11 17:55 how to switch user in postgres ikramuddin <ikram.amani815@gmail.com>
2026-04-11 18:33 ` Re: how to switch user in postgres David G. Johnston <david.g.johnston@gmail.com>
2026-04-27 15:52 ` Query performance Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>
@ 2026-04-28 05:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2026-04-28 05:33 UTC (permalink / raw)
To: Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Mon, 2026-04-27 at 15:52 +0000, Dirschel, Steve-CW wrote:
> Aurora Postgres version 17.4.
Aurora works quite differently, as far as storage is concerned, so you may be
suffering from some peculiarity of that closed source software.
> Table in question:
>
>
> \d poslog_publisher_rms_stage
> Table "public.poslog_publisher_rms_stage"
> Column | Type | Collation | Nullable | Default
> -------------------+--------------------------+-----------+----------+---------
> stage_id | uuid | | not null |
> status | character varying(100) | | |
> message_body | text | | not null |
> error_code | character varying(100) | | |
> error_category | character varying(100) | | |
> error_message | text | | |
> error_retry_count | integer | | | 0
> create_date | timestamp with time zone | | not null | now()
> modified_date | timestamp with time zone | | not null | now()
> Indexes:
> "poslog_publisher_rms_stage_pkey" PRIMARY KEY, btree (stage_id)
> "idx_poslog_publisher_stage_create_date_col" btree (create_date)
> "idx_poslog_publisher_stage_status_error_retry_count_modi_date_c" btree (status, error_retry_count, modified_date)
> Referenced by:
> TABLE "poslog_publisher_rms_detail" CONSTRAINT "fk_poslog_publisher_detail_stage_id" FOREIGN KEY (stage_id) REFERENCES poslog_publisher_rms_stage(stage_id)
> Publications:
> “sashpsrms_publication"
>
> The table is constantly getting loaded into. Rows are inserted with a status ready and
> then there is a different process looking for that status and will update to processed
> after processing the row. We have multiple tables like this.
>
> Then every 2 hours a different process runs this query looking for failed or unprocessed rows:
>
> select
> ppse.stage_id as stageId,
> ppse.status as status,
> ppse.message_body as messageBody
> from
> poslog_publisher_rms_stage ppse
> where
> ppse.status in ('UNPROCESSED','FAILED')
> and ppse.error_retry_count < 3
> order by
> ppse.create_date
> limit 100;
>
> If I run that query with explain it is doing a ton of work to find 0 rows. The index it
> uses seems appropriate for the query.
The index can be used, but it is far from perfect. The ideal index would be:
CREATE INDEX ON poslog_publisher_rms_stage (create_date)
WHERE status in ('UNPROCESSED','FAILED') AND error_retry_count < 3;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.823..66566.824 rows=0 loops=1)
> Buffers: shared hit=1509768 read=2011479
> I/O Timings: shared read=79792.017
> -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=66566.821..66566.821 rows=0 loops=1)
> Sort Key: create_date
> Sort Method: quicksort Memory: 25kB
> Buffers: shared hit=1509768 read=2011479
> I/O Timings: shared read=79792.017
> -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=66566.761..66566.761 rows=0 loops=1)
> Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
> Buffers: shared hit=1509765 read=2011479
> I/O Timings: shared read=79792.017
> Planning:
> Buffers: shared hit=195 read=1
> I/O Timings: shared read=1.038
> Planning Time: 2.909 ms
> Execution Time: 66581.498 ms
>
> The query did 3.5 million block reads when scanning the index of which 1.5 million were
> in memory and 2 million were from disk. 5 seconds later I ran the exact same query again:
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=10.92..10.93 rows=1 width=1019) (actual time=23.589..23.591 rows=0 loops=1)
> Buffers: shared hit=18736
> -> Sort (cost=10.92..10.93 rows=1 width=1019) (actual time=23.588..23.589 rows=0 loops=1)
> Sort Key: create_date
> Sort Method: quicksort Memory: 25kB
> Buffers: shared hit=18736
> -> Index Scan using idx_poslog_publisher_stage_status_error_retry_count_modi_date_c on poslog_publisher_rms_stage ppse (cost=0.57..10.91 rows=1 width=1019) (actual time=23.583..23.584 rows=0 loops=1)
> Index Cond: (((status)::text = ANY ('{UNPROCESSED,FAILED}'::text[])) AND (error_retry_count < 3))
> Buffers: shared hit=18736
> Planning Time: 0.118 ms
> Execution Time: 23.628 ms
>
> Now it only did 18k block reads all in memory. It used the same index, it also
> returned 0 rows. Between those 2 runs I looked at pg_stat_user_tables and could see the
> n_tup_ins increased by 13, n_tup_del increased by 13, n_live_tup increased by 13, and
> n_dead_tup increased by 13. n_live_tup was 96 million and n_dead_tup was 8.4 million.
> 18k logical reads to find 0 rows is still high but I believe that is most likely caused
> by the 8.4 million n_dead_tups.
>
> What is going on here where this query has to do 3+ million block reads to find 0 rows?
> And how is it possible when I run the query 2 times in a row the logical reads from the
> 2nd run comes down significantly? Is this somehow related to determining if rows are
> visible or something like that?
The only way I can imagine this happening in PostgreSQL is if the first execution
kills a lot of index tuples (https://www.cybertec-postgresql.com/en/killed-index-tuples/).
> When I waited 15 minutes between runs the inserted/updated rows only increased by
> 10.3k yet total block reads increased by 3 million.
Now that seems to speak against the above theory, so you are suffering from some Amazon-
specific behavior.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-04-28 05:33 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-11 17:55 how to switch user in postgres ikramuddin <ikram.amani815@gmail.com>
2026-04-11 18:00 ` Pavel Stehule <pavel.stehule@gmail.com>
2026-04-11 18:33 ` David G. Johnston <david.g.johnston@gmail.com>
2026-04-11 19:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-27 15:52 ` Query performance Dirschel, Steve-CW <Steve.Dirschel@bestbuy.com>
2026-04-28 05:33 ` Re: Query performance Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox