public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Jon Emord <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Poor performance with row wise comparisons
Date: Sun, 09 Feb 2025 17:40:33 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <BLAPR09MB64998C1078D3E84803E10BEECCF12@BLAPR09MB6499.namprd09.prod.outlook.com>
References: <BLAPR09MB64993673A034EE93C6C6F4B6CCF62@BLAPR09MB6499.namprd09.prod.outlook.com>
<CAKAnmmJDHajWWYx8bMUJRxVHZnLnP0WhEMZD40jeqrZ7aLsFUw@mail.gmail.com>
<BLAPR09MB64998C1078D3E84803E10BEECCF12@BLAPR09MB6499.namprd09.prod.outlook.com>
Jon Emord <[email protected]> writes:
> -> 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
> 2.
> data_model_id = 123 is the 15 most common value of data_model_id with 10.8 million records
Hm. I think your answer is in this comment in nbtree's
key-preprocessing logic:
* Row comparison keys are currently also treated without any smarts:
* we just transfer them into the preprocessed array without any
* editorialization. We can treat them the same as an ordinary inequality
* comparison on the row's first index column, for the purposes of the logic
* about required keys.
That is, for the purposes of deciding when the index scan can stop,
the "<= ROW" condition acts like "data_model_id <= 123". So it will
run through all of the data_model_id = 123 entries before stopping.
regards, tom lane
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Poor performance with row wise comparisons
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox