Re: multi-tenant queries select wrong index

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