Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgIR0-009NF4-Tv for pgsql-performance@arkaria.postgresql.org; Fri, 07 Feb 2025 07:05:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tgIQx-00GEPB-97 for pgsql-performance@arkaria.postgresql.org; Fri, 07 Feb 2025 07:05:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tg9Be-00BtdL-IC for pgsql-performance@lists.postgresql.org; Thu, 06 Feb 2025 21:13:26 +0000 Received: from mail-westusazlp170110001.outbound.protection.outlook.com ([2a01:111:f403:c001::1] helo=BY5PR09CU001.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tg9Bb-003cL2-0e for pgsql-performance@lists.postgresql.org; Thu, 06 Feb 2025 21:13:25 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=Clp9z9aiwsdAslBw3JY6gyBSRu6c8VTv/nwL8hO3VIfn0U8BdvG+yHzIojbAmlBCCvAoaER4zfiEQC9e2tqvK1oDiA+xNfi+IrqmZkR0IJ+JXz+9SawSMQJphba1C02JFPZ5dUm8x2ywNRTdPdQw6YGEdveGMzTPb06Eq2jEZXEx1RSI2vfWe6WaamcUm0F56XXYw+a9QjDSN9s1RlXkoGkOLt0tpVSU+NeHQSSrtU5jBJhtxj20w2KvEbVFT9BOcrkcjPiFQtGEp+JvOk/m60MzVFr2R8hsuRHo108qRJ9Vy20QLiRPF0OFMdyl36085ESU3qFa/H871SQ2nVBO2Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=o3BZ11Z0QZPtk4SfxA77acBpikLa33BY2Ik6krqNEyE=; b=e6RFuK5nm/yamkF3DhtsZ3kotTmtWUVy0UxPEQrVNbjTUiqg5vRUodnnLbhpXdcymHjwf/rgzccbJsPFgHONNLkx9gaQau5Jy14jEGthN+o2nJJXVy0/fKQVatju+LnxXCD/CXJeYhqkS6us+241a2EmcLLknDdOj26P406hM8UcGj9HLqYex3rMlTMNxhQkoxbZKNv6m1l2+Qgxm3WX7gOPCWmFmYk1AR6sGSVx01Bo1DRyLI0XgDEmRUsGrRQNKtBWXuRYmP4IqJl1T1rmqzTdqX9SA/+hl3qxEkgfd6JnI65KOsMPeXJgocpn5ldU6AKo2u/s+UD17nQxU/RkbA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=peregrine.io; dmarc=pass action=none header.from=peregrine.io; dkim=pass header.d=peregrine.io; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=peregrine65.onmicrosoft.com; s=selector1-peregrine65-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=o3BZ11Z0QZPtk4SfxA77acBpikLa33BY2Ik6krqNEyE=; b=6+EjyfQmHbykQ41OlKZBsQgEg+t//I0Gxl7DIyokNvl0qRjn/sIBTcV7aFqxXtB+RFv+XSG9CY5R0dopbm35/xaT+3tn25YUI+eUc9guKPfDAR56bXXEUZjgyPphcpPCkyplKWj/hY8ygfySHWUtTNdATB0ghRUFFF87xkFFkL4= Received: from BLAPR09MB6499.namprd09.prod.outlook.com (2603:10b6:208:2a8::9) by SJ0PR09MB11154.namprd09.prod.outlook.com (2603:10b6:a03:509::5) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8422.14; Thu, 6 Feb 2025 21:13:17 +0000 Received: from BLAPR09MB6499.namprd09.prod.outlook.com ([fe80::209b:cfd5:14f4:b1ba]) by BLAPR09MB6499.namprd09.prod.outlook.com ([fe80::209b:cfd5:14f4:b1ba%5]) with mapi id 15.20.8422.012; Thu, 6 Feb 2025 21:13:17 +0000 From: Jon Emord To: "pgsql-performance@lists.postgresql.org" Subject: Poor performance with row wise comparisons Thread-Topic: Poor performance with row wise comparisons Thread-Index: AQHbeNjMryiTTb6JwUePFOWz9vUwGA== Date: Thu, 6 Feb 2025 21:13:17 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=peregrine.io; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BLAPR09MB6499:EE_|SJ0PR09MB11154:EE_ x-ms-office365-filtering-correlation-id: ec388b5e-9d1a-4500-644a-08dd46f3137a x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|31052699003|366016|41320700013|1800799024|39142699003|8096899003|38070700018; x-microsoft-antispam-message-info: =?Windows-1252?Q?V0Yj9h3/8OGKH9dfM393hQtvc4NHIAkVHvKODkAu6Qvbfao+hh5EJwBI?= =?Windows-1252?Q?j+B06QSlI0lVCb1wAvVxsA8KOXKN/XmqNiszZiy3RGTK56l7jZBfticp?= =?Windows-1252?Q?9yvZyHzdr4ZRWHgWwokLuy1QeBYOUj0T81yYsyEnYm6KDIhodXH6ZEhn?= =?Windows-1252?Q?2CBRvmCLkpzhcYWN+uWveSVcI+BDj+bYbQtmvPLZ2AD+ISBbdSlE1J8d?= =?Windows-1252?Q?vgPleelnZSWe2EkhBPRRiVhSmHhZc1yIMMin5PsELNEAHzF8SylDP+sa?= =?Windows-1252?Q?0+y/SBq25XauuNxI6a3buM26ILA8Sc78RlPwgTBslVZUDCRc/NpZSvCZ?= =?Windows-1252?Q?ajv/nWOZ+wayT3rolnDEj6/7bVYcQPYBDwQhJM18dTHtmsUmMqD+nXP6?= =?Windows-1252?Q?65tQITKxK5DT2hp54CXyhdDsC9R0yk01SYjFb071zs3qrravhXr9Q9Z4?= =?Windows-1252?Q?DZbmupnqGZ1YGIFPEZf4HCh3kogKGNRxA1airuqANyfr3dijd8E32kaY?= =?Windows-1252?Q?cWZKCOgNKfJqBWjug6wcSevBCIeB061cs8jTtJQZHFObnQUgoCywdrMd?= =?Windows-1252?Q?FvhLJWOF9j3MTYI3tKVzxa7S4ah2+/R3Cs2ezoBQ1ul6qXwEz+e1ZS0b?= =?Windows-1252?Q?fI7K+fkvz3smiHPZVNkavFMqv8ay19NX8eeMiDKHFnFC8x20ZPxT+hIj?= =?Windows-1252?Q?y+R5A1VUmFHzya5R8A0upc15vbsVevulLsFCqU8h4UQ6EBnkC+scc+XK?= =?Windows-1252?Q?3IWmFujVogWqJ4QJfhc1E+Ap+yt5CZq7VJt1BwFAyBgzdn9SyI7LmFk7?= =?Windows-1252?Q?gNLRGQmiOu7DKZMdb2zSr5LNZVIS+aRmtrQiCK4uoaJLG1S05OzigQqy?= =?Windows-1252?Q?VGICrX0VkHhBO+vzOhgQg2PwebbWFVWAEHc/wA4kfMdEfwUgFSWApu97?= =?Windows-1252?Q?k9177Mzbz/IKYbL7KJSoYkA76AhIt+Fyk694YyQsKh1yr1TERzCPQ2oP?= =?Windows-1252?Q?mFdg0EuNBraV7sFE2AXMLMFL2FRGwjE5XZWmtiAufGcP8QJl9672YmW0?= =?Windows-1252?Q?Awp0q0DNLD1atXdkx8MJN/NWzhal+x2bbW0UcKPzm7jIZA2X4whJoQ40?= =?Windows-1252?Q?mz5FFogH9YnSV4bg0Y5NxxqxHj4aws2TBvSWajaq0Bd2T1H+a1Z3ekWS?= =?Windows-1252?Q?Ey0W4omTxWWLCfzTAEhTVzQiwI5iaeBTKwrCyne3DM2gCkxM4//f/p29?= =?Windows-1252?Q?4Gap4jnhuRWqmvS+ABjvZBYm8a6DXwkLRfDXEPH/QYhXYKESr1QidpDh?= =?Windows-1252?Q?1KQLBWw/rP5nCnmboMHYTjlHswygSFCX/CWAKAmzszCrx/vqMfg3Ii7V?= =?Windows-1252?Q?uD4/YnjLKBLU7mFM632aEndWhUo6hwEBLOznt753nS6HGcDZNAGHYl2o?= =?Windows-1252?Q?EMB1CexiFteYRcHtiRJNOW4S+pxbPmqNJxo/lOpUuIkZhYwa1QE8FbEp?= =?Windows-1252?Q?jTH97XN6i6mL8K7sTPyilHtPkR6hWfE4fQNENArB5QSZwond58553NR/?= =?Windows-1252?Q?8XzlM7tCO94OVt/Q?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BLAPR09MB6499.namprd09.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(31052699003)(366016)(41320700013)(1800799024)(39142699003)(8096899003)(38070700018);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?xOj5pUBRFhJQ5IE5fk7PmlpDJNGx5o8c+N/LHOebHLbDeclHg7f1xF+z?= =?Windows-1252?Q?U80KFhF4rA5PDBpoKvaR1i31H3A+JQD1K7smatvgA7H0B12i/SXEiPKO?= =?Windows-1252?Q?dQIm45zynkTgEF/c05cYEMushCnrzE9noyiuAK7D/ImfPPWiOmkQMkRd?= =?Windows-1252?Q?kwJ5OJjDrlEuIzRruZK9saRboCTO2KiJwCIWrucCf5b5tVRH6Tpo3d5U?= =?Windows-1252?Q?zJDlxJJ0esaGYnNf/ETSjSpm27We7HzlCzgnWnJLgpIs7ZmqcFgy0zkS?= =?Windows-1252?Q?+OZIkck4KYk+vgm+b0rSRVIViGLDhZgLxkFDZ5kyZZIqS4lVg3onwse2?= =?Windows-1252?Q?iM4SGsjjMe0Vpu+kNxv609+lhLW3Vzn8I44eyIQUb5gN45+yF/ZCdgQZ?= =?Windows-1252?Q?DEtrB4A9ieXiJPQihK8cm+/XVlkdG2yh7arHcJ3D3syWDrdOyuie/+6L?= =?Windows-1252?Q?NVLVZm04giGGzXjOGqW9kEfvRoiR/L8lapxKraewWNbcbC2agaIwuqx+?= =?Windows-1252?Q?hQoO7y/QS96pGZnVMPpP1oIkg6hoA0xlrQjVkrEEKS2kgqAI/8QEKzRw?= =?Windows-1252?Q?+TwTsDFrp95PZb6KYWsrEk36v+MXH6OQUJUUYU4eFhcyc71fIDTGqG9W?= =?Windows-1252?Q?fZZkW2p1NZvWPX4D6Ce2v7Vy8eHRziSVEQUNepMwSP7QlX8WnEyxlOa5?= =?Windows-1252?Q?CNfACXTQDg3JNNqm+FwZRkoQzaNsSIFhrAKSDEXNP76wYLEi/5d4Cswy?= =?Windows-1252?Q?nHUYBW6SR00ES+qbdV02ZzQ4mk2aWBLpEot5AsYs47AZPPPNBbDZ0iLi?= =?Windows-1252?Q?q/K3a1NaTQ6gXHZ2UZA4fejQeKL8/4vD/xOUKWmIqO4dtctkBct0q3dB?= =?Windows-1252?Q?QsFEVuU32SHyQ4QZaRFMRBKrfoGywdU3G33TPoQH7ryYmRYDgqSU5oeE?= =?Windows-1252?Q?PM8x1auUpSZM0F8C8EZCWyNERItva58nVjaw1mwgXIhhq8JkoKvV72HL?= =?Windows-1252?Q?ZlzoKZPtmOMYPrsEBwBoOGjVEWiUW0XjOftIG9wUHp668+w0ZIgmQtz4?= =?Windows-1252?Q?dfr+w8gqZ959Xl/h4I3PF5bcw4w2Q24mzPyFcRftHnlVOmXF00baEE4y?= =?Windows-1252?Q?OmKVqloRkE6i6hcyEg/n/EUjI3rAVBnN3fke9vEA1KUlShuYVulOnl39?= =?Windows-1252?Q?p2fcCai+2GMtyjJupfs4eUbI1sW4tw0tK6iGm41hTlun3amZWA7TlBHa?= =?Windows-1252?Q?p2cFun3wcUqPDhhuFH+YB6Dk7RSrMBJ6XBrBXF+hToDbtnGXhQxkO6v5?= =?Windows-1252?Q?58rTHjw82WdjKSRkVQACdA0G0pwvp9yNK8gOkFSsNruVpb8yB5CMDQ+L?= =?Windows-1252?Q?TjbGMyg5Git7Ttgaa4EGq9qNUogU+vYYI5BfoXrb3dhCepDYWr1OiYwg?= =?Windows-1252?Q?pp5vOR2SuUb+q2VyJyTfVoXIeLiUaza02Y4Nz8/hrUl6g6y28utSjT2g?= =?Windows-1252?Q?Fwm8r/7P0bJ5XXakvMUgPP/v4BCCaGPeLlFmixsKvtgCvcc7PGEr7cSC?= =?Windows-1252?Q?C9TF6KVQxBjp5A9NV05QUajBSeXOBXZArPgvPvy10i8rEk43FuPdLo55?= =?Windows-1252?Q?fIpyKVvBaZCdcqXM94OleI+C8gMNxTbsuAcyjFK2ogqx4x+hILkcz1QH?= =?Windows-1252?Q?fXi33PE6Caw=3D?= Content-Type: multipart/alternative; boundary="_000_BLAPR09MB64993673A034EE93C6C6F4B6CCF62BLAPR09MB6499namp_" MIME-Version: 1.0 X-OriginatorOrg: peregrine.io X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: BLAPR09MB6499.namprd09.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: ec388b5e-9d1a-4500-644a-08dd46f3137a X-MS-Exchange-CrossTenant-originalarrivaltime: 06 Feb 2025 21:13:17.4090 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 258dabb1-2e0f-4417-be8e-16d8fa6ab063 X-MS-Exchange-Transport-CrossTenantHeadersStamped: SJ0PR09MB11154 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BLAPR09MB64993673A034EE93C6C6F4B6CCF62BLAPR09MB6499namp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi everyone I wrote a query to iterate over two different columns. These columns have a= composite unique index on them, and I=92m only retrieving those columns. I= found that there was a very large difference in performance when using row= wise comparison versus a column comparison. The column comparison was much= faster and the row wise comparison showed an increase in both CPU and shar= ed buffer hits. My expectation is that the following two queries would have roughly the sam= e performance. They both use the same index only scans and return the same = 100 rows of data. The main difference I see in the explain output is that t= he row wise comparison has 3,000 times the shared buffer hits, but it is un= clear why there would need to be more hits. I=92ve also found that when I add a limit 100 clause to the row wise compar= ison I get roughly equivalent performance, but with limit 101, the extra sh= ared hits return, which leads me to believe that the <=3D condition is not = stopping the execution of the query. We are running on AWS Aurora with version 16 and db.r5.24xlarge instance si= ze. I=92ve attached 3 explains: 1. Well performing query without a row wise comparison 2. Poor performing query with row wise comparison 3. Well performing query with row wise comparison and limit explain (analyze, buffers) select data_model_id, primary_key from entity WHERE data_model_id =3D 123 AND primary_key >=3D =91ABC=92 AND primary_key = <=3D =91DEF=92; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- Index Only Scan using entity_data_model_id_primary_key_uniq on entity (co= st=3D0.70..2.92 rows=3D1 width=3D31) (actual time=3D0.088..0.154 rows=3D100= loops=3D1) Index Cond: ((data_model_id =3D 287) AND (primary_key >=3D =91ABC=92::te= xt) AND (primary_key <=3D =91DEF=92::text)) Heap Fetches: 21 Buffers: shared hit=3D37 Planning Time: 0.111 ms Execution Time: 0.175 ms (6 rows) explain (analyze, buffers) select data_model_id, primary_key from entity WHERE (data_model_id, primary_key) >=3D (123 , =91ABC=92) AND (data_model_i= d, primary_key) <=3D (123, =91DEF=92); = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------ Index Only Scan using entity_data_model_id_primary_key_uniq on entity (co= st=3D0.70..7135372.50 rows=3D15347234 width=3D31) (actual time=3D0.116..277= 9.874 rows=3D100 loops=3D1) Index Cond: ((ROW(data_model_id, primary_key) >=3D ROW(123, =91ABC=92::t= ext)) AND (ROW(data_model_id, primary_key) <=3D ROW(123, =91DEF=92::text))) Heap Fetches: 21 Buffers: shared hit=3D97261 Planning: Buffers: shared hit=3D112 Planning Time: 0.324 ms Execution Time: 2779.915 ms (8 rows) explain (analyze, buffers) select data_model_id, primary_key from entity WHERE (data_model_id, primary_key) BETWEEN (123, =91DEF=92) AND (123, 'DEF'= ) limit 100; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------- Limit (cost=3D0.70..6.31 rows=3D100 width=3D31) (actual time=3D0.075..0.1= 75 rows=3D100 loops=3D1) Buffers: shared hit=3D34 -> Index Only Scan using entity_data_model_id_primary_key_uniq on entit= y (cost=3D0.70..873753.60 rows=3D15581254 width=3D31) (actual time=3D0.075= ..0.167 rows=3D100 loops=3D1) Index Cond: ((ROW(data_model_id, primary_key) >=3D ROW(123, 'DEF':= :text)) AND (ROW(data_model_id, primary_key) <=3D ROW(123, 'DEF'::text))) Heap Fetches: 4 Buffers: shared hit=3D34 Planning: Buffers: shared hit=3D104 Planning Time: 0.264 ms Execution Time: 0.198 ms (10 rows) A truncated schema of our table: Table "public.entity" Column | Type | Collation | Null= able | Default ------------------------------+--------------------------+-----------+-----= -----+---------------------------------------- id | bigint | | not = null | nextval('api_entity_id_seq'::regclass) updated_at | timestamp with time zone | | not = null | created_at | timestamp with time zone | | not = null | entity_id | uuid | | not = null | primary_key | character varying(255) | | not = null | data_model_id | bigint | | = | Indexes: "api_entity_pkey" PRIMARY KEY, btree (id) "entity_data_model_id_primary_key_uniq" UNIQUE, btree (data_model_id, p= rimary_key) "api_entity_entity_id_key" UNIQUE CONSTRAINT, btree (entity_id) "api_entity_updated_045756_idx" btree (updated_at DESC) Check constraints: "api_entity_data_model_v3_not_null" CHECK (data_model_v3_id IS NOT NULL= ) Best, Jon The content of this email is confidential, may contain proprietary informat= ion, and is solely intended for the recipient specified. If you received th= is message by mistake, please reply to this message and follow with its del= etion, so that we can ensure such a mistake does not occur in the future. --_000_BLAPR09MB64993673A034EE93C6C6F4B6CCF62BLAPR09MB6499namp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Hi everyone

I wrote a query to iterate over two different columns. These columns have a= composite unique index on them, and I=92m only retrieving those columns. I= found that there was a very large difference in performance when using row= wise comparison versus a column comparison. The column comparison was much faster and the row wise comparison showed a= n increase in both CPU and shared buffer hits.

My expectation is that the following two queries would have roughly the sam= e performance. They both use the same index only scans and return the same = 100 rows of data. The main difference I see in the explain output is that t= he row wise comparison has 3,000 times the shared buffer hits, but it is unclear why there would need to be= more hits. 

I=92ve also found that when I add a limit 100 clause to the row wise compar= ison I get roughly equivalent performance, but with limit 101, the extra sh= ared hits return, which leads me to believe that the <=3D condition is n= ot stopping the execution of the query. 

We are running on AWS Aurora with version 16 and db.r5.24xlarge instance si= ze.

I=92ve attached 3 explains:

  1. Well performing query without a row wise comparison
  2. Poor performing query with row wise comparison
  3. Well performing query with row wise comparison and limit

explain (analyze, buffers) 
select data_model_id, primary_key
from entity
WHERE data_model_id =3D 123 AND primary_key >=3D =91ABC=92 AND primary_k= ey <=3D =91DEF=92;
                     = ;                     &nb= sp;                     &= nbsp;               QUERY PLAN
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------
 Index Only Scan using entity_data_model_id_primary_key_uniq on entity=   (cost=3D0.70..2.92 rows=3D1 width=3D31) (actual time=3D0.088..0.154 = rows=3D100 loops=3D1)
   Index Cond: ((data_model_id =3D 287) AND (primary_key >=3D = =91ABC=92::text) AND (primary_key <=3D =91DEF=92::text))
   Heap Fetches: 21
   Buffers: shared hit=3D37
 Planning Time: 0.111 ms
 Execution Time: 0.175 ms
(6 rows)

explain (analyze, buffers)
select data_model_id, primary_key
from entity
WHERE (data_model_id, primary_key) >=3D (123 , =91ABC=92) AND (data_mode= l_id, primary_key) <=3D (123, =91DEF=92);
                     = ;                     &nb= sp;                     &= nbsp;                    =   QUERY PLAN
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------
 Index Only Scan using entity_data_model_id_primary_key_uniq on entity=   (cost=3D0.70..7135372.50 rows=3D15347234 width=3D31) (actual time=3D= 0.116..2779.874 rows=3D100 loops=3D1)
   Index Cond: ((ROW(data_model_id, primary_key) >=3D ROW(123,= =91ABC=92::text)) AND (ROW(data_model_id, primary_key) <=3D ROW(123, = =91DEF=92::text)))
   Heap Fetches: 21
   Buffers: shared hit=3D97261
 Planning:
   Buffers: shared hit=3D112
 Planning Time: 0.324 ms
 Execution Time: 2779.915 ms
(8 rows)

explain (analyze, buffers)
select data_model_id, primary_key
from entity
WHERE (data_model_id, primary_key) BETWEEN (123, =91DEF=92) AND (123, 'DEF'= ) limit 100;
                     = ;                     &nb= sp;                     &= nbsp;                    =     QUERY PLAN
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------------------------
 Limit  (cost=3D0.70..6.31 rows=3D100 width=3D31) (actual time=3D= 0.075..0.175 rows=3D100 loops=3D1)
   Buffers: shared hit=3D34
   ->  Index Only Scan using entity_data_model_id_primary= _key_uniq on entity  (cost=3D0.70..873753.60 rows=3D15581254 width=3D3= 1) (actual time=3D0.075..0.167 rows=3D100 loops=3D1)
         Index Cond: ((ROW(data_model_id, primary_= key) >=3D ROW(123, 'DEF'::text)) AND (ROW(data_model_id, primary_key) &l= t;=3D ROW(123, 'DEF'::text)))
         Heap Fetches: 4
         Buffers: shared hit=3D34
 Planning:
   Buffers: shared hit=3D104
 Planning Time: 0.264 ms
 Execution Time: 0.198 ms
(10 rows)

A truncated schema of our table:

                     = ;                     &nb= sp;     Table "public.entity"
            Column       =     |           Type     &nbs= p;     | Collation | Nullable |         =       Default
------------------------------+--------------------------+-----------+-----= -----+----------------------------------------
 id                   &nb= sp;       | bigint            =       |           | not null | nex= tval('api_entity_id_seq'::regclass)
 updated_at                 &n= bsp; | timestamp with time zone |           | not = null |
 created_at                 &n= bsp; | timestamp with time zone |           | not = null |
 entity_id                &nbs= p;   | uuid                 &n= bsp;   |           | not null |
 primary_key                &n= bsp; | character varying(255)   |           |= not null |
 data_model_id             | bigint &nbs= p;                 |     =       |          |
Indexes:
    "api_entity_pkey" PRIMARY KEY, btree (id)
    "entity_data_model_id_primary_key_uniq" UNIQUE, btr= ee (data_model_id, primary_key)
    "api_entity_entity_id_key" UNIQUE CONSTRAINT, btree= (entity_id)
    "api_entity_updated_045756_idx" btree (updated_at D= ESC)
Check constraints:
    "api_entity_data_model_v3_not_null" CHECK (data_mod= el_v3_id IS NOT NULL)


Best,
Jon


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

--_000_BLAPR09MB64993673A034EE93C6C6F4B6CCF62BLAPR09MB6499namp_--