Re: Postgres 15 SELECT query doesn't use index under RLS

2023-10-26 Thread Alexander Okulovich 

Hi Tom,

I've attempted to reproduce this on my PC in Docker from the stage 
database dump, but no luck. The first query execution on Postgres 15 
behaves like on the real stage, but subsequent ones use the index. Also, 
they execute much faster. Looks like the hardware and(or) the data 
structure on disk matters.


Here is the Docker Compose sample config:


version:'2.4' services:
database-15:
   image: postgres:15.4
   ports:
 -"7300:5432" environment:
 POSTGRES_DB: stage_db
 POSTGRES_USER: stage
 POSTGRES_PASSWORD: stage
   volumes:
 -"./init.sql:/docker-entrypoint-initdb.d/init.sql" 
-"./pgdb/aws-15:/var/lib/postgresql/data" mem_limit: 512M
   cpus: 2
   blkio_config:
 device_read_bps:
   -path: /dev/nvme0n1
 rate:'10mb' device_read_iops:
 -path: /dev/nvme0n1
   rate: 2000
 device_write_bps:
 -path: /dev/nvme0n1
   rate:'10mb' device_write_iops:
 -path: /dev/nvme0n1
   rate: 2000
I performed tests only with CPU and memory limits. If I try to limit the 
disk(blkio_config), my system hangs on container startup after a while.
Could you please share your thoughts on how to create such a 
self-contained test case.


Kind regards,

Alexander

On 18.10.2023 22:35, Tom Lane wrote:
If you could provide a self-contained test case that performs 10x 
worse under v15 than v12, we'd surely take a look at it. But with the 
information you've given so far, little is possible beyond speculation.

Re: Postgres 15 SELECT query doesn't use index under RLS

2023-10-26 Thread Tom Lane 
Alexander Okulovich  writes:
> I've attempted to reproduce this on my PC in Docker from the stage 
> database dump, but no luck. The first query execution on Postgres 15 
> behaves like on the real stage, but subsequent ones use the index.

Can you force it in either direction with "set enable_seqscan = off"
(resp. "set enable_indexscan = off")?  If so, how do the estimated
costs compare for the two plan shapes?

> Also, 
> they execute much faster. Looks like the hardware and(or) the data 
> structure on disk matters.

Maybe your prod installation has a bloated index, and that's driving
up the estimated cost enough to steer the planner away from it.

regards, tom lane