From: Kirill <aurx@mail.ru>
To: pgsql-performance@postgresql.org
Subject: multi-tenant queries select wrong index
Date: Mon, 20 Sep 2021 15:33:16 +0300
Message-ID: <1632141196.287783798@f377.i.mail.ru> (raw)
Hello,
As modern software is typically multi-tenant aware it is critical for DB to effectively filter
database records based on tenant ID context. Yet, we constantly hit the situations when Postgres 13.4 performs poorly.
If community is interested I can report such trivial and obvious cases for optimisation. Or even sponsor development a bit.
1. Here is an example when tasks are selected for 1 tenant and everything is fine and index on (tenant_id, id) is used:
SELECT * FROM "tasks" WHERE
(tenant_id IN ('45AQ7HARTXQG1P6QNEDDA8A5V0'))
ORDER BY id desc LIMIT 100
Limit (cost=0.69..426.01 rows=100 width=1679) (actual time=0.023..0.209 rows=100 loops=1)
-> Index Scan Backward using task_tenant_id_status_idx on tasks (cost=0.69..25770.78 rows=6059 width=1679) (actual time=0.023..0.200 rows=100 loops=1)
Index Cond: (tenant_id = '45AQ7HARTXQG1P6QNEDDA8A5V0'::text)
Planning Time: 0.125 ms
Execution Time: 0.231 ms
2. Now when I add 2 additional tenant IDs to the query everything gets 100x worse, despite the fact that those 2 tenants do NOT have any records at all.
The reason is the wrong index on (tenant_id, status) is used:
SELECT * FROM "tasks" WHERE
(tenant_id IN ('222P0TQT0FAR86BR30BB50TZZX','1X2W2J9B2VVJFSXGWZYR3XEHJO','45AQ7HARTXQG1P6QNEDDA8A5V0'))
ORDER BY id desc LIMIT 100
Limit (cost=65506.24..65506.49 rows=100 width=1679) (actual time=93.972..93.989 rows=100 loops=1)
-> Sort (cost=65506.24..65551.68 rows=18178 width=1679) (actual time=93.970..93.979 rows=100 loops=1)
Sort Key: id DESC
Sort Method: top-N heapsort Memory: 97kB
-> Bitmap Heap Scan on tasks (cost=322.56..64811.49 rows=18178 width=1679) (actual time=10.546..65.559 rows=29159 loops=1)
Recheck Cond: (tenant_id = ANY ('{222P0TQT0FAR86BR30BB50TZZX,1X2W2J9B2VVJFSXGWZYR3XEHJO,45AQ7HARTXQG1P6QNEDDA8A5V0}'::text[]))
Heap Blocks: exact=27594
-> Bitmap Index Scan on task_tenant_status_idx (cost=0.00..318.01 rows=18178 width=0) (actual time=4.268..4.268 rows=29236 loops=1)
Index Cond: (tenant_id = ANY ('{222P0TQT0FAR86BR30BB50TZZX,1X2W2J9B2VVJFSXGWZYR3XEHJO,45AQ7HARTXQG1P6QNEDDA8A5V0}'::text[]))
Planning Time: 0.212 ms
Execution Time: 94.051 ms
is it possible somehow to force PG to use the correct index?
Regards,
Kirill
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: aurx@mail.ru
Subject: Re: multi-tenant queries select wrong index
In-Reply-To: <1632141196.287783798@f377.i.mail.ru>
* 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