Re: Poor performance with row wise comparisons

With limit 101, the plan is ~equivalent to the no limit case

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

Some other information about the table:

1. Row estimate for entity is 1.2 billion rows
2.
data_model_id = 123 is the 15 most common value of data_model_id with 10.8 million records
3. primary_key is a relatively unique column

select attname, null_frac, avg_width, n_distinct
from pg_stats
where tablename = ‘entity’ and attname in ('data_model_id', 'primary_key');
attname | null_frac | avg_width | n_distinct
------------------+-----------+-----------+-------------
data_model_id | 0 | 8 | 1143
primary_key | 0 | 23 | -0.27303192
(2 rows)

________________________________
From: Greg Sabino Mullane <htamfids(at)gmail(dot)com>
Sent: Friday, February 7, 2025 9:43 AM
To: Jon Emord <jon(at)peregrine(dot)io>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: Poor performance with row wise comparisons

You don't often get email from htamfids(at)gmail(dot)com(dot) Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
On Fri, Feb 7, 2025 at 2:05 AM Jon Emord <jon(at)peregrine(dot)io<mailto:jon(at)peregrine(dot)io>> wrote:
but with limit 101, the extra shared hits return

Can you show the explain analyze for the limit 101 case?

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com<https://www.crunchydata.com/>
Enterprise Postgres Software Products & Tech Support

This email is from an external source. Exercise additional caution with links and attachments.

The content of this email is confidential, may contain proprietary information, and is solely intended for the recipient specified. If you received this message by mistake, please reply to this message and follow with its deletion, so that we can ensure such a mistake does not occur in the future.