Re: Poor performance with row wise comparisons

On Fri, 2025-02-07 at 16:16 +0000, Jon Emord wrote:
> explain (analyze, buffers)
> select data_model_id, primary_key
> from entity
> WHERE (data_model_id, primary_key) BETWEEN (123, ‘ABC’) AND (123, ‘DEF’) 
> limit 101;
>                                                                                           QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.70..6.37 rows=101 width=31) (actual time=0.094..2712.844 rows=100 loops=1)
>    Buffers: shared hit=97259
>    ->  Index Only Scan using entity_data_model_id_primary_key_uniq on entity  (cost=0.70..873753.60 rows=15581254 width=31) (actual time=0.093..2712.836 rows=100 loops=1)
>          Index Cond: ((ROW(data_model_id, primary_key) >= ROW(123, 'ABC'::text)) AND (ROW(data_model_id, primary_key) <= ROW(123, 'DEF'::text)))
>          Heap Fetches: 4
>          Buffers: shared hit=97259
>  Planning:
>    Buffers: shared hit=104
>  Planning Time: 0.204 ms
>  Execution Time: 2712.873 ms

How is the index "entity_data_model_id_primary_key_uniq" defined?

Yours,
Laurenz Albe