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 1sgnr4-008GsK-5C for pgsql-general@arkaria.postgresql.org; Wed, 21 Aug 2024 16:06:38 +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 1sgnr2-00ClAc-1T for pgsql-general@arkaria.postgresql.org; Wed, 21 Aug 2024 16:06:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgnr1-00Cl8H-89 for pgsql-general@lists.postgresql.org; Wed, 21 Aug 2024 16:06:36 +0000 Received: from mail-northcentralusazlp170100001.outbound.protection.outlook.com ([2a01:111:f403:c105::1] helo=CH1PR05CU001.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgnqv-000ngW-8T for pgsql-general@lists.postgresql.org; Wed, 21 Aug 2024 16:06:34 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=eD0uocB4GYgGgS5RwHVhU07ec9DCuit3W+3XjJg+RFk24An1ReIghXksLMHqrlRM5Fw2yaH3FVHykk/Wy1Hz+sQabIME7FQlEAkpZurg9sZ9gJ6typ7sVE9lJuzsoUvcxfiGZZ5F0jkCFGJQSxoGwOq3Eg9PeDBGerrDz6sNJKfRToUjM1u2eG5YOotoFdMgyzxjDxsLymNohCBng/q2WZWTxg/L2jCf/Xz0IhfQioIuG2DmEDTZVGgV5jN41F1JAELrPRx87cukGC6dRwL9ELD4JBG4en/W9ZT6becZVhp11kSLKB9Q16pUSbM2WjdTn6uQXmy7+M7YrEaV1bKvdQ== 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=XvY9CtcPJXhAMJF+3H2YLBTL/yj4BQ0xUwqgU+Hl5gk=; b=CSXC+4l8wsLC41p0NfPHnC1f4wvAX5/oIgeE7fJOmvLcpx/3neSeZa771T+bkWdnMYCueDnXS2lHEfLb1dOR1eB5lAs5bbTsxprcljFdCVkoC0Ms1+zcNHwWYjQOh8scgOXyDxE8N7S+KLB8IsAHm2lLi6L5rzM6e9VxcY+tbjirlZFeSD4FUjg3k5DHuMmGtEkOPmR7H5kjhY1zMkkfDTrKm9GUUR8/0f9TTZiCn08MlOguOXsluJIfgILmrP1N6RxVpdariqwZrurPGS2+RMx9NU//w7q94/YyG+T6kkswAXzJT/+5woeu20zcARVCNKbGx+ELW+coPFoTMxLTvw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=thomsonreuters.com; dmarc=pass action=none header.from=thomsonreuters.com; dkim=pass header.d=thomsonreuters.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=thomsonreuters.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=XvY9CtcPJXhAMJF+3H2YLBTL/yj4BQ0xUwqgU+Hl5gk=; b=H9zmevPH3OdfUgcNqLN4fxxnhY8xdwwLv+TeFrWbxhQqoIi6vFvL+azGBQ9aFJnGrCB6cAUGrVR3fYyvqXjRMWvu08mOBdcUDWLwjvJalKfz3T+o2E1ycrkaUYSgGoW/zoQHRHCgYrteDQ0L+CGoH/qWqW5DcThYNaE6zBNPBo8= Received: from BL0PR03MB4001.namprd03.prod.outlook.com (2603:10b6:208:2e::15) by BN9PR03MB6025.namprd03.prod.outlook.com (2603:10b6:408:136::7) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7897.18; Wed, 21 Aug 2024 16:06:23 +0000 Received: from BL0PR03MB4001.namprd03.prod.outlook.com ([fe80::d7a1:21c4:c2bb:d568]) by BL0PR03MB4001.namprd03.prod.outlook.com ([fe80::d7a1:21c4:c2bb:d568%3]) with mapi id 15.20.7897.014; Wed, 21 Aug 2024 16:06:23 +0000 From: "Dirschel, Steve" To: pgsql-general Subject: Query tuning question Thread-Topic: Query tuning question Thread-Index: Adrz43QgSkI3oeDrTKmvBKUYJfSRbA== Date: Wed, 21 Aug 2024 16:06:23 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=thomsonreuters.com; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: BL0PR03MB4001:EE_|BN9PR03MB6025:EE_ x-ms-office365-filtering-correlation-id: 1677d0b6-e064-497f-803c-08dcc1fb3428 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|366016|376014|38070700018; x-microsoft-antispam-message-info: =?us-ascii?Q?vugk7Z2cF03GawYhgbI468lNMxvH8IeoRx+1GrZoAojmMszJ67U0xYckgdeQ?= =?us-ascii?Q?GykrE8ZeGgp3R8z5SodjGaDdqn0UfQZSDwjI6/xrijU57rwDrWaIRWD0BMrs?= =?us-ascii?Q?MjzwrFcBXce3kw6RMEwdG0CTfuWA6JsFLslCLEfQU3V0Dh75fMciKtG0RIpc?= =?us-ascii?Q?b3j1kZBetQLc6opIdagsqH0FZuSJc7y1egb4FmHEKoqsW2+NLWdux40T3YHa?= =?us-ascii?Q?Y63MYmKh0UzZbgy1FoTRRUtdgstO+FVcadZKmCl4zEc45YO/Wlvf9iw4SvAS?= =?us-ascii?Q?ckKwq2BwERvy0OEGupwKazgthl93qmIbLNq98k2ryDXIjOLW6FJfEj/e110G?= =?us-ascii?Q?PKYY17SfttLt45dDFfsHqZn1LZxwMhqhD+zOE3jMXVib3/5mR3ZWc73K3Ye4?= =?us-ascii?Q?ab47Curw2OjmyudpQ31bR1rdnp/HteWRxSamsC3K+n/LDsn7pPQusukerwoL?= =?us-ascii?Q?dHgWp+KZYgTCc0Pw2P4u3DrJamb76okdkN6EoVx3B3x34AwMMa43XRQ2Zq2h?= =?us-ascii?Q?aoLUKaZLtfVQXoAaPQx2wwEszAhjNFHCOPFkaA4Zur0+v1ds/PmTGk8dVwuV?= =?us-ascii?Q?L3xfYNZcSvU6iOVgwZ/I7uM7kEkKDcjMO7jy4vYJpnRBngH+u9AmCfDBJZxq?= =?us-ascii?Q?PMMuDqAk8KxWJjTML+kEMjLCw4bz4r/wSM0+WmVE6IsQy4pzZXsMU8Nagihj?= =?us-ascii?Q?bhnQKX9KPTpL7G9TPxddPQY/kUYtSOtaohPuA41IrkFDmGu1KHjToLk8DEbp?= =?us-ascii?Q?d41w9CtS15jYJHjo0uEBnrmD35k0quiIzT4vZIGZavUDSJGbzK2DZlOasSHH?= =?us-ascii?Q?2DZ0BSlgKNjxdjOFBaVM+jAwpfdDOMJes+LlHuOOhg9nN29ydtqOd4zOeh7r?= =?us-ascii?Q?e/6WE9y9QjyZ/y5Btep0jUH6oecUIC8wrI1BsMK/1D1Tir+IQ9uBVxXK1sON?= =?us-ascii?Q?Yc8tM1r7b9+oxHBYQkgREInlRM804YgiGg91eSYTjPPCGuuiheopAHE2Yqza?= =?us-ascii?Q?yV5U4Lt4NoBviXCAbZOO9jVsEXmrD/3J/LL2Yymyohe7bb96LU4taxQhsdmy?= =?us-ascii?Q?Fp2MB//IotvMGngm4qLDo3tyWQGpU1XevZ5vwu5u00j2cmCINhpjDiTPKEdU?= =?us-ascii?Q?ouBFTS7s20ZiGhMoPu7/gfJ8FesX70l6becZ2fU2fQlW5eQWdep75bslQfZ9?= =?us-ascii?Q?hpS8EwaEvQ/Lm8sl0Yo0KSd1tYL/R5if5BOyfq09YEso4vm5XjCj1XDG5did?= =?us-ascii?Q?ipnt94702taDDQz+f70SGtnUL0w9vDbn+BnbCpJUELmZbsYb44yzGzqO+pNN?= =?us-ascii?Q?T3AQpaJ9EJfIglo/G1InFeHHLeQC8YaORKG7Q4fflu6Xr44NLubnvN+rX9UD?= =?us-ascii?Q?pu3xyeA2gHqvGcwu6yBIpGn9dJ1EslYBmvW41nWPjNfwdMEVBw=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:BL0PR03MB4001.namprd03.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(366016)(376014)(38070700018);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?+/SCj7rMatxycUGfhd2VwAYUh3OdNFj5eO2WsjdW57WjaltyK/olV5Ifqc0k?= =?us-ascii?Q?p5FBwQxCwYk9/WN9XTNykbPbHO6R7N3mxqTJ/jnY6+RMeCjB9coWcS3DdLd3?= =?us-ascii?Q?G2BP62j9jIgui5XUuV4WQxg+O0TMq3CbtgNdHXbMyy3hRMBDW7QLmpkzuZWu?= =?us-ascii?Q?zBaORWImyGZkhTBXzOlsiq5TFdDs9HyIE7IbAMBHCFXil1a1JIkIADVjk/UM?= =?us-ascii?Q?a1iHheaXb/rUKS7t8UJMWtnPV5eCnlRU52Xfx3dvke5qJXp1cjIT9lG6lZoN?= =?us-ascii?Q?swPSGeOk8ZaheLiq6kfu1b3yd3S1CMoYnwtHYkBbMVvLV/Gh2zQ+uHCZPUKU?= =?us-ascii?Q?+Q3oUIjU8qvMNiUQSC89TUBSthxW2y27HoOtMOqFnnhDHn220Xw1qpgKNH0u?= =?us-ascii?Q?ixE4ZnqiLkBE3M1SgsJwkokVky1je2WoeVAWtL3rIzMWpVv5h509tflWX3pS?= =?us-ascii?Q?XIVbX9FjhQwvv9KroA+WzBaw55Zzj2CR4CaBflpQPbEhGK9gHSQHF6BHCSoc?= =?us-ascii?Q?zqs1yHZ4z0oQOBUoWoOhw73AmQhnOk5KU5LTEbursvZZHJsOA/I9WwjOZ6E2?= =?us-ascii?Q?DLUHloTrkcQE2y0zTqAEZPOZKlWZQjuOCrwgUlLjBSsnyfqUrYVDvdPVK4AH?= =?us-ascii?Q?fDdclyBSIElQVv3HUT/nGtAlXMgPCFjcHFQc0ctnBqjnFraEItAc7T1+3Lkh?= =?us-ascii?Q?yIiYBhAJJAt+JTv+kd8n6EgcHLLq0GtRbLCA/+3qzDZ6ftOKUblQyzUFPFkQ?= =?us-ascii?Q?K4miMSN+Vz6nohxwL/HHfuPuiyVtNCe0rqQcljHScjaaUMaRUp7W0e/qIodQ?= =?us-ascii?Q?5EWe96rd3hWJUfKryNxLTJAj4wYia+A97h43/KWyIvp++hFRt5+BBUaFS1RO?= =?us-ascii?Q?D+3x0GGsqjyvLk+w+y3V9lYUCHLZLwIWl3MhcPYwrdwI247ucmbfb5+mFyUE?= =?us-ascii?Q?dVrcjVebuLtoDeW+d01Z58S1nDM2Z0exG1O5QIbMDb9otBjBqWrTLRIZwEg5?= =?us-ascii?Q?69I82TCZAkGERcJB1bcsZ5FyWNDRbzSA0EVn7dvb1x9+BA7BG0QW0CQ0dx9v?= =?us-ascii?Q?VhIzJ8JhmlDXDCQOXB5f2aQwrXeIkGNYMvZX7x4RO7FZz38HtRyChct4a2TU?= =?us-ascii?Q?km4s5e8sN9aOx3pLjwFkkZKIywJQhS9MnEvih7tM7kTy7CCUo/xy02f9u8/e?= =?us-ascii?Q?DlwoCPvVaO83RnCOsF+HccFeVMppHOEwDQ68dUlzIU884BbXJ39iVD9Zvdzz?= =?us-ascii?Q?UNGhZA/ng61yqPmEDwyvA3oUkFwG6Tv+uGUDKyyBlSrnLvstBi5JfAKC2gF4?= =?us-ascii?Q?eTUp/zj7asjWVP6RdEDN6xeitxTqdyhPYuwP9KTv9j93ghY5fNAuvWObzgkD?= =?us-ascii?Q?dh7hqrJozW4csMGVAUZ5eM13kuAcqgtJXALpV+aHbPYqjEB2+vP1C6HKTP78?= =?us-ascii?Q?EDBkskjQLAHv7ruornw5GQrBMm2kQ3hWtnInMTxOhYeNjVyLvHVdofC3x1gw?= =?us-ascii?Q?aFzq8UQqxZJPQYjQF1fXv6XZ8OvzmQ4H03gAdC+jNlEZaZdLeB5J29gujvlL?= =?us-ascii?Q?78Vw/hskBNEw1z+HdcMgFZ8kVrwB3pX7ZYEXdsjEfuboVVKBpP2gVZjtI5qg?= =?us-ascii?Q?ag=3D=3D?= Content-Type: multipart/alternative; boundary="_000_BL0PR03MB40010718B01C44752902BF0FFA8E2BL0PR03MB4001namp_" MIME-Version: 1.0 X-OriginatorOrg: thomsonreuters.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: BL0PR03MB4001.namprd03.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 1677d0b6-e064-497f-803c-08dcc1fb3428 X-MS-Exchange-CrossTenant-originalarrivaltime: 21 Aug 2024 16:06:23.5708 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 62ccb864-6a1a-4b5d-8e1c-397dec1a8258 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: AV3JeERuEdf1GlzEywKcoo1GrPwRNbLEssm9ltrX/EAsWXn8GaGe9lmGqsQmoUT10pb1BB33M7W6r8qDtCBQkCQLp+biUy4jFOZWGAfEOXNJOQklnlNhTFAxU7A9n0sp X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN9PR03MB6025 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_BL0PR03MB40010718B01C44752902BF0FFA8E2BL0PR03MB4001namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Aurora Postgres version 13.7. Table definition: acquisition_channel_db=3D> \d acquisition_channel.acquired_object Table "acquisition_= channel.acquired_object" Column | Type | Collat= ion | Nullable | Default ----------------------------------------+--------------------------+-------= ----+----------+-----------------------------------------------------------= ------ acquired_object_uuid | character varying(36) | = | not null | acquired_object_name | text | = | not null | acquired_object_size | bigint | = | | acquisition_run_record_id | bigint | = | | correlation_id | character varying(36) | = | | acquired_datetime | timestamp with time zone | = | | checksum | character varying(128) | = | | acquisition_method_id | bigint | = | | ol_version | integer | = | not null | created_datetime | timestamp with time zone | = | not null | updated_datetime | timestamp with time zone | = | | status | character varying(50) | = | | parent_acquired_object_uuid | character varying(36) | = | | extracted_from_object_path | text | = | | resource_group_id | bigint | = | | s3_gcs_bucket | character varying(100) | = | | s3_key | character varying(500) | = | | metadata_s3_gcs_bucket | character varying(100) | = | | metadata_s3_key | character varying(500) | = | | routing_result_acquisition_channel_id | bigint | = | | acquired_object_type | character varying(100) | = | | created_by_id | integer | = | | updated_by_id | integer | = | | metadata | jsonb | = | | acquired_object_id | bigint | = | not null | nextval('acquisition_channel.acquired_object_id_seq'::regcl= ass) routed_to_acquisition_channel_datetime | timestamp with time zone | = | | routed_to_acquisition_channel_id | bigint | = | | rendition_guid | character varying(36) | = | | revision_guid | character varying(36) | = | | channel_availability_status | character varying(100) | = | | mime_type | character varying(100) | = | | encryption_public_key_hash | text | = | | acquired_metadata | jsonb | = | | original_acquired_object_name | text | = | | acquired_family_uuid | character varying(36) | = | | last_broadcast_datetime | timestamp with time zone | = | | original_checksum | character varying(128) | = | | Indexes: "acquired_object_pkey" PRIMARY KEY, btree (acquired_object_uuid) "acquired_family_uuid_idx" btree (acquired_family_uuid) "acquired_object_acq_method_id_acq_dt_acquired_object_uuid" btree (acqu= isition_method_id, acquired_datetime DESC NULLS LAST, acquired_object_uuid) "acquired_object_acquired_items_initial_ui_page" btree (acquisition_met= hod_id, acquired_datetime DESC NULLS LAST, acquired_object_uuid) WHERE stat= us::text <> 'TEST'::text AND parent_acquired_object_uuid IS NULL "acquired_object_acquired_object_id_unq" UNIQUE, btree (acquired_object= _id) "acquired_object_acquired_object_name" btree (lower(acquired_object_nam= e)) "acquired_object_acquisition_method_id" btree (acquisition_method_id) "acquired_object_acquisition_run_record_id" btree (acquisition_run_reco= rd_id) "acquired_object_checksum" btree (checksum) "acquired_object_correlation_id" btree (correlation_id) "acquired_object_idx2" btree (parent_acquired_object_uuid, extracted_fr= om_object_path) "acquired_object_in_channel" UNIQUE, btree (routed_to_acquisition_chann= el_id, checksum) WHERE routed_to_acquisition_channel_id IS NOT NULL "acquired_object_routed_to_acq_channel_id_dt_acq_object_uuid" btree (ro= uted_to_acquisition_channel_id, routed_to_acquisition_channel_datetime DESC= NULLS LAST, acquired_object_uuid) "acquired_object_routed_to_acq_channel_id_dt_acq_object_uuid_asc" btree= (routed_to_acquisition_channel_id, routed_to_acquisition_channel_datetime,= acquired_object_uuid) "acquired_object_routed_to_acquisition_channel_id" btree (routed_to_acq= uisition_channel_id) "acquired_object_trgm_acquired_object_name" gin (lower(acquired_object_= name) acquisition_channel.gin_trgm_ops) Foreign-key constraints: "acquired_object_acquisition_channel_fk" FOREIGN KEY (routed_to_acquisi= tion_channel_id) REFERENCES acquisition_channel.acquisition_channel(acquisi= tion_channel_id) "acquired_object_fk2" FOREIGN KEY (acquisition_run_record_id) REFERENCE= S acquisition_channel.acquisition_run_record(acquisition_run_record_id) "acquired_object_fk3" FOREIGN KEY (acquisition_method_id) REFERENCES ac= quisition_channel.acquisition_method(acquisition_method_id) "acquired_object_fk4" FOREIGN KEY (parent_acquired_object_uuid) REFEREN= CES acquisition_channel.acquired_object(acquired_object_uuid) "acquired_object_fk5" FOREIGN KEY (routing_result_acquisition_channel_i= d) REFERENCES acquisition_channel.acquisition_channel(acquisition_channel_i= d) Referenced by: TABLE "acquisition_channel.acquired_object" CONSTRAINT "acquired_object= _fk4" FOREIGN KEY (parent_acquired_object_uuid) REFERENCES acquisition_chan= nel.acquired_object(acquired_object_uuid) TABLE "acquisition_channel.broadcast_item" CONSTRAINT "broadcast_item_a= cquired_object_fk" FOREIGN KEY (acquired_object_uuid) REFERENCES acquisitio= n_channel.acquired_object(acquired_object_uuid) TABLE "acquisition_channel.routing_record" CONSTRAINT "routing_record_a= cquired_object_fk" FOREIGN KEY (acquired_object_uuid) REFERENCES acquisitio= n_channel.acquired_object(acquired_object_uuid) Extension pg_trgm is installed at version 1.3 and index acquired_object_trg= m_acquired_object_name is a gin index. Query and plan: acquisition_channel_db=3D> explain (analyze, buffers) acquisition_channel_db-> select * acquisition_channel_db-> from acquisition_channel.acquired_object acquir= edob0_ acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) l= ike lower('%de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------- Limit (cost=3D46341.49..50488.39 rows=3D100 width=3D1342) (actual time=3D7= 589.846..7593.992 rows=3D1 loops=3D1) Buffers: shared hit=3D638206 read=3D1127 I/O Timings: read=3D932.828 -> Gather (cost=3D46341.49..3436021.28 rows=3D81740 width=3D1342) (act= ual time=3D7589.845..7593.990 rows=3D1 loops=3D1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=3D638206 read=3D1127 I/O Timings: read=3D932.828 -> Parallel Bitmap Heap Scan on acquired_object acquiredob0_ (co= st=3D45341.49..3426847.28 rows=3D34058 width=3D1342) (actual time=3D7584.02= 1..7584.022 rows=3D0 loops=3D3) Recheck Cond: (lower(acquired_object_name) ~~ '%de_fcdd7e0d-= 4812-4fb2-bd19-4f114f86a43f%'::text) Heap Blocks: exact=3D1 Buffers: shared hit=3D638206 read=3D1127 I/O Timings: read=3D932.828 -> Bitmap Index Scan on acquired_object_trgm_acquired_objec= t_name (cost=3D0.00..45321.05 rows=3D81740 width=3D0) (actual time=3D7589.= 148..7589.149 rows=3D1 loops=3D1) Index Cond: (lower(acquired_object_name) ~~ '%de_fcdd7= e0d-4812-4fb2-bd19-4f114f86a43f%'::text) Buffers: shared hit=3D638205 read=3D1127 I/O Timings: read=3D932.828 Planning: Buffers: shared hit=3D7 Planning Time: 0.209 ms Execution Time: 7594.346 ms It is using the GIN index but it did 639k total buffer reads to find 1 row = which seems like a ton. I don't know how GIN/GIN_TRGM_OPS are implemented = but does 638k buffer reads to find 1 row using that type of index seem high= or does that seem as expected? I noticed for this 1 row returned the acquired_object_name starts with de_ = so the leading % isn't really needed for this data point . There is a dif= ferent index that is a normal btree index that could be used by this query = if the leading % was removed: acquired_object_acquired_object_name btree (lower(acquired_object_name)) Here is the plan if I remove the leading %: acquisition_channel_db=3D> explain (analyze, buffers) acquisition_channel_db-> select * acquisition_channel_db-> from acquisition_channel.acquired_object acquir= edob0_ acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) l= ike lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------- Limit (cost=3D48145.49..52292.39 rows=3D100 width=3D1342) (actual time=3D8= 639.428..8643.529 rows=3D1 loops=3D1) Buffers: shared hit=3D827759 read=3D143 I/O Timings: read=3D119.373 -> Gather (cost=3D48145.49..3437825.28 rows=3D81740 width=3D1342) (act= ual time=3D8639.427..8643.527 rows=3D1 loops=3D1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=3D827759 read=3D143 I/O Timings: read=3D119.373 -> Parallel Bitmap Heap Scan on acquired_object acquiredob0_ (co= st=3D47145.49..3428651.28 rows=3D34058 width=3D1342) (actual time=3D8633.20= 0..8633.201 rows=3D0 loops=3D3) Recheck Cond: (lower(acquired_object_name) ~~ 'de_fcdd7e0d-4= 812-4fb2-bd19-4f114f86a43f%'::text) Heap Blocks: exact=3D1 Buffers: shared hit=3D827759 read=3D143 I/O Timings: read=3D119.373 -> Bitmap Index Scan on acquired_object_trgm_acquired_objec= t_name (cost=3D0.00..47125.05 rows=3D81740 width=3D0) (actual time=3D8638.= 786..8638.787 rows=3D1 loops=3D1) Index Cond: (lower(acquired_object_name) ~~ 'de_fcdd7e= 0d-4812-4fb2-bd19-4f114f86a43f%'::text) Buffers: shared hit=3D827758 read=3D143 I/O Timings: read=3D119.373 Planning: Buffers: shared hit=3D7 Planning Time: 0.226 ms Execution Time: 8643.664 ms I was surprised the optimizer decided to still use the gin index vs the acq= uired_object_acquired_object_name index. And now the query did 827k buffer= reads to find that 1 row via the index. I tried using pg_hint_plan to get= the query to use the acquired_object_acquired_object_name index but the op= timizer still wouldn't use it. So then I decided to disable bitmap scan to= see if it would then use this other index (this time I'm just doing an exp= lain to see the plan): acquisition_channel_db=3D> set enable_bitmapscan to off; SET acquisition_channel_db=3D> explain acquisition_channel_db-> select * acquisition_channel_db-> from acquisition_channel.acquired_object acquir= edob0_ acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) l= ike lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------ Limit (cost=3D1000.00..122287.58 rows=3D100 width=3D1342) -> Gather (cost=3D1000.00..99141466.60 rows=3D81740 width=3D1342) Workers Planned: 2 -> Parallel Seq Scan on acquired_object acquiredob0_ (cost=3D0.0= 0..99132292.60 rows=3D34058 width=3D1342) Filter: (lower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4f= b2-bd19-4f114f86a43f%'::text) Wow, so rather than using the acquired_object_acquired_object_name it thin= ks a full scan of the table would be better.... The table is 718 GB. It di= dn't even want to use that gin index- when accessing a gin index can that = only be done via a bitmap index scan? Something seems out of whack here bu= t I don't know what it is. Leaving enable_bitmapscan to off I also tried d= isabling seqscan- the plan below shows it will still to a seq scan and not= e the cost now. acquisition_channel_db=3D> set enable_seqscan to off; SET acquisition_channel_db=3D> explain acquisition_channel_db-> select * acquisition_channel_db-> from acquisition_channel.acquired_object acquir= edob0_ acquisition_channel_db-> where lower(acquiredob0_.acquired_object_name) l= ike lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%') limit 100; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------- Limit (cost=3D10000000000.00..10000130032.86 rows=3D100 width=3D1342) -> Seq Scan on acquired_object acquiredob0_ (cost=3D10000000000.00..10= 106288859.64 rows=3D81740 width=3D1342) Filter: (lower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4fb2-bd1= 9-4f114f86a43f%'::text) My questions: 1. Does 638k buffer reads using the gin index to find 1 row seem appropr= iate? That seems like a ton of work to me. 2. When I remove the leading % it still uses the gin index but now it do= es 827k buffer reads to find 1 row. Why does it still want to use this gin= index vs using the other index I note above and why is it doing even more = work when removing the leading %? 3. If I disable bitmap scan it then prefers to full scan the table vs us= ing the other index I note above. Why would it still not want to use this = other index? 4. If I also disable seqscan it still won't use this other index. Why? I don't know what other info would be helpful here but I'll show settings r= elated to Query Tuning / Planner Method Configuration (after re-logging in = to show current settings) along with table/index statistics. I assume I'm = overlooking something rather simple here but I'm not seeing it. Any input = would be much appreciated. Regards Steve acquisition_channel_db=3D> select name, setting from pg_settings where cate= gory =3D 'Query Tuning / Planner Method Configuration'; name | setting -----------------------------------------+--------- apg_enable_correlated_any_transform | off apg_enable_function_migration | on apg_enable_not_in_transform | on apg_enable_remove_redundant_inner_joins | off apg_enable_semijoin_push_down | off apg_force_full_key_semijoin | off apg_force_semijoin_push_down | off apg_force_single_key_semijoin | off enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on enable_hashjoin | on enable_incremental_sort | on enable_indexonlyscan | on enable_indexscan | on enable_material | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on enable_parallel_hash | on enable_partition_pruning | on enable_partitionwise_aggregate | off enable_partitionwise_join | off enable_seqscan | on enable_sort | on enable_tidscan | on (26 rows) acquisition_channel_db=3D> \x on Expanded display is on. acquisition_channel_db=3D> select * FROM pg_stat_user_tables where relnam= e =3D 'acquired_object'; -[ RECORD 1 ]-------+------------------------------ relid | 16777 schemaname | acquisition_channel relname | acquired_object seq_scan | 1218 seq_tup_read | 307573542582 idx_scan | 2260152042 idx_tup_fetch | 94409160669 n_tup_ins | 140420690 n_tup_upd | 642849845 n_tup_del | 0 n_tup_hot_upd | 251641126 n_live_tup | 821351776 n_dead_tup | 26646304 n_mod_since_analyze | 18564560 n_ins_since_vacuum | 7485136 last_vacuum | last_autovacuum | 2024-08-17 15:39:04.259598+00 last_analyze | last_autoanalyze | 2024-08-20 06:46:16.897584+00 vacuum_count | 0 autovacuum_count | 6 analyze_count | 0 autoanalyze_count | 21 acquisition_channel_db=3D> select * from pg_stats where tablename =3D 'acqu= ired_object' and attname =3D 'acquired_object_name'; -[ RECORD 1 ]----------+---------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------- schemaname | acquisition_channel tablename | acquired_object attname | acquired_object_name inherited | f null_frac | 0 avg_width | 43 n_distinct | 3.053536e+06 most_common_vals | {.xxxxxxxx.xxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.= pdf,yyyyyyyyyyyyyyyyyyyyyyyyyyyy.pdf} <--- replace actual values due to po= ssible concerns with values shown most_common_freqs | {0.00026666667,0.00016666666,0.00016666666} histogram_bounds | {removing values also due to possible concerns wit= h values shown} correlation | 0.1324247 most_common_elems | most_common_elem_freqs | elem_count_histogram | This e-mail is for the sole use of the intended recipient and contains info= rmation that may be privileged and/or confidential. If you are not an inten= ded recipient, please notify the sender by return e-mail and delete this e-= mail and any attachments. Certain required legal entity disclosures can be = accessed on our website: https://www.thomsonreuters.com/en/resources/disclo= sures.html --_000_BL0PR03MB40010718B01C44752902BF0FFA8E2BL0PR03MB4001namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Aurora Postgres version 13.7.

 

Table definition:

 

acquisition_channel_db=3D> \d acquisition_channel.acquir= ed_object

          = ;            &n= bsp;            = ;            &n= bsp;        Table "acquisition_chan= nel.acquired_object"

          = ;       Column     &= nbsp;           | &n= bsp;         Type   =         | Collation | Nullable | &n= bsp;            = ;            &n= bsp;  Default

----------------------------------------+------------------= --------+-----------+----------+-------------------------------------------= ----------------------

acquired_object_uuid      &nb= sp;            | cha= racter varying(36)    |      &= nbsp;    | not null |

acquired_object_name      &nb= sp;            | tex= t            &n= bsp;        |    &nb= sp;      | not null |

acquired_object_size      &nb= sp;            | big= int            =        |      &= nbsp;    |        &n= bsp; |

acquisition_run_record_id     &nbs= p;        | bigint   &nbs= p;            &= nbsp;  |           |=           |<= /p>

correlation_id       &nb= sp;            =      | character varying(36)    | &= nbsp;         |   &n= bsp;      |

acquired_datetime       =             &nb= sp;  | timestamp with time zone |      &= nbsp;    |        &n= bsp; |

checksum        &nb= sp;            =           | character varying(= 128)   |         &nb= sp; |          |

acquisition_method_id      &n= bsp;           | bigint&n= bsp;            = ;      |       =     |          = |

ol_version        &= nbsp;           &nbs= p;        | integer   &nb= sp;            =   |           | not = null |

created_datetime       &= nbsp;           &nbs= p;   | timestamp with time zone |     &n= bsp;     | not null |

updated_datetime       &= nbsp;           &nbs= p;   | timestamp with time zone |     &n= bsp;     |       &nb= sp;  |

status         = ;            &n= bsp;           | characte= r varying(50)    |       =     |          = |

parent_acquired_object_uuid     &n= bsp;      | character varying(36)   = ; |           | &nbs= p;        |

extracted_from_object_path     &nb= sp;       | text     = ;            &n= bsp;   |         &nb= sp; |          |

resource_group_id       =             &nb= sp;  | bigint         &nb= sp;         |   &nbs= p;       |      = ;    |

s3_gcs_bucket       &nbs= p;            &= nbsp;     | character varying(100)   | &= nbsp;         |   &n= bsp;      |

s3_key         = ;            &n= bsp;           | characte= r varying(500)   |        = ;   |          |

metadata_s3_gcs_bucket      &= nbsp;          | character var= ying(100)   |        &nbs= p;  |          |

metadata_s3_key       &n= bsp;            = ;    | character varying(500)   |   = ;        |     =      |

routing_result_acquisition_channel_id  | bigint &= nbsp;           &nbs= p;     |        = ;   |          |

acquired_object_type      &nb= sp;            | cha= racter varying(100)   |       =     |          = |

created_by_id       &nbs= p;            &= nbsp;     | integer      =             | &= nbsp;         |   &n= bsp;      |

updated_by_id       &nbs= p;            &= nbsp;     | integer      =             | &= nbsp;         |   &n= bsp;      |

metadata        &nb= sp;            =           | jsonb  &= nbsp;           &nbs= p;     |        = ;   |          |

acquired_object_id       = ;            &n= bsp; | bigint          &n= bsp;        |    &nb= sp;      | not null | nextval('acquisition_channel= .acquired_object_id_seq'::regclass)

routed_to_acquisition_channel_datetime | timestamp with tim= e zone |           | = ;         |

routed_to_acquisition_channel_id    &nb= sp;  | bigint         &nb= sp;         |   &nbs= p;       |      = ;    |

rendition_guid       &nb= sp;            =      | character varying(36)    | &= nbsp;         |   &n= bsp;      |

revision_guid       &nbs= p;            &= nbsp;     | character varying(36)    |&n= bsp;          |  &nb= sp;       |

channel_availability_status     &n= bsp;      | character varying(100)   |&n= bsp;          |  &nb= sp;       |

mime_type        &n= bsp;            = ;         | character varying(100)&= nbsp;  |           |=           |<= /p>

encryption_public_key_hash     &nb= sp;       | text     = ;            &n= bsp;   |         &nb= sp; |          |

acquired_metadata       =             &nb= sp;  | jsonb         &nbs= p;          |   = ;        |     =      |

original_acquired_object_name     =      | text       &n= bsp;            = ; |           | &nbs= p;        |

acquired_family_uuid      &nb= sp;            | cha= racter varying(36)    |      &= nbsp;    |        &n= bsp; |

last_broadcast_datetime      =           | timestamp with tim= e zone |           | = ;         |

original_checksum       =             &nb= sp;  | character varying(128)   |    &nb= sp;      |      &nbs= p;   |

Indexes:

    "acquired_object_pkey" PRIMARY= KEY, btree (acquired_object_uuid)

    "acquired_family_uuid_idx" btr= ee (acquired_family_uuid)

    "acquired_object_acq_method_id_acq_= dt_acquired_object_uuid" btree (acquisition_method_id, acquired_dateti= me DESC NULLS LAST, acquired_object_uuid)

    "acquired_object_acquired_items_ini= tial_ui_page" btree (acquisition_method_id, acquired_datetime DESC NUL= LS LAST, acquired_object_uuid) WHERE status::text <> 'TEST'::text AND= parent_acquired_object_uuid IS NULL

    "acquired_object_acquired_object_id= _unq" UNIQUE, btree (acquired_object_id)

    "acquired_object_acquired_object_na= me" btree (lower(acquired_object_name))

    "acquired_object_acquisition_method= _id" btree (acquisition_method_id)

    "acquired_object_acquisition_run_re= cord_id" btree (acquisition_run_record_id)

    "acquired_object_checksum" btr= ee (checksum)

    "acquired_object_correlation_id&quo= t; btree (correlation_id)

    "acquired_object_idx2" btree (= parent_acquired_object_uuid, extracted_from_object_path)<= /p>

    "acquired_object_in_channel" U= NIQUE, btree (routed_to_acquisition_channel_id, checksum) WHERE routed_to_a= cquisition_channel_id IS NOT NULL

    "acquired_object_routed_to_acq_chan= nel_id_dt_acq_object_uuid" btree (routed_to_acquisition_channel_id, ro= uted_to_acquisition_channel_datetime DESC NULLS LAST, acquired_object_uuid)=

    "acquired_object_routed_to_acq_chan= nel_id_dt_acq_object_uuid_asc" btree (routed_to_acquisition_channel_id= , routed_to_acquisition_channel_datetime, acquired_object_uuid)<= /span>

    "acquired_object_routed_to_acquisit= ion_channel_id" btree (routed_to_acquisition_channel_id)

    "acquired_object_trgm_acquired_obje= ct_name" gin (lower(acquired_object_name) acquisition_channel.gin_trgm= _ops)

Foreign-key constraints:

    "acquired_object_acquisition_channe= l_fk" FOREIGN KEY (routed_to_acquisition_channel_id) REFERENCES acquis= ition_channel.acquisition_channel(acquisition_channel_id)=

    "acquired_object_fk2" FOREIGN = KEY (acquisition_run_record_id) REFERENCES acquisition_channel.acquisition_= run_record(acquisition_run_record_id)

    "acquired_object_fk3" FOREIGN = KEY (acquisition_method_id) REFERENCES acquisition_channel.acquisition_meth= od(acquisition_method_id)

    "acquired_object_fk4" FOREIGN = KEY (parent_acquired_object_uuid) REFERENCES acquisition_channel.acquired_o= bject(acquired_object_uuid)

    "acquired_object_fk5" FOREIGN = KEY (routing_result_acquisition_channel_id) REFERENCES acquisition_channel.= acquisition_channel(acquisition_channel_id)

Referenced by:

    TABLE "acquisition_channel.acquired= _object" CONSTRAINT "acquired_object_fk4" FOREIGN KEY (paren= t_acquired_object_uuid) REFERENCES acquisition_channel.acquired_object(acqu= ired_object_uuid)

    TABLE "acquisition_channel.broadcas= t_item" CONSTRAINT "broadcast_item_acquired_object_fk" FOREI= GN KEY (acquired_object_uuid) REFERENCES acquisition_channel.acquired_objec= t(acquired_object_uuid)

    TABLE "acquisition_channel.routing_= record" CONSTRAINT "routing_record_acquired_object_fk" FOREI= GN KEY (acquired_object_uuid) REFERENCES acquisition_channel.acquired_objec= t(acquired_object_uuid)

 

Extension pg_trgm is installed at version 1.3 and in= dex acquired_object_trgm_acquired_object_name is a gin index.

 

Query and plan:

 

acquisition_channel_db=3D> explain (analyze, buffers)

acquisition_channel_db->  select *

acquisition_channel_db->    from acquisit= ion_channel.acquired_object acquiredob0_

acquisition_channel_db->   where lower(acquire= dob0_.acquired_object_name) like lower('%de_fcdd7e0d-4812-4fb2-bd19-4f114f8= 6a43f%') limit 100;

          = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;        QUERY PLAN=

-----------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------

Limit  (cost=3D46341.49..50488.39 rows=3D100 width=3D1= 342) (actual time=3D7589.846..7593.992 rows=3D1 loops=3D1)

   Buffers: shared hit=3D638206 read=3D1127<= /o:p>

   I/O Timings: read=3D932.828<= /p>

   ->  Gather  (cost=3D46341.49..343= 6021.28 rows=3D81740 width=3D1342) (actual time=3D7589.845..7593.990 rows= =3D1 loops=3D1)

         Workers Pl= anned: 2

         Workers La= unched: 2

         Buffers: s= hared hit=3D638206 read=3D1127

         I/O Timing= s: read=3D932.828

         -> = ; Parallel Bitmap Heap Scan on acquired_object acquiredob0_  (cost=3D4= 5341.49..3426847.28 rows=3D34058 width=3D1342) (actual time=3D7584.021..758= 4.022 rows=3D0 loops=3D3)

          = ;     Recheck Cond: (lower(acquired_object_name) ~~ '%d= e_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)

          = ;     Heap Blocks: exact=3D1

          = ;     Buffers: shared hit=3D638206 read=3D1127

          = ;     I/O Timings: read=3D932.828

          = ;     ->  Bitmap Index Scan on acquired_object_= trgm_acquired_object_name  (cost=3D0.00..45321.05 rows=3D81740 width= =3D0) (actual time=3D7589.148..7589.149 rows=3D1 loops=3D1)

          = ;           Index Cond: (= lower(acquired_object_name) ~~ '%de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%':= :text)

          = ;           Buffers: shar= ed hit=3D638205 read=3D1127

          = ;           I/O Timings: = read=3D932.828

Planning:

   Buffers: shared hit=3D7

Planning Time: 0.209 ms

Execution Time: 7594.346 ms

 

It is using the GIN index but it did 639k total buff= er reads to find 1 row which seems like a ton.  I don’t know how= GIN/GIN_TRGM_OPS are implemented but does 638k buffer reads to find 1 row = using that type of index seem high or does that seem as expected?

 

I noticed for this 1 row returned the acquired_objec= t_name starts with de_  so the leading % isn’t really needed for= this data point .  There is a different index that is a normal btree = index that could be used by this query if the leading % was removed:

 

acquired_object_acquired_object_name btree (lower(acquired_= object_name))

 

Here is the plan if I remove the leading %:

 

acquisition_channel_db=3D> explain (analyze, buffers)

acquisition_channel_db->  select *

acquisition_channel_db->    from acquisit= ion_channel.acquired_object acquiredob0_

acquisition_channel_db->   where lower(acquire= dob0_.acquired_object_name) like lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86= a43f%') limit 100;

          = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;        QUERY PLAN=

-----------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------

Limit  (cost=3D48145.49..52292.39 rows=3D100 width=3D1= 342) (actual time=3D8639.428..8643.529 rows=3D1 loops=3D1)

   Buffers: shared hit=3D827759 read=3D143

   I/O Timings: read=3D119.373<= /p>

   ->  Gather  (cost=3D48145.49..343= 7825.28 rows=3D81740 width=3D1342) (actual time=3D8639.427..8643.527 rows= =3D1 loops=3D1)

         Workers Pl= anned: 2

         Workers La= unched: 2

         Buffers: s= hared hit=3D827759 read=3D143

         I/O Timing= s: read=3D119.373

         -> = ; Parallel Bitmap Heap Scan on acquired_object acquiredob0_  (cost=3D4= 7145.49..3428651.28 rows=3D34058 width=3D1342) (actual time=3D8633.200..863= 3.201 rows=3D0 loops=3D3)

          = ;     Recheck Cond: (lower(acquired_object_name) ~~ 'de= _fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::text)

          = ;     Heap Blocks: exact=3D1

          = ;     Buffers: shared hit=3D827759 read=3D143

          = ;     I/O Timings: read=3D119.373

          = ;     ->  Bitmap Index Scan on acquired_object_= trgm_acquired_object_name  (cost=3D0.00..47125.05 rows=3D81740 width= =3D0) (actual time=3D8638.786..8638.787 rows=3D1 loops=3D1)

          = ;           Index Cond: (= lower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::= text)

          = ;           Buffers: shar= ed hit=3D827758 read=3D143

          = ;           I/O Timings: = read=3D119.373

Planning:

   Buffers: shared hit=3D7

Planning Time: 0.226 ms

Execution Time: 8643.664 ms

 

I was surprised the optimizer decided to still use t= he gin index vs the acquired_object_acquired_object_name index.  And n= ow the query did 827k buffer reads to find that 1 row via the index.  = I tried using pg_hint_plan to get the query to use the acquired_object_acquired_object_name index but the optimizer st= ill wouldn’t use it.  So then I decided to disable bitmap scan t= o see if it would then use this other index (this time I’m just doing= an explain to see the plan):

 

acquisition_channel_db=3D> set enable_bitmapscan to off;=

SET

acquisition_channel_db=3D> explain

acquisition_channel_db->  select *

acquisition_channel_db->    from acquisit= ion_channel.acquired_object acquiredob0_

acquisition_channel_db->   where lower(acquire= dob0_.acquired_object_name) like lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86= a43f%') limit 100;

          = ;            &n= bsp;            = ;            &n= bsp;  QUERY PLAN

-----------------------------------------------------------= ----------------------------------------------------

Limit  (cost=3D1000.00..122287.58 rows=3D100 width=3D1= 342)

   ->  Gather  (cost=3D1000.00..9914= 1466.60 rows=3D81740 width=3D1342)

         Workers Pl= anned: 2

         -> = ; Parallel Seq Scan on acquired_object acquiredob0_  (cost=3D0.00..991= 32292.60 rows=3D34058 width=3D1342)

          = ;     Filter: (lower(acquired_object_name) ~~ 'de_fcdd7= e0d-4812-4fb2-bd19-4f114f86a43f%'::text)

 

Wow, so rather than using  the acquired_object_= acquired_object_name it thinks a full scan of the table would be betterR= 30;. The table is 718 GB.  It didn’t even want to use that gin i= ndex-  when accessing a gin index can that only be done via a bitmap index scan?  Something seems out of whack here but I don= ’t know what it is.  Leaving enable_bitmapscan to off I also tri= ed disabling seqscan-  the plan below shows it will still to a seq sca= n and note the cost now.

 

acquisition_channel_db=3D> set enable_seqscan to off;

SET

acquisition_channel_db=3D> explain

acquisition_channel_db->  select *

acquisition_channel_db->    from acquisit= ion_channel.acquired_object acquiredob0_

acquisition_channel_db->   where lower(acquire= dob0_.acquired_object_name) like lower('de_fcdd7e0d-4812-4fb2-bd19-4f114f86= a43f%') limit 100;

          = ;            &n= bsp;            = ;            &n= bsp; QUERY PLAN

-----------------------------------------------------------= --------------------------------------------------

Limit  (cost=3D10000000000.00..10000130032.86 rows=3D1= 00 width=3D1342)

   ->  Seq Scan on acquired_object acquir= edob0_  (cost=3D10000000000.00..10106288859.64 rows=3D81740 width=3D13= 42)

         Filter: (l= ower(acquired_object_name) ~~ 'de_fcdd7e0d-4812-4fb2-bd19-4f114f86a43f%'::t= ext)

 

 

My questions:

  1. Does 638k buffer reads using the gin index to find 1 row seem appropr= iate?  That seems like a ton of work to me.
  2. Whe= n I remove the leading % it still uses the gin index but now it does 827k b= uffer reads to find 1 row.  Why does it still want to use this gin ind= ex vs using the other index I note above and why is it doing even more work when removing the leading %?=
  3. If I disable bitmap scan it then prefers to full scan the = table vs using the other index I note above.  Why would it still not w= ant to use this other index?
  4. If I also disable seqscan= it still won’t use this other index.  Why?

 

I don’t know what other info would be helpful = here but I’ll show settings related to Query Tuning / Planner Method = Configuration (after re-logging in to show current settings) along with tab= le/index statistics.  I assume I’m overlooking something rather simple here but I’m not seeing it.  Any input = would be much appreciated.

 

Regards

Steve

 

 

acquisition_channel_db=3D> select name, setting from pg_= settings where category =3D 'Query Tuning / Planner Method Configuration';<= o:p>

          = ;        name    &nb= sp;            =   | setting

-----------------------------------------+---------

apg_enable_correlated_any_transform    = | off

apg_enable_function_migration     =       | on

apg_enable_not_in_transform     &n= bsp;       | on

apg_enable_remove_redundant_inner_joins | off

apg_enable_semijoin_push_down     =       | off

apg_force_full_key_semijoin     &n= bsp;       | off

apg_force_semijoin_push_down     &= nbsp;      | off

apg_force_single_key_semijoin     =       | off

enable_bitmapscan       =             &nb= sp;   | on

enable_gathermerge       = ;            &n= bsp;  | on

enable_hashagg       &nb= sp;            =       | on

enable_hashjoin       &n= bsp;            = ;     | on

enable_incremental_sort      =            | on

enable_indexonlyscan      &nb= sp;            = | on

enable_indexscan       &= nbsp;           &nbs= p;    | on

enable_material       &n= bsp;            = ;     | on

enable_mergejoin       &= nbsp;           &nbs= p;    | on

enable_nestloop       &n= bsp;            = ;     | on

enable_parallel_append      &= nbsp;           | on=

enable_parallel_hash      &nb= sp;            = | on

enable_partition_pruning      = ;          | on

enable_partitionwise_aggregate     = ;     | off

enable_partitionwise_join     &nbs= p;         | off<= /p>

enable_seqscan       &nb= sp;            =       | on

enable_sort        =             &nb= sp;        | on

enable_tidscan       &nb= sp;            =       | on

(26 rows)

 

acquisition_channel_db=3D> \x on

Expanded display is on.

 

 

 

acquisition_channel_db=3D>   select * FROM pg_= stat_user_tables where relname =3D 'acquired_object';

-[ RECORD 1 ]-------+------------------------------

relid         =       | 16777

schemaname        &= nbsp; | acquisition_channel

relname        &nbs= p;    | acquired_object

seq_scan        &nb= sp;   | 1218

seq_tup_read        | 30= 7573542582

idx_scan        &nb= sp;   | 2260152042

idx_tup_fetch       | 9440916= 0669

n_tup_ins        &n= bsp;  | 140420690

n_tup_upd        &n= bsp;  | 642849845

n_tup_del        &n= bsp;  | 0

n_tup_hot_upd       | 2516411= 26

n_live_tup        &= nbsp; | 821351776

n_dead_tup        &= nbsp; | 26646304

n_mod_since_analyze | 18564560

n_ins_since_vacuum  | 7485136

last_vacuum        = |

last_autovacuum     | 2024-08-17 15:39:= 04.259598+00

last_analyze        |

last_autoanalyze    | 2024-08-20 06:46:16.89= 7584+00

vacuum_count        | 0<= o:p>

autovacuum_count    | 6

analyze_count       | 0<= /o:p>

autoanalyze_count   | 21

 

 

acquisition_channel_db=3D> select * from pg_stats where = tablename =3D 'acquired_object' and attname =3D 'acquired_object_name';

-[ RECORD 1 ]----------+-----------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------

schemaname        &= nbsp;    | acquisition_channel

tablename        &n= bsp;     | acquired_object

attname        &nbs= p;       | acquired_object_name

inherited        &n= bsp;     | f

null_frac        &n= bsp;     | 0

avg_width        &n= bsp;     | 43

n_distinct        &= nbsp;    | 3.053536e+06

most_common_vals       | {.xx= xxxxxx.xxxxxx,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.pdf,yyyyyyyyyyyyyyyyyyyyyyyyy= yyy.pdf}  ß= - repla= ce actual values due to possible concerns with values shown

most_common_freqs      | {0.000266= 66667,0.00016666666,0.00016666666}

histogram_bounds       | {rem= oving values also due to possible concerns with values shown}

correlation        =     | 0.1324247

most_common_elems      |

most_common_elem_freqs |

elem_count_histogram   |

 

 

 

 

 

 

This e-mail is for the sole use of the intended recipient and contains info= rmation that may be privileged and/or confidential. If you are not an inten= ded recipient, please notify the sender by return e-mail and delete this e-= mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://= www.thomsonreuters.com/en/resources/disclosures.html --_000_BL0PR03MB40010718B01C44752902BF0FFA8E2BL0PR03MB4001namp_--