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 1rHQnW-002Pak-46 for pgsql-admin@arkaria.postgresql.org; Sun, 24 Dec 2023 15:53:50 +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 1rHQnU-00BuDI-Oj for pgsql-admin@arkaria.postgresql.org; Sun, 24 Dec 2023 15:53:48 +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 1rHQnU-00BuD6-9B for pgsql-admin@lists.postgresql.org; Sun, 24 Dec 2023 15:53:48 +0000 Received: from mail-dm3gcc02on2071e.outbound.protection.outlook.com ([2a01:111:f400:7d04::71e] helo=GCC02-DM3-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rHQnR-00BzdU-7S for pgsql-admin@postgresql.org; Sun, 24 Dec 2023 15:53:46 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=ZKq2F2Wark8MDy7UFVs4hneBjFN47KCl6/dWjaTZ+aP5zymCd3st6+yCzWEm6gpxUhKYOq+ooO9ZoTi47UNjew/Pd9+oePP7FhiXpp8cB7YoER/btpdV0BkS4MYxg990uXphTW3Ly6LhhyLCxQmmZnhggLb4Q389D2T49Yjexni/dXURa2jo5ciTe2jspob7raVGUL4X0AS29wCVrK51BNC6d58n9A3nUbsEesp2ngEiCXk+cYqOLUebKPfYCOIBf8kx6gU+nZdAdH6tXB4KSfDZD2fFDpQY0gyy8rXNvDFw3p9SmXSDMV3/Z+AiKOCBCGLpOXjjzq5eyV1h5PuUEg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; 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=2cwQS1zN5cvtwNJgyFTZtIP2uxs7Sh9LuB0zbIjV4+U=; b=NtQF2mcAqWdpAViDjSdh1hanRZ7bagjrX+rnmjlB2kLy3rxWmhL9RtXNHRR1qMbAIZfIv3JNpURDxS8NqP+FlCnzKtpZQZ7PphP8RtL+fPNQtRe3uaH+VESAw5Uohc/OO+RC1QYYwf7d/Cw7Xwzpfkye6dy7cvgOZ3rKwOweZIs0my9vdg0bWW8wiWhtNDjlEnagZtDKVtiZfLwEK5wnNlys8Sw86Aj2M/FaFhhM4uzlxclPYEv+bj3jp428lVkoj12aj7LukQPKjr1vVnQMVPlyiCLEOZG4GFPs0x/FLZmaldhgN2bijk+b2VhaxKvyAx91rX2SFLRnGnLqiLlSZQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=fnal.gov; dmarc=pass action=none header.from=fnal.gov; dkim=pass header.d=fnal.gov; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=fnal.gov; s=selector2; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=2cwQS1zN5cvtwNJgyFTZtIP2uxs7Sh9LuB0zbIjV4+U=; b=iD1foCUOzzEASy4j3vmHPG3e+7PYrv1tnfaqnVLkDwB5bqsk+3EE9xDCgbsqIVMsMDIDC3kh76Qc38Ukr+RFe/Xl9jYi7S33Y6pITlWJqow0ItURweG25o0sfhEu7UrV/Q7tqYvCtxVageVy7ZTRIys1ejhOa3Prr6JbaT2mI+o+gkucDGlJoqwoz2DYVUhHtBhQm5r0DJbnNNpKtka7QKDwWV1+r9bjsd7HvsZRpL1P9+Dv3BL1xJjQFGY+h9RkvVYSyjL+mF+Madzpx9O+X5QKuKSOtJ4Q7+IHCE8BJ/hG8r0IbiCHWeGCN7z8Zll82ONUQRZdkXjtUQMq42aSbg== Received: from DM8PR09MB6677.namprd09.prod.outlook.com (2603:10b6:5:2ee::12) by SA1PR09MB10651.namprd09.prod.outlook.com (2603:10b6:806:36f::10) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7113.26; Sun, 24 Dec 2023 15:53:39 +0000 Received: from DM8PR09MB6677.namprd09.prod.outlook.com ([fe80::6d1a:36c3:9501:83ce]) by DM8PR09MB6677.namprd09.prod.outlook.com ([fe80::6d1a:36c3:9501:83ce%7]) with mapi id 15.20.7113.026; Sun, 24 Dec 2023 15:53:39 +0000 From: Murthy Nunna To: "pgsql-admin@postgresql.org" Subject: Query Performance after pg_restore Thread-Topic: Query Performance after pg_restore Thread-Index: Ado2fzuBBYj6luGgQmGkzrMcuB87EA== Date: Sun, 24 Dec 2023 15:53:39 +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=fnal.gov; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DM8PR09MB6677:EE_|SA1PR09MB10651:EE_ x-ms-office365-filtering-correlation-id: 4d2d14e1-d57b-4944-f57d-08dc04987f1f x-fermilab-sender-location: inside x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: 5kSQov4Zvg77CTAJScwWZCwkYBw1bVmILwYF0gYtJ6W1HrXnROZw55K4yZNo5T8OHd6PkUBko/bhbH7f/XgJzQAilWdt8PEBxogveb8OsJi+hajwd3UNuKSpY0bI4Sh1+uxv8RenEWHgR1iakWIMNdqOw8PTLcgYPqB195NdanZsKuNCA95qpWM6/uifMxcjTYJ17yaCbOzHpJ6/DWsPQoXbJ1HpbgoB69V4htB5b8PUbP1+VQ2ZBMQ9umGkLKwub6G5Ps4M/98GfICTIfk0LY5jY3xRUB9a76hkHbjsOworpYLd1TOFj0M5U3BTs853WfauP+tKe9sMfzCysRmPLk2/MgNwfBDf55lOrP0nOh64UGmO44sF71i7hHmynnpZXjXvvd4bTeK1SyzHm5KjEr9RjIzPP0+I3ovdWfNF+43oqqCRzGLBmL+tqxsTyb3EGXuqGROjjz+yDmeyqOg7fGYQ5Dmx8Fn8PGEz8rTAvyyCvJNLLpShW1HUnFFGaiU2Gq4D41M8lQ6dpk2pcNg1ezNvGnM+HKWaAdbbVAS/mJKdf/tLrSIzstnbQxjl4B/69ICTEeSNmcFkxm6WO/CafAgsEjlRRVypfdp4A3bCq+4nJXlZM50C2nom6atPvf8m x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DM8PR09MB6677.namprd09.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230031)(366004)(230922051799003)(1800799012)(451199024)(186009)(83380400001)(6506007)(9686003)(7696005)(508600001)(71200400001)(26005)(82960400001)(122000001)(38100700002)(8676002)(9326002)(8936002)(6916009)(52536014)(64756008)(66446008)(66476007)(66556008)(66946007)(76116006)(5660300002)(4744005)(2906002)(33656002)(55016003)(38070700009)(86362001);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?PHfuM/J0mPIu2dEz0WRx8sZey7OVfu55SZC66TlQgGjidsQmZGz/6JWAMB07?= =?us-ascii?Q?od0OdlEqgKGSvTD9qI0CpsjWR6wITNAPcrqPv3RBc6mSz5rfQLISUqrJ10zd?= =?us-ascii?Q?Ljql1M5pXl0btNfJNAL3oD53n1hzUmxdw1xid7tksf/jgaPGOY4fdxWwV9dx?= =?us-ascii?Q?GfvR27y8cTUFsjYecfyamuib82kjucbX/yhIcN+z5S24P+hmJxa6ntWq/T3x?= =?us-ascii?Q?j4wRNBhDb+40i0OKw1XKy/Jh0vyslnl2y9JnepJnn7f2AnUPDacnnPDEpQuj?= =?us-ascii?Q?ZEQzP0RaOhkkyR6YNjk1wFl6Bt/k8OqxHRdV7/Os/5LvajsRoVhMa2cQjwKZ?= =?us-ascii?Q?tOa+oak6eGPFzmSwLNAPM0Au97oF9f68hLiUS+dMdXaqRkUXDtC6tTD0jxLd?= =?us-ascii?Q?XtLrpbBBUUO3q6tjG8BO/u0JWJITTHvPWOz2YyDAVSmvOJrMUbW5LWaHS7Ji?= =?us-ascii?Q?YFzhCWKCpNFSTcj0EBuShbSvo1OFl7R9Ke5cBhCxnjr4+ynxdxPo8duCtk6w?= =?us-ascii?Q?PR8KMBpshATg6PtqOwyD/gjRZambCEpuZ2lkjKlmvEnZ8XW19hpLzvxCX1cc?= =?us-ascii?Q?PLGo9/9uPm99htTMtPqfAMVCUMgHXHtsPgUe+L6I+oKIslKxGqbSC45WhHWv?= =?us-ascii?Q?vQQoKY4bJzghKuqCdsP8aW6y7uDxEd5JBYijd8R5R+BwmxVLJw0lYavVhIXl?= =?us-ascii?Q?H7LliMR02QWO/0ibz68pqib+eW/KprM+FRP8mTP9e1BekhVHjDlVlum5AuYj?= =?us-ascii?Q?KzOj2vaHca8/k6egriy8v9/JNg3P2X8ZYKjupmXYRf6rOCbgRxKQ2L2J88VB?= =?us-ascii?Q?Cmhe2Riezaj2ArAdd7JTXhTz+ZOJJ9aaexqImyORpNApgsrOujdoasyMvIw2?= =?us-ascii?Q?/14PoVZ1OoIvBWkqSrRrDwjpJwsC1BCzfa8eZlvAG9jllJFpn7SdGZnjVPfk?= =?us-ascii?Q?zv38QZw4s57y++OuIckG0MepmmltDo3OZgvemrT0ghgIgXJQ9TZO5ZXUwJ6f?= =?us-ascii?Q?Bl5TPMMSE29YozMnOniq3NHih7yH0GFHFiU3jZP3uKsd78LV3gUkIpJEPUpR?= =?us-ascii?Q?OX1s09tWF2swYuVXT3Btmh5DsiE+CbFRVAA4RaaSnt+O+tmH3kPXDI0Uqgpm?= =?us-ascii?Q?V0g9u0uz20mO56tifcDNPIwFxridy26UhS+/JZO5aS90EqR924yuGpOhNViy?= =?us-ascii?Q?1Rl6GbjYMh7CrfEU1XKIN71A+2VnXD95twwjkbmy1gBcHqGMBRZTsWC01VQo?= =?us-ascii?Q?OsvwWEdxvCgW4uvenxGaiXan0csBUqBDA2yzmk/0C38BgzFW7xzMbhNyIuzx?= =?us-ascii?Q?xBTki4mpfndtZEi3wRgNXi9gemnbqF3dC7c0cFdShUJ/sUwI8OWlfyvudjUU?= =?us-ascii?Q?VNHp2UfcVs20Zsaq9XdtjNXLtnytyO2os4vrEgeXSXaMgghHUz8u/FNg4fy5?= =?us-ascii?Q?8O1qChnAnfh+wT7NTCLO3IyuQEkHO3878UXyFlfaATmHeRP2a4pzg+0V4V6D?= =?us-ascii?Q?HqjHlYP7DvdtupbmH/5bTG1S9GmN5bXNOF0n2BDsCNq1B9W3ydD52rSnV/LV?= =?us-ascii?Q?hkEfS8Rl7dgkElKVQg5Eiw5G+Nu8+OMpVSPjrFlm?= Content-Type: multipart/alternative; boundary="_000_DM8PR09MB66777254DC04045815B09196B89AADM8PR09MB6677namp_" MIME-Version: 1.0 X-OriginatorOrg: fnal.gov X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DM8PR09MB6677.namprd09.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 4d2d14e1-d57b-4944-f57d-08dc04987f1f X-MS-Exchange-CrossTenant-originalarrivaltime: 24 Dec 2023 15:53:39.3474 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 9d5f83d3-d338-4fd3-b1c9-b7d94d70255a X-MS-Exchange-Transport-CrossTenantHeadersStamped: SA1PR09MB10651 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DM8PR09MB66777254DC04045815B09196B89AADM8PR09MB6677namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I did pg_dump of a ~20TB database followed by pg_restore. I find simple que= ries like select count(*) running slow. I did a select count(*) on all tabl= es before pg_dump which took ~4 hours. After pg_restore, same thing took 32= hours. I ran "analyze verbose ;" on the database, but it did not help. It ran quic= k in 1.5 hours, but as you are aware it does sampling. I am now running "vacuumdb -p -a -z -j 10 -v", but I do not k= now if it is going to help. I am not sure if above vacuumdb helps, but if it helps what is it vacuuming= in a pristine database? If it is doing some special "analyze", what is it?= Is there a way to run "full" analyze without sampling and without vacuumin= g? By the way, there is no change in postgres versions. It is 14.4 before and = after pg_restore. Thanks! --_000_DM8PR09MB66777254DC04045815B09196B89AADM8PR09MB6677namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

I did pg_dump of a = ~20TB database followed by pg_restore. I find simple queries like select co= unt(*) running slow. I did a select count(*) on all tables before pg_dump w= hich took ~4 hours. After pg_restore, same thing took 32 hours.

 

I ran “analyz= e verbose ;” on the database, but it did not help. It ran quick in 1.= 5 hours, but as you are aware it does sampling.

 

I am now running &#= 8220;vacuumdb -p <port-number> -a -z -j 10 -v”, but I do not kn= ow if it is going to help.

 

I am not sure if ab= ove vacuumdb helps, but if it helps what is it vacuuming in a pristine data= base? If it is doing some special “analyze”, what is it? Is the= re a way to run “full” analyze without sampling and without vacuuming?

 

By the way, there i= s no change in postgres versions. It is 14.4 before and after pg_restore.

 

Thanks!<= /span>

 

 

 

--_000_DM8PR09MB66777254DC04045815B09196B89AADM8PR09MB6677namp_--