Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ee16I-0003RW-D9 for pgsql-performance@arkaria.postgresql.org; Tue, 23 Jan 2018 16:07:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ee16H-0002Da-Fb for pgsql-performance@arkaria.postgresql.org; Tue, 23 Jan 2018 16:07:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1edzFR-0001jF-Pw for pgsql-performance@lists.postgresql.org; Tue, 23 Jan 2018 14:08:25 +0000 Received: from mail-sn1nam02on0059.outbound.protection.outlook.com ([104.47.36.59] helo=NAM02-SN1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1edzFN-0001NO-93 for pgsql-performance@postgresql.org; Tue, 23 Jan 2018 14:08:25 +0000 Received: from BL2NAM02FT057.eop-nam02.prod.protection.outlook.com (10.152.76.58) by BL2NAM02HT011.eop-nam02.prod.protection.outlook.com (10.152.76.150) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.20.428.12; Tue, 23 Jan 2018 14:08:17 +0000 Authentication-Results: spf=pass (sender IP is 104.156.72.34) smtp.mailfrom=ultra-ft.com; postgresql.org; dkim=none (message not signed) header.d=none;postgresql.org; dmarc=bestguesspass action=none header.from=ultra-ft.com; Received-SPF: Pass (protection.outlook.com: domain of ultra-ft.com designates 104.156.72.34 as permitted sender) receiver=protection.outlook.com; client-ip=104.156.72.34; helo=SRV-CAMTL-EXCH2.Forensictech.com; Received: from SRV-CAMTL-EXCH2.Forensictech.com (104.156.72.34) by BL2NAM02FT057.mail.protection.outlook.com (10.152.77.36) with Microsoft SMTP Server (version=TLS1_0, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384) id 15.20.428.12 via Frontend Transport; Tue, 23 Jan 2018 14:08:17 +0000 Received: from SRV-CAMTL-EXCH2.Forensictech.com ([172.30.10.87]) by SRV-CAMTL-EXCH2.Forensictech.com ([172.30.10.87]) with mapi id 14.03.0361.001; Tue, 23 Jan 2018 09:08:17 -0500 From: Jean-Marc Lessard To: "pgsql-performance@postgresql.org" Subject: RE: Inefficient full seq scan on pg_largeobject instead of index scan Thread-Topic: Inefficient full seq scan on pg_largeobject instead of index scan Thread-Index: AdOUUujLAgHyaZPbQMCC2+tGA5hEgAAAK3/g Date: Tue, 23 Jan 2018 14:08:17 +0000 Message-ID: <8FC5F25FF3EC4744ADFCF20CBA3F44BE01246C503E@SRV-CAMTL-EXCH2.Forensictech.com> References: <8FC5F25FF3EC4744ADFCF20CBA3F44BE01246C501A@SRV-CAMTL-EXCH2.Forensictech.com> In-Reply-To: <8FC5F25FF3EC4744ADFCF20CBA3F44BE01246C501A@SRV-CAMTL-EXCH2.Forensictech.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [172.30.33.140] x-c2processedorg: 845c9e2e-433d-4b81-a451-faf8842d96b4 Content-Type: multipart/alternative; boundary="_000_8FC5F25FF3EC4744ADFCF20CBA3F44BE01246C503ESRVCAMTLEXCH2_" MIME-Version: 1.0 X-EOPAttributedMessage: 0 X-Forefront-Antispam-Report: CIP:104.156.72.34;IPV:NLI;CTRY:;EFV:NLI;SFV:NSPM;SFS:(10009020)(39840400004)(39380400002)(376002)(396003)(346002)(2980300002)(438002)(189003)(199004)(9326002)(8936002)(81156014)(8676002)(790700001)(3846002)(106002)(6116002)(316002)(16586007)(229853002)(69596002)(2906002)(236005)(6306002)(9686003)(54896002)(6246003)(5640700003)(53386004)(55016002)(33656002)(15974865002)(336011)(81166006)(7736002)(606006)(106466001)(2950100002)(6916009)(2501003)(5660300001)(14454004)(2351001)(2920100001)(7696005)(102836004)(26005)(77096007)(76176011)(478600001)(2900100001)(104016004)(72206003)(53416004)(86362001)(59450400001)(55846006)(356003)(84326002)(460985005);DIR:OUT;SFP:1101;SCL:1;SRVR:BL2NAM02HT011;H:SRV-CAMTL-EXCH2.Forensictech.com;FPR:;SPF:Pass;PTR:ip-34-72-156-104.static.fibrenoire.ca;MX:1;A:1;LANG:; X-Microsoft-Exchange-Diagnostics: 1;BL2NAM02FT057;1:kkX/xJV/kMXKf0SYmeHKf0CN9sRXwKGoL6OUHbwtynC2oeetJmh31vZi8PwYjXvrIW9+8fHy7QlI+KIprL3D/ZRr7GXd+WZ2GqhtgHlxuXDwa89rfvIieIs4rPOaH+X3 X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id: edbe9883-bb2d-469c-cd68-08d5626ac088 X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:(7020095)(4652020)(5600026)(4604075)(4608076)(4534165)(4627221)(201703031133081)(201702281549075)(2017052603307)(7153060)(7193020);SRVR:BL2NAM02HT011; X-Microsoft-Exchange-Diagnostics: 1;BL2NAM02HT011;3:8cZ4frYEZlGK+bGZofmsbDbzHSXRhAXsT35e8Z61rBZrl8O0WvnLFgasSsSbnGVWjE6qZVM8niSHZQIDZ8Dk5hdPvJVSBpawlr4DoQpLYfynGuoNfd8t12CdUSlzXuU1tJItux38aDScNI49/H1aE8C3jQxW0IiK7VINH7g3WMLZEGHrH68tSC8qS4Bl9+lqhuv6ID5p3/FUlgA+z74lIdOnGeDdnh6WjgpUQ9khzplYAhkl3tXH7rNl2G9mEWNGJ0gJDT2WT/HLqpcZpPabSrkaOaLESYp2kX30S0QGLSgGiMUd0udDkshLp1G9qVhbqpvGTHASOvrDsg7vmhJ+DA==;25:Cd4VrNHKfiWvpjs3A6fd48/NHrtvZqm+G+UNU9yDmM3n41m+mW6v9Sie36nsZd47MF86ytCacfkTNHe56pSVLzyhyfzYCemC5M1b7mSc0GiWxb1wEJYXShkWwutjIjp22V44NpZCNeHqSBE1NHZJFsOK3ofXf1nbeJZZK3Gws9NmvWrxaukJvFgNggiqN/ckgSuHfzrcrka777Ax9U7aoxzk2UeiDhOBFFhTHV8DKsi/CLLS/OaRiTLfDpU1tEoyUEGiUjVhTgjxPEdwXhlW3Jqhok5YOLy2ZiWgG+/MS6icBr95hWtGHKe5kmuihmqHc6tPQvacR74iNvx5iTve6w==;31:30Yd+87FpdnOVI9dYKo6SnJnnJz03mHxVa1g4dGDuruh0RONiwRKY+F3kut7ogf/HHHf9L6KPPmMlQ/5LffJlxJDHlRd67u5mDPxxOsuoOjWXMUSfA9nET4TQuYBaROkIQvBRKz0FZttLzaAFXQ5xseQTzt5RDvOQH3Tuv3Yy9M8K+9mxwqixJRAI/ZILiNzY4UwO6e2QZRpJ8oe+IHCQ7FFnfxYq0QeTrT2KW9kWo4= X-MS-TrafficTypeDiagnostic: BL2NAM02HT011: X-Microsoft-Exchange-Diagnostics: 1;BL2NAM02HT011;20:GnsXlcayUUmK4glH6NkwLujJ64gqi0+lF+DIA8hDtUsk50LxGTEJ78cHXZRIRV8CPWHyJBNAPSNR4QXjMPVMaDGaRseFwN9xU6zcZzhf8AxGRSKiEj7WJHbjXaQm84NwL9SnnFxJyT8bFwPGBKcglrJ9NroQlyTrVkZwJExsNpiSRn7a90z1ASswD7NXDIHfE1FaI70xdtgzS3Fc8gmSMO/lQRT4BVIjh1oX9971Uj0bQVekRqXUktz7WeTxZI1dUpZ3GHO3nQKiYMK5fQh0P+yFw5yvJDBptrvgxzIj5V47UteOXfQiy5x+x1v6sMVWAnQWRKRQo+2AlrikEqqxympKyBNNLqSQRD/NOzlqEu7vVUW2GFSjKfnsfWX4oLTTB8BtwRTdQCS3Ttv2S3qXwb3s15HjBABj7EKJyHD4dY6uiCbGgi7NJqDtqQHyJIl4uXlAiFlvWWrLytQS99RKWpSJo0/vo093z98nfE+eFJdZchXAje6IOggo6Mp5ByryDGtWSkbw9ttkXqoW6D137KcXajqf6YHA2+P2mv0NbbpUuDrpeIecwWLC2pbWtywt9Y4Vlt44luV4DuqxoYM5mcxovq2FoQAZsuxsgE4yhi8= X-Microsoft-Antispam-PRVS: <3e8b39dfc1ce496c8df17ab9bd97bea3@BL2NAM02HT011.eop-nam02.prod.protection.outlook.com> X-Exchange-Antispam-Report-Test: UriScan:(28532068793085)(158342451672863)(21748063052155); X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040501)(2401047)(8121501046)(5005006)(93006095)(93004095)(10201501046)(3002001)(3231023)(2400081)(944501161)(6041288)(20161123562045)(201703131423095)(201702281528075)(20161123555045)(201703061421075)(201703061406153)(20161123560045)(20161123558120)(20161123564045)(6072148)(201708071742011);SRVR:BL2NAM02HT011;BCL:0;PCL:0;RULEID:(100000803101)(100110400095);SRVR:BL2NAM02HT011; X-Microsoft-Exchange-Diagnostics: 1;BL2NAM02HT011;4:p3bKFcEkhnqPSvFK5jJKeAJngJl9Rd39FyMJRr1xTZ+TphpSYK9ppl863yBt5ZHIhcQckeOR4p6Pozocws8j76wIkvXWXypS06JuZ6JT33tS5wvtvGei5qSrI+xX08rn+R0egLcsXOrbJNvSWFNvri8kiAVsEaOAWz7INNrKegFDoG7b7vnyGQ5wjJrWIISN7oMxDKE9w5ISHrcBCDSuKEL6Aoad7yh1w9/yvcAwW4CaQN2tjB4aKSxAYI/yce92/JngdRBgxfxjHVWSKcW6qVQcccGGcJsKMIIVPgYPXi0rc8Xepxq2nYThcDAbu2UAfUsKnySzczgWt2bzwJ89HkT+Ox7RauK1O7jQoezlSUnJ3jYse6U81ZkcdjOsVI4A X-Forefront-PRVS: 05610E64EE X-Microsoft-Exchange-Diagnostics: =?us-ascii?Q?1;BL2NAM02HT011;23:4dHxV/MLETKkkWZqQ7D3x5KIWs4oyWOxiTo9KnXSF?= =?us-ascii?Q?fniXFObaLJ2q6hqkkp/CzE/FSNdpcsMSPe99I8OqH8aQMZnBmV8t4OfatNTu?= =?us-ascii?Q?zPKekqI4w/M08jJAdiZO9QuxlDW4/a/SRnEf3BLVKMfaeQS+dbYSeBCztsyv?= =?us-ascii?Q?ziQeiOvE3qxaObtpbOF8ZsWJP8cqEkMXi05NMw/JUqWutBKf/KJh2CJ6Dzku?= =?us-ascii?Q?kRJKNXxcOD4lSQ93ws6P/eTIq5PBslp2jbkpwq0VU6hTrvmrcjRJ/Uh7XlmE?= =?us-ascii?Q?ufLdC3wv1fMMef6MDlwl3LbPv3wVQ4DP/97LK1iw1N7FpFlrLtFiRU1OnQdv?= =?us-ascii?Q?UpPU++PA5SbGqtNMEhi5SKYabHrmoO7iNJ4ibHuA9Y54uHN/FVoPlLeAf2Kh?= =?us-ascii?Q?/v32NuZtKVqcsExMDyfq4konKL1Mpfa5OS6h33SVfkaBInPyo8FvBQZcN+I2?= =?us-ascii?Q?Zh3VBgTZ1rQv5q7dKRrk5fkR5K1wmkk9kklZLd5S1TLAYyJndsiRSWBR+KXm?= =?us-ascii?Q?jxqNDeBviA6MO2L6yiwQeUDkomfNQ4ieY8CDBfcclJjMsaHzcqZA3Tac59u0?= =?us-ascii?Q?MZV8dfCYxwrQwqPlHq4tdGcvP/TQaD+5gl+uhLZ1XGgc+BADYeMAkwoPxwYj?= =?us-ascii?Q?OVaQ8RSDa6oLAhQ1MLGgYxCThtjbeuxUicAqrkWqBr0cVbj7cAkkcOOmrbYM?= =?us-ascii?Q?e+S9cvtHRFSZgEGDIEtFCBsC4Vjh0Aqa+yxrzquLmaaMf+aVRyyxwUEI7slp?= =?us-ascii?Q?ZdFolZinOZE2rUj6TZcuHwEkogDxSpiPtNst5rS5bdJq+3zytrtUAHrxpx6E?= =?us-ascii?Q?UMQQ6+g8oO6de6MXZQgocQScUV/h9rHEse71q/zUYsMnFusKwPM2xuNqEJXH?= =?us-ascii?Q?trSo3Ow8qY0dUG2RVKtGOmw5SYTaFNeBCoReex1Ll/9yLAvykuUrBseAlW7Q?= =?us-ascii?Q?NfcrF1Qmn1115Vr/j7WW8te6qTf2K4MnSxaz+xTRbHuZOmEmVB00IMY0TrF7?= =?us-ascii?Q?BUYgihqaOwb+GRy8u1nawfbBYU9wgzuzPja582RN3uKsEXUGoSGUiSmzXqtQ?= =?us-ascii?Q?E/P1GjCGaWS+M2QM+79N8eRmzrvVKoQ+HsBGMNAfB18nG5fXM8jBOtlV+DB8?= =?us-ascii?Q?RDdHD8riwFJ5XvVQyWT9oqEC10Qtk4M56wo8IV1GC3HWYt9CwkAmir55Z90p?= =?us-ascii?Q?N7sd1jCyUWj8RNi30A62Y7w0C9ktqdhj5lX8eoxnUa50TlkEqVqwLDi8KeFd?= =?us-ascii?Q?3niag6sgZbTAnsv34tefVVn06E+dRoIRXyqf1vis2s5sf/kM4xzE6sHUF4+t?= =?us-ascii?Q?p7y8nJ2oxU0FEVIZBEja0F7w8c0ys57//v8KYZojDVEBNL6J5bAOzCmmQ9DC?= =?us-ascii?Q?tIpBw=3D=3D?= X-Microsoft-Exchange-Diagnostics: 1;BL2NAM02HT011;6:ohXhDdBufld1p9DComzD6bowot2CMNIAwjLSvJYmJuWx88FWlrXqkR89K3zGjx0GSaGk6TjevXk7XMXv9xRs4yxVsGmBYXSPKDawrRtDFBzcjYQ1sivEqgfUtripfp+zWZojZgE5xnSVKLR2FfH+yjq0ZVZFaPhDfyDjuYm44iIJQCijKDpfjMJB3I9mr6vxn7e59caK4Yc3CkulRw5J7ss7arWR7p6FWykaW5xweP54ZLlL4uTFNrHuffNDfQviQq/Z/QgJMmsEPgM+7a2ZNxyRGHRHZxB/LJwNYOjyI3xMIkOsPOBVt0L2us+zs+Xucg3Zme72Wcw1uCKsL0Cs0tWjHIkAsm1vhOCWE8u46hc=;5:vDbhAKNt1XLnBg05LrtKfqALuxjH/vqPlb9DnhCKgdwZ/jzHQ86u1fMPx1Oskjmjyrvkp2RWb+WNh8YKIEcvVXQC5H2TY1NuVc7x1dRdj1J763xjXYaos80tv2pW30VW8SKKrkjf93yFnSXxMb0IL4xu5Rbn/fLdbslnnf0Sla0=;24:1H4BvIspT1ffdoanmVFFsdHBKsd9NwkO0v+e6ONPUQIuIdTr+ZuLpQWM2Q2XGeE/ZfSERJggwXVzyvps6OfV+hwv9xBKQX5yMhVHRMgRiN4=;7:tT+QyAa62bisSt+mkRf6yLcumuvmZGj4K3P+owGBCyviB0Y7XP1/IitxKoGSKrRGw8K2pRNaGX66xHV0zJ3c3tlZpACsuhGnNTUoyL4DiR7I+zUN4AXhSr72SIyKcQOJmfiFhp1htJ5XVXeUEfoI4/oayjk9CIXy3h66ajW9PDEtIB9gyzJjm5Js+4UY8m9mShsuAvYUapeRVTxa27pme7yJoncdjRixGhKfsi2UK0GtvMUy6mEnXGahoiMs1ZsA SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: ultra-ft.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 Jan 2018 14:08:17.6227 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: edbe9883-bb2d-469c-cd68-08d5626ac088 X-MS-Exchange-CrossTenant-Id: 16b31a13-a8fc-4ef5-912d-fdd1665124c4 X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=16b31a13-a8fc-4ef5-912d-fdd1665124c4;Ip=[104.156.72.34];Helo=[SRV-CAMTL-EXCH2.Forensictech.com] X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem X-MS-Exchange-Transport-CrossTenantHeadersStamped: BL2NAM02HT011 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_8FC5F25FF3EC4744ADFCF20CBA3F44BE01246C503ESRVCAMTLEXCH2_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have to provide a summary of how much spaces is used in the large objects= table based on a group by condition. I would expect an index only scan on the large object table, but a full seq= scan that last for hours is performed. BigSql distribution PostgreSQL 9.6.5 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by = MSYS2 project) 4.9.2, 64-bit Win Server 2012 R2, 8GB RAM pg server mem settings: effective_cache_size | 6GB maintenance_work_mem | 819MB random_page_cost | 2 shared_buffers | 2GB work_mem | 32MB Testcase 1: Here is a simplified query, timing and the explain plan: SELECT ima.sit_cod, COUNT(*)*2048*4/3 FROM images ima JOIN pg_largeobject ON (loid=3Dima.val) GROUP BY ima.sit_cod; Time: 343997.661 ms (about 6 min) ran on a small DB, took 4hrs on a ~1TB ta= ble HashAggregate (cost=3D2452378.86..2452379.01 rows=3D15 width=3D14) Group Key: ima.sit_cod -> Hash Join (cost=3D1460.40..2418245.74 rows=3D6826625 width=3D6) Hash Cond: (pg_largeobject.loid =3D ima.val) ---------> Seq Scan on pg_largeobject (cost=3D0.00..2322919.25 rows=3D682= 6625 width=3D4) -> Hash (cost=3D1114.62..1114.62 rows=3D27662 width=3D10) -> Seq Scan on images ima (cost=3D0.00..1114.62 rows=3D2766= 2 width=3D10) Testcase 2: A simple count(*) for a specific group (small group) perform an= Index Only Scan and last few secs. SELECT COUNT(*) FROM images ima JOIN pg_largeobject ON (loid=3Dima.val) WHERE sit_cod=3D'W8213'; count ------- 8599 Time: 12.090 ms Aggregate (cost=3D11930.30..11930.31 rows=3D1 width=3D8) -> Nested Loop (cost=3D2.87..11918.58 rows=3D4689 width=3D0) -> Bitmap Heap Scan on images ima (cost=3D2.43..37.81 rows=3D19 w= idth=3D4) Recheck Cond: ((sit_cod)::text =3D 'W8213'::text) -> Bitmap Index Scan on ima_pk (cost=3D0.00..2.43 rows=3D19= width=3D0) Index Cond: ((sit_cod)::text =3D 'W8213'::text) ---------> Index Only Scan using pg_largeobject_loid_pn_index on pg_largeo= bject (cost=3D0.43..621.22 rows=3D408 width=3D4) Index Cond: (loid =3D ima.val) Testcase 3: However, larger group still perform full seq scan SELECT COUNT(*) FROM images ima JOIN pg_largeobject ON (loid=3Dima.val) WHERE sit_cod=3D'W8317'; count --------- 2209704 Time: 345638.118 ms (about 6 min) Aggregate (cost=3D2369363.01..2369363.02 rows=3D1 width=3D8) -> Hash Join (cost=3D1125.63..2365419.35 rows=3D1577463 width=3D0) Hash Cond: (pg_largeobject.loid =3D ima.val) ---------> Seq Scan on pg_largeobject (cost=3D0.00..2322919.25 rows=3D682= 6625 width=3D4) -> Hash (cost=3D1045.73..1045.73 rows=3D6392 width=3D4) -> Bitmap Heap Scan on images ima (cost=3D127.83..1045.73 r= ows=3D6392 width=3D4) Recheck Cond: ((sit_cod)::text =3D 'W8317'::text) -> Bitmap Index Scan on ima_pk (cost=3D0.00..126.23 r= ows=3D6392 width=3D0) Index Cond: ((sit_cod)::text =3D 'W8317'::text) Pretty sure that using the index would lead to much better perf. Any idea of what can be done? Jean-Marc Lessard Administrateur de base de donn=E9es / Database Administrator Ultra Electronics Forensic Technology Inc. T +1 514 489 4247 x4164 www.ultra-forensictechnology.com Jean-Marc Lessard Administrateur de base de donn=E9es / Database Administrator Ultra Electronics Forensic Technology Inc. T +1 514 489 4247 x4164 www.ultra-forensictechnology.com --_000_8FC5F25FF3EC4744ADFCF20CBA3F44BE01246C503ESRVCAMTLEXCH2_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

 

I have to provide a summary of how much spaces is us= ed in the large objects table based on a group by condition.

I would expect an index only scan on the large objec= t table, but a full seq scan that last for hours is performed.

 

BigSql distribution

PostgreSQL 9.6.5 on x86_64-pc-mingw64, compiled by g= cc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit

Win Server 2012 R2, 8GB RAM

pg server mem settings:

effective_cache_size | 6GB

maintenance_work_mem | 819MB

random_page_cost     | 2

shared_buffers       |= 2GB

work_mem       &n= bsp;     | 32MB

 

Testcase 1: Here is a simplified query, timing and t= he explain plan:

SELECT ima.sit_cod, COUNT(*)*2048*4/3

  FROM images ima JOIN pg_largeobject ON (loid= =3Dima.val)

GROUP BY ima.sit_cod;

Time: 343997.661 ms (about 6 min) ran on a small DB,= took 4hrs on a ~1TB table

 

HashAggregate  (cost=3D2452378.86..2452379.01 r= ows=3D15 width=3D14)

  Group Key: ima.sit_cod

  ->  Hash Join  (cost=3D1460.40..= 2418245.74 rows=3D6826625 width=3D6)

        Hash Cond= : (pg_largeobject.loid =3D ima.val)

--------->  Seq Scan on pg_largeobject = (cost=3D0.00..2322919.25 rows=3D6826625 width=3D4)

        ->&nbs= p; Hash  (cost=3D1114.62..1114.62 rows=3D27662 width=3D10)<= /p>

        &nbs= p;     ->  Seq Scan on images ima  (cost= =3D0.00..1114.62 rows=3D27662 width=3D10)

 

 

Testcase 2: A simple count(*) for a specific group (= small group) perform an Index Only Scan and last few secs.

SELECT COUNT(*)

  FROM images ima JOIN pg_largeobject ON (loid= =3Dima.val)

WHERE sit_cod=3D'W8213';

count

-------

  8599

Time: 12.090 ms

 

Aggregate  (cost=3D11930.30..11930.31 rows=3D1 = width=3D8)

  ->  Nested Loop  (cost=3D2.87..1= 1918.58 rows=3D4689 width=3D0)

        ->&nbs= p; Bitmap Heap Scan on images ima  (cost=3D2.43..37.81 rows=3D19 width= =3D4)

         &nb= sp;    Recheck Cond: ((sit_cod)::text =3D 'W8213'::text= )

        &nbs= p;     ->  Bitmap Index Scan on ima_pk  (c= ost=3D0.00..2.43 rows=3D19 width=3D0)

        &nbs= p;           Index Cond: = ((sit_cod)::text =3D 'W8213'::text)

--------->  Index Only Scan using pg_largeob= ject_loid_pn_index on pg_largeobject  (cost=3D0.43..621.22 rows=3D408 = width=3D4)

        &nbs= p;     Index Cond: (loid =3D ima.val)

 

 

Testcase 3: However, larger group still perform full= seq scan

SELECT COUNT(*)

  FROM images ima JOIN pg_largeobject ON (loid= =3Dima.val)

WHERE sit_cod=3D'W8317';

  count

---------

2209704

Time: 345638.118 ms (about 6 min)

 

Aggregate  (cost=3D2369363.01..2369363.02 rows= =3D1 width=3D8)

  ->  Hash Join  (cost=3D1125.63..= 2365419.35 rows=3D1577463 width=3D0)

        Hash Cond= : (pg_largeobject.loid =3D ima.val)

--------->  Seq Scan on pg_largeobject = (cost=3D0.00..2322919.25 rows=3D6826625 width=3D4)

        ->&nbs= p; Hash  (cost=3D1045.73..1045.73 rows=3D6392 width=3D4)

        &nbs= p;     ->  Bitmap Heap Scan on images ima = (cost=3D127.83..1045.73 rows=3D6392 width=3D4)

        &nbs= p;           Recheck Cond= : ((sit_cod)::text =3D 'W8317'::text)

        &nbs= p;           ->  = Bitmap Index Scan on ima_pk  (cost=3D0.00..126.23 rows=3D6392 width=3D= 0)

        &nbs= p;            &= nbsp;    Index Cond: ((sit_cod)::text =3D 'W8317'::text)

 

Pretty sure that using the index would lead to much = better perf.

Any idea of what can be done?

Jean-Marc Lessard=
Administrateur de base de donn=E9es / Databas= e Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

Jean-Marc Less= ard
Admi= nistrateur de base de donn=E9es / Database Administrator
Ultra Electronics Forensic Technology Inc= .
T +1 514 489 4247 x4164
www.ultra-fo= rensictechnology.com

--_000_8FC5F25FF3EC4744ADFCF20CBA3F44BE01246C503ESRVCAMTLEXCH2_--