pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres 15 SELECT query doesn't use index under RLS
10+ messages / 3 participants
[nested] [flat]
* Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-12 16:41 Alexander Okulovich <aokulovich@stiltsoft.com>
0 siblings, 2 replies; 10+ messages in thread
From: Alexander Okulovich @ 2023-10-12 16:41 UTC (permalink / raw)
To: pgsql-performance
Hello everyone!
Recently, we upgraded the AWS RDS instance from Postgres 12.14 to 15.4
and noticed extremely high disk consumption on the following query
execution:
select (exists (select 1 as "one" from "public"."indexed_commit" where
"public"."indexed_commit"."repo_id" in (964992,964994,964999, ...);
For some reason, the query planner starts using Seq Scan instead of the
index on the "repo_id" column when requesting under user limited with
RLS. On prod, it happens when there are more than 316 IDs in the IN part
of the query, on stage - 3. If we execute the request from Superuser,
the planner always uses the "repo_id" index.
Luckily, we can easily reproduce this on our stage database (which is
smaller). If we add a multicolumn "repo_id, tenant_id" index, the
planner uses it (Index Only Scan) with any IN params count under RLS.
Could you please clarify if this is a Postgres bug or not? Should we
include the "tenant_id" column in all our indexes to make them work
under RLS?
Postgres version / Operating system+version
PostgreSQL 15.4 on aarch64-unknown-linux-gnu, compiled by gcc (GCC)
7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
Full Table and Index Schema
\d indexed_commit
Table "public.indexed_commit"
Column | Type | Collation | Nullable |
Default
---------------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
commit_hash | character varying(40) | | not null |
parent_hash | text | | |
created_ts | timestamp without time zone | | not null |
repo_id | bigint | | not null |
lines_added | bigint | | |
lines_removed | bigint | | |
tenant_id | uuid | | not null |
author_id | uuid | | not null |
Indexes:
"indexed-commit-repo-idx" btree (repo_id)
"indexed_commit_commit_hash_repo_id_key" UNIQUE CONSTRAINT, btree
(commit_hash, repo_id) REPLICA IDENTITY
"indexed_commit_repo_id_without_loc_idx" btree (repo_id) WHERE
lines_added IS NULL OR lines_removed IS NULL
Policies:
POLICY "commit_isolation_policy"
USING ((tenant_id =
(current_setting('app.current_tenant_id'::text))::uuid))
Table Metadata
SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts,
relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE
relname='indexed_commit';
relname | relpages | reltuples | relallvisible | relkind |
relnatts | relhassubclass | reloptions | pg_table_size
----------------+----------+--------------+---------------+---------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------
indexed_commit | 18170522 | 7.451964e+08 | 18104744 | r |
9 | f |
{autovacuum_vacuum_scale_factor=0,autovacuum_analyze_scale_factor=0,autovacuum_vacuum_threshold=200000,autovacuum_analyze_threshold=100000}
| 148903337984
EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
Production queries:
316 ids under RLS limited user
<https://explain.depesz.com/s/X7Iq;
392 ids under RLS limited user <https://explain.depesz.com/s/lbkX;
392 ids under Superuser <https://explain.depesz.com/s/uKSG;
History
It became slow after the upgrade to 15.4. We never had any issues before.
Hardware
AWS DB class db.t4g.large + GP3 400GB disk
Maintenance Setup
Are you running autovacuum? Yes
If so, with what settings?
autovacuum_vacuum_scale_factor=0,autovacuum_analyze_scale_factor=0,autovacuum_vacuum_threshold=200000,autovacuum_analyze_threshold=100000
SELECT * FROM pg_stat_user_tables WHERE relname='indexed_commit';
relid | schemaname | relname | seq_scan | seq_tup_read |
idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del |
n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze |
n_ins_since_vacuum | last_vacuum | last_autovacuum |
last_analyze | last_autoanalyze | vacuum_count |
autovacuum_count | analyze_count | autoanalyze_count
-------+------------+----------------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+--------------------+-------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
24662 | public | indexed_commit | 2485 | 49215378424 |
374533865 | 4050928807 | 764089750 | 2191615 | 18500311
| 0 | 745241398 | 383 | 46018
| 45343 | | 2023-10-11 23:51:29.170378+00
| | 2023-10-11 23:50:18.922351+00 | 0
| 672 | 0 | 753
WAL Configuration
For data writing queries: have you moved the WAL to a different disk?
Changed the settings? No.
GUC Settings
What database configuration settings have you changed? We use default
settings.
What are their values?
SELECT * FROM pg_settings WHERE name IN ('effective_cache_size',
'shared_buffers', 'work_mem');
name | setting | unit | category |
short_desc | extra_desc | context | vartype | source |
min_val | max_val | enumvals | boot_val | reset_val | sourcefile |
sourceline | pending_restart
----------------------+---------+------+---------------------------------------+------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------+---------+------------+----------+----------+-----------+------------+------------+-----------------
effective_cache_size | 494234 | 8kB | Query Tuning / Planner Cost
Constants | Sets the planner's assumption about the total size of the
data caches. | That is, the total size of the caches (kernel cache and
shared buffers) used for PostgreSQL data files. This is measured in disk
pages, which are normally 8 kB each. | user | integer |
configuration file | 1 | 2147483647 | | 524288 |
494234 | | | f
shared_buffers | 247117 | 8kB | Resource Usage /
Memory | Sets the number of shared memory buffers used by
the server. | | postmaster | integer | configuration file | 16 |
1073741823 | | 16384 | 247117 | | | f
work_mem | 4096 | kB | Resource Usage /
Memory | Sets the maximum memory to be used for query
workspaces. | This much memory can be used by each
internal sort operation and hash table before switching to temporary
disk files. | user
| integer | default | 64 | 2147483647 | |
4096 | 4096 | | | f
Statistics: n_distinct, MCV, histogram
Useful to check statistics leading to bad join plan. SELECT (SELECT
sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname,
inherited, null_frac, n_distinct, array_length(most_common_vals,1)
n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM
pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;
Returns 0 rows.
Kind regards,
Alexander
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-13 20:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Okulovich <aokulovich@stiltsoft.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-10-13 20:26 UTC (permalink / raw)
To: Alexander Okulovich <aokulovich@stiltsoft.com>; +Cc: pgsql-performance
Alexander Okulovich <aokulovich@stiltsoft.com> writes:
> Recently, we upgraded the AWS RDS instance from Postgres 12.14 to 15.4
> and noticed extremely high disk consumption on the following query
> execution:
> select (exists (select 1 as "one" from "public"."indexed_commit" where
> "public"."indexed_commit"."repo_id" in (964992,964994,964999, ...);
> For some reason, the query planner starts using Seq Scan instead of the
> index on the "repo_id" column when requesting under user limited with
> RLS. On prod, it happens when there are more than 316 IDs in the IN part
> of the query, on stage - 3. If we execute the request from Superuser,
> the planner always uses the "repo_id" index.
The superuser bypasses the RLS policy. When that's enforced, the
query can no longer use an index-only scan (because it needs to fetch
tenant_id too). Moreover, it may be that only a small fraction of the
rows fetched via the index will satisfy the RLS condition. So the
estimated cost of an indexscan query could be high enough to persuade
the planner that a seqscan is a better idea.
> Luckily, we can easily reproduce this on our stage database (which is
> smaller). If we add a multicolumn "repo_id, tenant_id" index, the
> planner uses it (Index Only Scan) with any IN params count under RLS.
Yeah, that would be the obvious way to ameliorate both problems.
If in fact you were getting decent performance from an indexscan plan
before, the only explanation I can think of is that the repo_ids you
are querying for are correlated with the tenant_id, so that the RLS
filter doesn't eliminate very many rows from the index result. The
planner wouldn't realize that by default, but if you create extended
statistics on repo_id and tenant_id then it might do better. Still,
you probably want the extra index.
> Could you please clarify if this is a Postgres bug or not?
You haven't shown any evidence suggesting that.
> Should we
> include the "tenant_id" column in all our indexes to make them work
> under RLS?
Adding tenant_id is going to bloat your indexes quite a bit,
so I wouldn't do that except in cases where you've demonstrated
it's important.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-18 10:29 Alexander Okulovich <aokulovich@stiltsoft.com>
parent: Alexander Okulovich <aokulovich@stiltsoft.com>
1 sibling, 1 reply; 10+ messages in thread
From: Alexander Okulovich @ 2023-10-18 10:29 UTC (permalink / raw)
To: Oscar van Baten <info@oxcro.com>; +Cc: pgsql-performance
Hi Oscar,
Thank you for the suggestion.
Unfortunately, I didn't mention that on prod we performed the upgrade
from Postgres 12 to 15 using replication to another instance with
pglogical, so I assume that the index was filled from scratch by
Postgres 15.
We upgraded stage instance by changing Postgres version only, so
potentially could run into the index issue there. I've tried to execute
REINDEX CONCURRENTLY, but the performance issue hasn't gone. The problem
is probably somewhere else. However, I do not exclude that we'll perform
REINDEX on prod.
Kind regards,
Alexander
On 13.10.2023 11:44, Oscar van Baten wrote:
> Hi Alexander,
>
> I think this is caused by the de-duplication of B-tree index entries
> which was added to postgres in version 13
> https://www.postgresql.org/docs/release/13.0/
>
> "
> More efficiently store duplicates in B-tree indexes (Anastasia
> Lubennikova, Peter Geoghegan)
> This allows efficient B-tree indexing of low-cardinality columns by
> storing duplicate keys only once. Users upgrading with pg_upgrade will
> need to use REINDEX to make an existing index use this feature.
> "
>
> When we upgraded from 12->13 we had a similar issue. We had to rebuild
> the indexes and it was fixed..
>
>
> regards,
> Oscar
>
>
> Op do 12 okt 2023 om 18:41 schreef Alexander Okulovich
> <aokulovich@stiltsoft.com>:
>
> Hello everyone!
>
>
> Recently, we upgraded the AWS RDS instance from Postgres 12.14 to
> 15.4 and noticed extremely high disk consumption on the following
> query execution:
>
> select (exists (select 1 as "one" from "public"."indexed_commit"
> where "public"."indexed_commit"."repo_id" in
> (964992,964994,964999, ...);
>
> For some reason, the query planner starts using Seq Scan instead
> of the index on the "repo_id" column when requesting under user
> limited with RLS. On prod, it happens when there are more than 316
> IDs in the IN part of the query, on stage - 3. If we execute the
> request from Superuser, the planner always uses the "repo_id" index.
>
> Luckily, we can easily reproduce this on our stage database (which
> is smaller). If we add a multicolumn "repo_id, tenant_id" index,
> the planner uses it (Index Only Scan) with any IN params count
> under RLS.
>
> Could you please clarify if this is a Postgres bug or not? Should
> we include the "tenant_id" column in all our indexes to make them
> work under RLS?
>
>
> Postgres version / Operating system+version
>
>
> PostgreSQL 15.4 on aarch64-unknown-linux-gnu, compiled by gcc
> (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
>
>
> Full Table and Index Schema
>
> \d indexed_commit
> Table "public.indexed_commit"
> Column | Type | Collation |
> Nullable | Default
> ---------------+-----------------------------+-----------+----------+---------
> id | bigint | | not null |
> commit_hash | character varying(40) | | not null |
> parent_hash | text | | |
> created_ts | timestamp without time zone | | not null |
> repo_id | bigint | | not null |
> lines_added | bigint | | |
> lines_removed | bigint | | |
> tenant_id | uuid | | not null |
> author_id | uuid | | not null |
> Indexes:
> "indexed-commit-repo-idx" btree (repo_id)
> "indexed_commit_commit_hash_repo_id_key" UNIQUE CONSTRAINT,
> btree (commit_hash, repo_id) REPLICA IDENTITY
> "indexed_commit_repo_id_without_loc_idx" btree (repo_id) WHERE
> lines_added IS NULL OR lines_removed IS NULL
> Policies:
> POLICY "commit_isolation_policy"
> USING ((tenant_id =
> (current_setting('app.current_tenant_id'::text))::uuid))
>
>
> Table Metadata
>
> SELECT relname, relpages, reltuples, relallvisible, relkind,
> relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM
> pg_class WHERE relname='indexed_commit';
> relname | relpages | reltuples | relallvisible |
> relkind | relnatts | relhassubclass | reloptions | pg_table_size
> ----------------+----------+--------------+---------------+---------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------
> indexed_commit | 18170522 | 7.451964e+08 | 18104744 |
> r | 9 | f |
> {autovacuum_vacuum_scale_factor=0,autovacuum_analyze_scale_factor=0,autovacuum_vacuum_threshold=200000,autovacuum_analyze_threshold=100000}
> | 148903337984
>
>
> EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
>
> Production queries:
>
> 316 ids under RLS limited user
> <https://explain.depesz.com/s/X7Iq;
>
> 392 ids under RLS limited user <https://explain.depesz.com/s/lbkX;
>
> 392 ids under Superuser <https://explain.depesz.com/s/uKSG;
>
>
> History
>
> It became slow after the upgrade to 15.4. We never had any issues
> before.
>
>
> Hardware
>
> AWS DB class db.t4g.large + GP3 400GB disk
>
>
> Maintenance Setup
>
> Are you running autovacuum? Yes
>
> If so, with what settings?
>
> autovacuum_vacuum_scale_factor=0,autovacuum_analyze_scale_factor=0,autovacuum_vacuum_threshold=200000,autovacuum_analyze_threshold=100000
>
> SELECT * FROM pg_stat_user_tables WHERE relname='indexed_commit';
> relid | schemaname | relname | seq_scan | seq_tup_read |
> idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del |
> n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze |
> n_ins_since_vacuum | last_vacuum | last_autovacuum |
> last_analyze | last_autoanalyze | vacuum_count |
> autovacuum_count | analyze_count | autoanalyze_count
> -------+------------+----------------+----------+--------------+-----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+--------------------+-------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
> 24662 | public | indexed_commit | 2485 | 49215378424 |
> 374533865 | 4050928807 | 764089750 | 2191615 | 18500311
> | 0 | 745241398 | 383 | 46018
> | 45343 | | 2023-10-11 23:51:29.170378+00
> | | 2023-10-11 23:50:18.922351+00 | 0
> | 672 | 0 | 753
>
>
> WAL Configuration
>
> For data writing queries: have you moved the WAL to a different
> disk? Changed the settings? No.
>
>
> GUC Settings
>
> What database configuration settings have you changed? We use
> default settings.
>
> What are their values?
>
> SELECT * FROM pg_settings WHERE name IN ('effective_cache_size',
> 'shared_buffers', 'work_mem');
> name | setting | unit | category |
> short_desc | extra_desc | context | vartype |
> source | min_val | max_val | enumvals | boot_val |
> reset_val | sourcefile | sourceline | pending_restart
> ----------------------+---------+------+---------------------------------------+------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------+---------+------------+----------+----------+-----------+------------+------------+-----------------
> effective_cache_size | 494234 | 8kB | Query Tuning / Planner
> Cost Constants | Sets the planner's assumption about the total
> size of the data caches. | That is, the total size of the caches
> (kernel cache and shared buffers) used for PostgreSQL data files.
> This is measured in disk pages, which are normally 8 kB each. |
> user | integer | configuration file | 1 | 2147483647
> | | 524288 | 494234 | | | f
> shared_buffers | 247117 | 8kB | Resource Usage /
> Memory | Sets the number of shared memory buffers
> used by the server. | | postmaster | integer | configuration file
> | 16 | 1073741823 | | 16384 | 247117 |
> | | f
> work_mem | 4096 | kB | Resource Usage /
> Memory | Sets the maximum memory to be used for
> query workspaces. | This much memory can be used by
> each internal sort operation and hash table before switching to
> temporary disk
> files. |
> user | integer | default | 64 | 2147483647
> | | 4096 | 4096 | | | f
>
>
> Statistics: n_distinct, MCV, histogram
>
> Useful to check statistics leading to bad join plan. SELECT
> (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV,
> tablename, attname, inherited, null_frac, n_distinct,
> array_length(most_common_vals,1) n_mcv,
> array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats
> WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;
>
> Returns 0 rows.
>
>
> Kind regards,
>
> Alexander
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-18 14:07 Alexander Okulovich <aokulovich@stiltsoft.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Alexander Okulovich @ 2023-10-18 14:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
> If in fact you were getting decent performance from an indexscan plan
> before, the only explanation I can think of is that the repo_ids you
> are querying for are correlated with the tenant_id, so that the RLS
> filter doesn't eliminate very many rows from the index result. The
> planner wouldn't realize that by default, but if you create extended
> statistics on repo_id and tenant_id then it might do better. Still,
> you probably want the extra index.
Do you have any idea how to measure that correlation?
> You haven't shown any evidence suggesting that.
My suggestion is based on following backward reasoning.
We used the product with the default settings. The requests are simple.
We didn't change the hardware (actually, we use even more performant
hardware because of that issue) and DDL. I've checked the request on old
and new databases. Requests that rely on this index execute more than 10
times longer. Planner indeed used Index Scan before, but now it doesn't.
So, from my perspective, the only reason we experience that is database
logic change. I think we could probably try to reproduce the issue on
different Postgres versions and find the specific version that causes this.
> Adding tenant_id is going to bloat your indexes quite a bit,
> so I wouldn't do that except in cases where you've demonstrated
> it's important.
Any recommendations from the Postgres team on how to use the indexes
under RLS would help a lot here, but I didn't find them.
Kind regards,
Alexander
On 13.10.2023 22:26, Tom Lane wrote:
> Alexander Okulovich <aokulovich@stiltsoft.com> writes:
>> Recently, we upgraded the AWS RDS instance from Postgres 12.14 to 15.4
>> and noticed extremely high disk consumption on the following query
>> execution:
>> select (exists (select 1 as "one" from "public"."indexed_commit" where
>> "public"."indexed_commit"."repo_id" in (964992,964994,964999, ...);
>> For some reason, the query planner starts using Seq Scan instead of the
>> index on the "repo_id" column when requesting under user limited with
>> RLS. On prod, it happens when there are more than 316 IDs in the IN part
>> of the query, on stage - 3. If we execute the request from Superuser,
>> the planner always uses the "repo_id" index.
> The superuser bypasses the RLS policy. When that's enforced, the
> query can no longer use an index-only scan (because it needs to fetch
> tenant_id too). Moreover, it may be that only a small fraction of the
> rows fetched via the index will satisfy the RLS condition. So the
> estimated cost of an indexscan query could be high enough to persuade
> the planner that a seqscan is a better idea.
>
>> Luckily, we can easily reproduce this on our stage database (which is
>> smaller). If we add a multicolumn "repo_id, tenant_id" index, the
>> planner uses it (Index Only Scan) with any IN params count under RLS.
> Yeah, that would be the obvious way to ameliorate both problems.
>
> If in fact you were getting decent performance from an indexscan plan
> before, the only explanation I can think of is that the repo_ids you
> are querying for are correlated with the tenant_id, so that the RLS
> filter doesn't eliminate very many rows from the index result. The
> planner wouldn't realize that by default, but if you create extended
> statistics on repo_id and tenant_id then it might do better. Still,
> you probably want the extra index.
>
>> Could you please clarify if this is a Postgres bug or not?
> You haven't shown any evidence suggesting that.
>
>> Should we
>> include the "tenant_id" column in all our indexes to make them work
>> under RLS?
> Adding tenant_id is going to bloat your indexes quite a bit,
> so I wouldn't do that except in cases where you've demonstrated
> it's important.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-18 20:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Okulovich <aokulovich@stiltsoft.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-10-18 20:35 UTC (permalink / raw)
To: Alexander Okulovich <aokulovich@stiltsoft.com>; +Cc: pgsql-performance
Alexander Okulovich <aokulovich@stiltsoft.com> writes:
> We used the product with the default settings. The requests are simple.
> We didn't change the hardware (actually, we use even more performant
> hardware because of that issue) and DDL. I've checked the request on old
> and new databases. Requests that rely on this index execute more than 10
> times longer. Planner indeed used Index Scan before, but now it doesn't.
> So, from my perspective, the only reason we experience that is database
> logic change.
[ shrug... ] Maybe, but it's still not clear if it's a bug, or an
intentional change, or just a cost estimate that was on the hairy
edge before and your luck ran out.
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.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-19 07:43 Tomek <tomekphotos@gmail.com>
parent: Alexander Okulovich <aokulovich@stiltsoft.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tomek @ 2023-10-19 07:43 UTC (permalink / raw)
To: aokulovich@stiltsoft.com <aokulovich@stiltsoft.com>; +Cc: pgsql-performance
Hi Alexander!
Apart from the problem you are writing about I'd like to ask you to explain
how you interpret counted frac_MCV - for me it has no sense at all to
summarize most_common_freqs.
Please rethink it and explain what was the idea of such SUM ? I understand
that it can be some measure for ratio of NULL values but only in some cases
when n_distinct is small.
regards
> Statistics: n_distinct, MCV, histogram
>>
>> Useful to check statistics leading to bad join plan. SELECT (SELECT
>> sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname,
>> inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv,
>> array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE
>> attname='...' AND tablename='...' ORDER BY 1 DESC;
>>
>> Returns 0 rows.
>>
>>
>> Kind regards,
>>
>> Alexander
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-19 09:58 Alexander Okulovich <aokulovich@stiltsoft.com>
parent: Tomek <tomekphotos@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Alexander Okulovich @ 2023-10-19 09:58 UTC (permalink / raw)
To: Tomek <tomekphotos@gmail.com>; +Cc: pgsql-performance
Hi Tomek,
Unfortunately, I didn't dig into this. This request is recommended to
provide when describing
<https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct,_MCV,_histogram;
slow query issues, but looks like it relates to JOINs in the query,
which we don't have.
Kind regards,
Alexander
On 19.10.2023 09:43, Tomek wrote:
> Hi Alexander!
> Apart from the problem you are writing about I'd like to ask you to
> explain how you interpret counted frac_MCV - for me it has no sense at
> all to summarize most_common_freqs.
> Please rethink it and explain what was the idea of such SUM ? I
> understand that it can be some measure for ratio of NULL values but
> only in some cases when n_distinct is small.
>
> regards
>
>>
>> Statistics: n_distinct, MCV, histogram
>>
>> Useful to check statistics leading to bad join plan. SELECT
>> (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV,
>> tablename, attname, inherited, null_frac, n_distinct,
>> array_length(most_common_vals,1) n_mcv,
>> array_length(histogram_bounds,1) n_hist, correlation FROM
>> pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1
>> DESC;
>>
>> Returns 0 rows.
>>
>>
>> Kind regards,
>>
>> Alexander
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-26 13:47 Alexander Okulovich <aokulovich@stiltsoft.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Alexander Okulovich @ 2023-10-26 13:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-26 14:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Okulovich <aokulovich@stiltsoft.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-10-26 14:09 UTC (permalink / raw)
To: Alexander Okulovich <aokulovich@stiltsoft.com>; +Cc: pgsql-performance
Alexander Okulovich <aokulovich@stiltsoft.com> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Postgres 15 SELECT query doesn't use index under RLS
@ 2023-10-31 16:01 Alexander Okulovich <aokulovich@stiltsoft.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Alexander Okulovich @ 2023-10-31 16:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
> 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?
Here are the results from the prod instance:
seqscan off <https://explain.depesz.com/s/9AWx;
indexscan_off <https://explain.depesz.com/s/mTU2;
Just noticed that the WHEN clause differs from the initial one (392 ids
under RLS). Probably, this is why the execution time isn't so
catastrophic. Please let me know if this matters, and I'll rerun this
with the initial request.
Speaking of the stage vs local Docker Postgres instance, the execution
time on stage is so short (0.1 ms with seq scan, 0.195 with index scan)
that we probably should not consider them. But I'll execute the requests
if it's necessary.
> Maybe your prod installation has a bloated index, and that's driving
> up the estimated cost enough to steer the planner away from it.
We tried to make REINDEX CONCURRENTLY on a prod copy, but the planner
still used Seq Scan instead of Index Scan afterward.
Kind regards,
Alexander
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-10-31 16:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-12 16:41 Postgres 15 SELECT query doesn't use index under RLS Alexander Okulovich <aokulovich@stiltsoft.com>
2023-10-13 20:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-18 14:07 ` Alexander Okulovich <aokulovich@stiltsoft.com>
2023-10-18 20:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-26 13:47 ` Alexander Okulovich <aokulovich@stiltsoft.com>
2023-10-26 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-31 16:01 ` Alexander Okulovich <aokulovich@stiltsoft.com>
2023-10-18 10:29 ` Alexander Okulovich <aokulovich@stiltsoft.com>
2023-10-19 07:43 ` Tomek <tomekphotos@gmail.com>
2023-10-19 09:58 ` Alexander Okulovich <aokulovich@stiltsoft.com>
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