From: Alexander Okulovich <aokulovich@stiltsoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres 15 SELECT query doesn't use index under RLS
Date: Thu, 26 Oct 2023 15:47:03 +0200
Message-ID: <e9d503cb-efeb-43d3-952e-f517e4d24302@stiltsoft.com> (raw)
In-Reply-To: <3153246.1697661350@sss.pgh.pa.us>
References: <5c1179bb-240b-4c1c-b4b3-2a24868e44bc@stiltsoft.com>
<1570249.1697228785@sss.pgh.pa.us>
<dd874d42-ad02-48a6-82db-5666f1ee0ec1@stiltsoft.com>
<3153246.1697661350@sss.pgh.pa.us>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aokulovich@stiltsoft.com, tgl@sss.pgh.pa.us
Subject: Re: Postgres 15 SELECT query doesn't use index under RLS
In-Reply-To: <e9d503cb-efeb-43d3-952e-f517e4d24302@stiltsoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox