PgSQL 15.3: Execution plan not using index as expected

[Dürr Software]

Dear list,

i have a strange problem when migrating a DB from version 9.3.4 to 15.3:
An index which seems perfect for the query and is used in 9.3.4 as 
expected is not used in 15.3.

I just wonder, whether the bug is on my side or on PgSQL's..
The details:
===
PostgreSQL 9.3.4:

test=# select version();
version
--
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
4.8.3-2) 4.8.3, 64-bit

(1 row)

test=> \d client_session
    Table "client_session"
    Column |  Type | Modifiers
---++---
 id    | bigint | not null default 
nextval('client_session_id_seq'::regclass)

 tstamp_start  | timestamp(3) without time zone | not null default now()
 permit_id | character varying(63)  | not null default 
"current_user"()
 user_id   | character varying(63)  | not null default 
"session_user"()

Indexes:
    "client_session_pkey" PRIMARY KEY, btree (id)
    "client_session_user_id_idx" btree (user_id, tstamp_start DESC)

vdws=# explain analyze SELECT permit_id FROM client_session WHERE 
user_id=SESSION_USER::VARCHAR ORDER BY tstamp_start DESC LIMIT 1;

QUERY PLAN
--
 Limit  (cost=0.57..2.37 rows=1 width=23) (actual time=0.134..0.134 
rows=1 loops=1)
   ->  Index Scan using client_session_user_id_idx on client_session  
(cost=0.57..52337.99 rows=29181 width=23) (actual time=0.133..0.133 
rows=1 loops=1)
 Index Cond: ((user_id)::text = (("session_user"())::character 
varying)::text)

 Total runtime: 0.165 ms
(4 rows)

===
PostgreSQL 15.3:

test=# select version();
version
-
 PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

(1 Zeile)

test=# \d client_session
  Tabelle »client_session«
    Spalte |  Typ   | Sortierfolge | NULL 
erlaubt? |   Vorgabewert

---++--+---+--
 id    | bigint |  | not 
null  | nextval('client_session_id_seq'::regclass)
 tstamp_start  | timestamp(3) without time zone |  | not 
null  | now()
 permit_id | character varying(63)  |  | not 
null  | "current_user"()
 user_id   | character varying(63)  |  | not 
null  | "session_user"()

Indexe:
    "client_session_pkey" PRIMARY KEY, btree (id)
    "client_session_user_id_idx" btree (user_id, tstamp_start DESC)

test=# explain analyze SELECT permit_id FROM client_session WHERE 
user_id=SESSION_USER::VARCHAR ORDER BY tstamp_start DESC LIMIT 1;

QUERY PLAN
--
 Limit  (cost=3778568.38..3778568.50 rows=1 width=152) (actual 
time=8431.320..8437.169 rows=1 loops=1)
   ->  Gather Merge  (cost=3778568.38..3853392.64 rows=641306 
width=152) (actual time=8383.774..8389.622 rows=1 loops=1)

 Workers Planned: 2
 Workers Launched: 2
 ->  Sort  (cost=3777568.36..3778369.99 rows=320653 width=152) 
(actual time=8372.263..8372.263 rows=0 loops=3)

   Sort Key: tstamp_start DESC
   Sort Method: quicksort  Memory: 25kB
   Worker 0:  Sort Method: quicksort  Memory: 25kB
   Worker 1:  Sort Method: quicksort  Memory: 25kB
   ->  Parallel Seq Scan on client_session 
(cost=0.00..3775965.09 rows=320653 width=152) (actual 
time=6150.412..8372.191 rows=1 loops=3)
 Filter: ((user_id)::text = 
((SESSION_USER)::character varying)::text)

 Rows Removed by Filter: 51303778
 Planning Time: 0.203 ms
 JIT:
   Functions: 13
   Options: Inlining true, Optimization true, Expressions true, 
Deforming true
   Timing: Generation 1.644 ms, Inlining 120.073 ms, Optimization 
70.361 ms, Emission 28.476 ms, Total 220.554 ms

 Execution Time: 8438.307 ms
(18 rows)

Thanks a lot for your help

--
==
Dürr Software Entw.
Guggenberg 26, DE-82380 Peißenberg
fon: +49-8803-4899016  fax: +49-8803-4899017
i...@fduerr.de
begin:vcard
fn;quoted-printable:

How to set default privilege for new users to have no access to other databases?

[Erik Nelson]

I have a lab with a database that I would like to use as a "multi-tenant"
database, in that I would like to create a database for each of the
applications that I'm running and segregate access so that user foo and
user bar cannot see anything about their neighbors. I'm somewhat surprised
to discover that any new user, *by default*, has the ability to list
databases, connect to them, and list their tables.

My understanding is that this ability is inherited from the public role
(could use confirmation of this)? I can think of two potential options, one
being more desirable:

   - I know I can revoke CONNECT from an explicit database, but this
   requires that I specify the database. I want to revoke this for all
   current, *and future* databases as the default privilege.
   - I could potentially create users with the NOINHERIT attribute
   (assuming this ability is inherited from public), but I don't think that's
   ideal because a new user could easily be created without this, and it would
   have access.

Is this not something to be concerned about because even if a user connects
to a database, they can't really do anything inside of it?

Re: PgSQL 15.3: Execution plan not using index as expected

[Adrian Klaver]

On 8/9/23 01:14, Dürr Software wrote:

Dear list,

i have a strange problem when migrating a DB from version 9.3.4 to 15.3:
An index which seems perfect for the query and is used in 9.3.4 as 
expected is not used in 15.3.


Did you run ANALYZE on the 15.3 database after the migration?


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

Re: How to set default privilege for new users to have no access to other databases?

[David G. Johnston]

On Wednesday, August 9, 2023, Erik Nelson  wrote:

> I have a lab with a database that I would like to use as a "multi-tenant"
> database, in that I would like to create a database for each of the
> applications that I'm running and segregate access so that user foo and
> user bar cannot see anything about their neighbors. I'm somewhat surprised
> to discover that any new user, *by default*, has the ability to list
> databases
>
This cannot be prevented.


> , connect to them
>

https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html



> and list their tables.
>
Requires being connected to the database being inspected.



> My understanding is that this ability is inherited from the public role
> (could use confirmation of this)?
>
Yes, public is what gets the default connection grant to newly created
databases.

David J.

pb with big volumes

[Marc Millas]

Hi,

I have a 15 TB db on postgres 14 (soon 15).
shared buffers is 32 GB.

It's a db with max 15 users and often less, and currently 1 or 2.
the biggest table have 133 partitions of 150M to 200M+ rows each.
lots of request access explicitly one of those.

When I, alone, run a query "reading" 15M buffers, it takes 20 minutes
(+-5minutes). inside the query there are  2 group by on a 200M rows
partition, with all the rows in each group by.
When a colleague run the same kind of request (not the same request, but
something reading roughly the same volume ) , on a different set of data,
 his request is completed in less than half an hour.
If we run our requests simultaneously... my request take hours. around 3
hours.

I am making a supposition that its some kind of "pumping" effect in the
cache.

I cannot have access to the underlying OS. I can, for sure, do some copy xx
from program 'some command',  but its a container with very limited
possibilities, not even 'ps'.
So I would like to monitor from inside the db (so without iostat and the
same)  the volumes of read that postgres do to the OS.
I did activate track_io_timing, but the volumes I get in the explain
analyze buffer are roughly the same alone or not alone. (the 15M buffers
told )
to my understanding, the volumes that are shown in pg_stat_database are the
useful ones ie. even if the db as to read it from disk more than once. true
? or false ?

So.. either my supposition is not correct, and I will read with a lot of
interest other ideas
either its correct and I would like to know how to monitor this (in the
current context, installing a dedicated extension is not impossible, but is
a very boring process)

Thanks for your help :-)

regards,

PS: I know that providing the complete data model and the exact requests
can be considered mandatory, but when I change the request I get the very
same behaviour...



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: pb with big volumes

[Ron]

On 8/10/23 16:36, Marc Millas wrote:

Hi,

I have a 15 TB db on postgres 14 (soon 15).
shared buffers is 32 GB.


Does the system have 128GB AM?



It's a db with max 15 users and often less, and currently 1 or 2.
the biggest table have 133 partitions of 150M to 200M+ rows each.
lots of request access explicitly one of those.

When I, alone, run a query "reading" 15M buffers, it takes 20 minutes 
(+-5minutes). inside the query there are  2 group by on a 200M rows 
partition, with all the rows in each group by.
When a colleague run the same kind of request (not the same request, but 
something reading roughly the same volume ) , on a different set of data,  
 his request is completed in less than half an hour.
If we run our requests simultaneously... my request take hours. around 3 
hours.


I am making a supposition that its some kind of "pumping" effect in the 
cache.


What is work_mem set to?

When were the tables last vacuumed and analyzed?

Good index support?  (But that might not matter if every row in the table is 
in the GROUP BY.)


Clustering the tables, and using BRIN indices might help.



I cannot have access to the underlying OS. I can, for sure, do some copy 
xx from program 'some command',  but its a container with very limited 
possibilities, not even 'ps'.
So I would like to monitor from inside the db (so without iostat and the 
same)  the volumes of read that postgres do to the OS.
I did activate track_io_timing, but the volumes I get in the explain 
analyze buffer are roughly the same alone or not alone. (the 15M buffers 
told )
to my understanding, the volumes that are shown in pg_stat_database are 
the useful ones ie. even if the db as to read it from disk more than once. 
true ? or false ?


So.. either my supposition is not correct, and I will read with a lot of 
interest other ideas
either its correct and I would like to know how to monitor this (in the 
current context, installing a dedicated extension is not impossible, but 
is a very boring process)


Thanks for your help :-)

regards,

PS: I know that providing the complete data model and the exact requests 
can be considered mandatory, but when I change the request I get the very 
same behaviour...




--
Born in Arizona, moved to Babylonia.

Re: pb with big volumes

[Adam Scott]

I think your concern is that 20 min + 30 min does not equal 3 hours.

It might be natural to think the contention would, at max, be 50 min x 2 (1
hr 40 min).

So what's going on?

It seems disk I/O  is a primary suspect since you hint for an iostat
replacement inside of Postgres.

If it is due to disk I/O the resolution will be to add RAID 0 SSDs at best.

Consider looking at io stats on the container's persistent volumes.

What is the pipe connecting the database server to the disks?  If it's NAS,
well that would explain it.

HTH,
Adam



On Thu, Aug 10, 2023 at 2:37 PM Marc Millas  wrote:

> Hi,
>
> I have a 15 TB db on postgres 14 (soon 15).
> shared buffers is 32 GB.
>
> It's a db with max 15 users and often less, and currently 1 or 2.
> the biggest table have 133 partitions of 150M to 200M+ rows each.
> lots of request access explicitly one of those.
>
> When I, alone, run a query "reading" 15M buffers, it takes 20 minutes
> (+-5minutes). inside the query there are  2 group by on a 200M rows
> partition, with all the rows in each group by.
> When a colleague run the same kind of request (not the same request, but
> something reading roughly the same volume ) , on a different set of data,
>  his request is completed in less than half an hour.
> If we run our requests simultaneously... my request take hours. around 3
> hours.
>
> I am making a supposition that its some kind of "pumping" effect in the
> cache.
>
> I cannot have access to the underlying OS. I can, for sure, do some copy
> xx from program 'some command',  but its a container with very limited
> possibilities, not even 'ps'.
> So I would like to monitor from inside the db (so without iostat and the
> same)  the volumes of read that postgres do to the OS.
> I did activate track_io_timing, but the volumes I get in the explain
> analyze buffer are roughly the same alone or not alone. (the 15M buffers
> told )
> to my understanding, the volumes that are shown in pg_stat_database are
> the useful ones ie. even if the db as to read it from disk more than once.
> true ? or false ?
>
> So.. either my supposition is not correct, and I will read with a lot of
> interest other ideas
> either its correct and I would like to know how to monitor this (in the
> current context, installing a dedicated extension is not impossible, but is
> a very boring process)
>
> Thanks for your help :-)
>
> regards,
>
> PS: I know that providing the complete data model and the exact requests
> can be considered mandatory, but when I change the request I get the very
> same behaviour...
>
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>

Re: pb with big volumes

[Ron]

Wouldn't IO contention make for additive timings instead of exponential?

On 8/10/23 20:41, Adam Scott wrote:

I think your concern is that 20 min + 30 min does not equal 3 hours.

It might be natural to think the contention would, at max, be 50 min x 2 
(1 hr 40 min).


So what's going on?

It seems disk I/O  is a primary suspect since you hint for an iostat 
replacement inside of Postgres.


If it is due to disk I/O the resolution will be to add RAID 0 SSDs at best.

Consider looking at io stats on the container's persistent volumes.

What is the pipe connecting the database server to the disks?  If it's 
NAS, well that would explain it.


HTH,
Adam



On Thu, Aug 10, 2023 at 2:37 PM Marc Millas  wrote:

Hi,

I have a 15 TB db on postgres 14 (soon 15).
shared buffers is 32 GB.

It's a db with max 15 users and often less, and currently 1 or 2.
the biggest table have 133 partitions of 150M to 200M+ rows each.
lots of request access explicitly one of those.

When I, alone, run a query "reading" 15M buffers, it takes 20 minutes
(+-5minutes). inside the query there are 2 group by on a 200M rows
partition, with all the rows in each group by.
When a colleague run the same kind of request (not the same request,
but something reading roughly the same volume ) , on a different set
of data,   his request is completed in less than half an hour.
If we run our requests simultaneously... my request take hours. around
3 hours.

I am making a supposition that its some kind of "pumping" effect in
the cache.

I cannot have access to the underlying OS. I can, for sure, do some
copy xx from program 'some command',  but its a container with very
limited possibilities, not even 'ps'.
So I would like to monitor from inside the db (so without iostat and
the same)  the volumes of read that postgres do to the OS.
I did activate track_io_timing, but the volumes I get in the explain
analyze buffer are roughly the same alone or not alone. (the 15M
buffers told )
to my understanding, the volumes that are shown in pg_stat_database
are the useful ones ie. even if the db as to read it from disk more
than once. true ? or false ?

So.. either my supposition is not correct, and I will read with a lot
of interest other ideas
either its correct and I would like to know how to monitor this (in
the current context, installing a dedicated extension is not
impossible, but is a very boring process)

Thanks for your help :-)

regards,

PS: I know that providing the complete data model and the exact
requests can be considered mandatory, but when I change the request I
get the very same behaviour...



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 



--
Born in Arizona, moved to Babylonia.

Re: pb with big volumes

[David Rowley]

On Fri, 11 Aug 2023 at 13:54, Ron  wrote:
> Wouldn't IO contention make for additive timings instead of exponential?

No, not necessarily. Imagine one query running that's doing a
parameterised nested loop join resulting in the index on the inner
side being descended several, say, million times.  Let's say there's
*just* enough RAM/shared buffers so that the index pages, once the
index is scanned the first time, all the required pages are cached
which results in no I/O on subsequent index scans.  Now, imagine
another similar query but with another index, let's say this index
also *just* fits in cache.  Now, when these two queries run
concurrently, they each evict buffers the other one uses.  Of course,
the shared buffers code is written in such a way as to try and evict
lesser used buffers first, but if they're all used about the same
amount, then this can stuff occur.  The slowdown isn't linear.

I've no idea if this is happening for the reported case. I'm just
saying that it can happen. The OP should really post the results of:
SET track_io_timing = ON; EXPLAIN (ANALYZE, BUFFERS) for both queries
running independently then again when they run concurrently.

David
David