On 09/20/21 15:33, Kirill wrote:
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?
Try "set enable_bitmapscan to off;", but it is not a solution.
Have you try to analyze table, vacuum table, create statistics [...] on
... from ... ?
Regards,
Kirill
