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 1s6q4r-0023zs-DZ for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 11:12:15 +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 1s6q4q-00D9nD-V2 for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 11:12:12 +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 1s6q4q-00D9fz-Ct for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 11:12:12 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s6q4n-0009Kn-Du for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 11:12:10 +0000 Received: by mail-ed1-x52c.google.com with SMTP id 4fb4d7f45d1cf-572baf393ddso1048712a12.1 for ; Tue, 14 May 2024 04:12:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gisticinc-com.20230601.gappssmtp.com; s=20230601; t=1715685127; x=1716289927; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=DY2irj8yYzLeGHBTpbrWrHLVXK9SP3C19OT2JeVgHwI=; b=b71n/6w/FY5z07mbU2zm/R3uYGvpHoK6b/iS/4uhbraEV5B72YHR6AVW6YaTHBvr0k hTmMmYlA0Mn0qOZB/L0NrZL8+FKMlxhsPFT0Xoj1ALnmdiP+qju4QIS/yH3yO0bJujx+ hNPj7pthZ+XUTuNJgf1BNjrZizoif6v5NQ9su1m+w0Czu07md54oGWKmAM1bKv/7vjhd cbZWG1bzr71OCDEXbgXyi9mCAOBRFdRUJM/6W+GAAPrB/N4Q3xXyWN4rFErmJqSPwxGN A+Vb7qiyth39sNh/AmpoR35DiNIQR9kGz5CfJ9y093il+zmpD97x+Xm2sY3JtqN2mxPT pQww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715685127; x=1716289927; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=DY2irj8yYzLeGHBTpbrWrHLVXK9SP3C19OT2JeVgHwI=; b=sUk5nMKGfTj9Y+MH+R3tMI8myDKFBVZgcLzjrUAtQGYjhWFE0xeHUsjGAbvxjf5Sco ZL3onHkSL2O5GiJOJWvvLRL0mTDM/bZTdS+wHIEz0fm+/tW5qhPfmogpaaeEnfyhg+7t h78iAkDnTLlxxfKMQi2oU3OTVSZiQmnjy6xXnx2IQ80c5WGdUJAZz+m8zUqYEUWtDe6v /7JFv1gZb4xJBxHb1iQMH3mWnxjjIH1LLSBo+vV+gQuS6TV6uJoVlzLA3fbgpXyiPzy7 Qw3y0dMBDAXG3zihn8VXpjsCQx5sJVc4hZDyIYPlIA5br18904WrLpXvDzuLWXduj+rU de0w== X-Gm-Message-State: AOJu0YyJcemVSTatVPCH4Sn6es7BWt3ZDsrDIcxKUoVQ3FFjs1vHA1Yx EwsNjBY3555GadajxXPJxJgeIek1efgyxIJvsEtRRs7uAVXFZ/mJAZXqE+w20XRRjraIpV2XuRJ DJu+MCaWQW2+wG1t1su7XlqR9MP9A3FVsM5e14q1R6URw9IXSRiLdoQ== X-Google-Smtp-Source: AGHT+IFLdyPlZS7cbVcgpDSdnYnbbGJ9OIHV6vmMTy92tVGS/BirSsdsAVwL0nKB/w9RBhMnJoIICBY8XRyIy7bRBPc= X-Received: by 2002:a17:906:3b56:b0:a55:b67c:bd04 with SMTP id a640c23a62f3a-a5a1155b531mr1299683466b.4.1715685127535; Tue, 14 May 2024 04:12:07 -0700 (PDT) MIME-Version: 1.0 From: Bo Guo Date: Tue, 14 May 2024 04:11:56 -0700 Message-ID: Subject: Small table selection extremely slow! To: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000092d9a6061868126e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000092d9a6061868126e Content-Type: text/plain; charset="UTF-8" Hi, The following query took 20 seconds on a small table of 108 rows with a dozen columns: SELECT * FROM azgiv.layers; Here is the vacuum analyze result: VACUUM (VERBOSE, ANALYZE) azgiv.layers INFO: vacuuming "azgiv.layers" INFO: table "layers": found 0 removable, 200 nonremovable row versions in 12 out of 12 pages INFO: vacuuming "pg_toast.pg_toast_52182" INFO: table "pg_toast_52182": index scan bypassed: 35 pages from table (0.69% of total) have 140 dead item identifiers INFO: table "pg_toast_52182": found 136 removable, 6 nonremovable row versions in 36 out of 5070 pages INFO: analyzing "azgiv.layers" INFO: "layers": scanned 12 of 12 pages, containing 200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total rows VACUUM Here is what the explan shows: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM azgiv.layers; Seq Scan on layers (cost=0.00..14.00 rows=200 width=233) (actual time=0.010..0.087 rows=200 loops=1) Buffers: shared hit=12 Planning: Buffers: shared hit=51 Planning Time: 0.233 ms Execution Time: 0.121 ms I am afraid that I have missed something obvious. Please kindly point it out. Many thanks! Bo --00000000000092d9a6061868126e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,=C2=A0=C2=A0=

The following query took 20 seconds on a small ta= ble of 108 rows with a dozen columns:

SELECT * FROM az= giv.layers;
=

Here is the vacuum analyze result:

VACUUM (VERB= OSE, ANALYZE) azgiv.layers

INFO: =C2=A0vacuuming "azgiv.layers"
<= div>
INFO: =C2=A0table "layers"= : found 0 removable, 200 nonremovable row versions in 12 out of 12 pages
INFO: =C2=A0va= cuuming "pg_toast.pg_toast_52182"
<= div class=3D"gmail_signature" data-smartmail=3D"gmail_signature">
=
INFO: =C2=A0table "pg_toast_52182":= index scan bypassed: 35 pages from table (0.69% of total) have 140 dead it= em identifiers
INFO: =C2=A0table "pg_toast_52182": found 136 removable, 6 nonre= movable row versions in 36 out of 5070 pages
=
INFO: =C2=A0analyzing "azgiv.layers&quo= t;
INFO: =C2= =A0"layers": scanned 12 of 12 pages, containing 200 live rows and= 0 dead rows; 200 rows in sample, 200 estimated total rows
VACUUM

Here is w= hat the explan shows:

EXPLAIN (ANALYZE, BUFFERS)=C2=A0SELECT * FROM azgiv.layers;
=
Seq Scan on layers =C2=A0(cost=3D0.00..14.00 rows=3D200 width=3D233) (= actual time=3D0.010..0.087 rows=3D200 loops=3D1)
=
=C2=A0 Buffers: shared hit=3D12
Planning:
=
=C2=A0 Buffers: shared hit= =3D51
=C2=A0Pl= anning Time: 0.233 ms
=C2=A0Execution Time: 0.121 ms

I am afraid= that I have missed something obvious.=C2=A0 Please kindly point it out.=C2= =A0 Many thanks!

Bo
--00000000000092d9a6061868126e--