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:
- Row estimate for entity is 1.2 billion rows
data_model_id = 123 is the 15 most common value of data_model_id with 10.8 million records
- 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@gmail.com>
Sent: Friday, February 7, 2025 9:43 AM
To: Jon Emord <jon@peregrine.io>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Poor performance with row wise comparisons
but with limit 101, the extra shared hits return
Can you show the explain analyze for the limit 101 case?
Cheers,
Greg
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.
