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 1s6qzB-0028EX-EB for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 12:10:26 +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 1s6qzB-00Dtx4-FH for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 12:10:25 +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 1s6qzB-00Dtww-0W for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 12:10:25 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s6qz8-0009iP-Bc for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 12:10:23 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-6209e8a0386so45317717b3.0 for ; Tue, 14 May 2024 05:10:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1715688621; x=1716293421; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=VaCMUJXfU1p+nftb+UE4gU+lMnILdjnhAENiIHaaPRo=; b=L5KvxmdCFBurE9oSH58dAAkdFBol2WITA8EyWIKAUOqCAcuO+4u/eul9d1wevQ4+F0 AfOmkol4G+GxReDKMtQgaINjBWjVHzk06Y8jUxzuyYYZ8IFZIWe6e1pswpolIXUBI8Q5 CEAEivL6w4HjqbStfmbUS+SgfGJdPsURb+2veMUDLVNRhpzZNCnV8Knl55VUHjgArXRq Ltv5doY5gMlNVPOJKSw0ig80KY41YQOAbdbxERBGSTsj8MY3h9EBXon/Qui/kdZKanhF EX17AUjxODVpIdMoKZUaeYfLAhiBVbn996O0XqQSTNhRdAnkyQuj1CrLCRIgVnDEiyfl e/pQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715688621; x=1716293421; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=VaCMUJXfU1p+nftb+UE4gU+lMnILdjnhAENiIHaaPRo=; b=JCQBRNppZKB7Gy5d/2QM9CSoXMNtxSk/Gc+eoZPph87WBBbZB57e0clNhoEGEgsm7o PIXHvMR8NXAE9aGlSqiyeeQzPOu0nFBBhF4PEtgzP8kkDymNi70T74WvkerLs44pk4z6 sxA3e4OtYnP901v+bM9R32Vok5s3ieLsw/4Dkvid/hVIZ4pc8NxEOEYJEmNX81kD7bSf 15yFhyE9EtS3Ulv672xUUgzWbmzpK7Xqd6HcfVGmTilsxTwMZAh9DUNMtvJWJeXj7eGB tGAQ2YTawB5Oh0dOtrVTsgiyyWAAEM6+fsAW92cLOblE6Rg4lYIyDpW9cK4fEmi6Bo0t h5Bg== X-Forwarded-Encrypted: i=1; AJvYcCXQzvUbht1xQ2r8lTjQzjBtifnrJ2ZhFfb7GkaU3EgBkRfeWUkC5HF+1NwzXFRLYDbC2ICG9/IEWwFGPg/DDMQE93yE/eTb2OGsjfdsgCw= X-Gm-Message-State: AOJu0Yx0ZCrujLPW5mHpEMLKSZxN18JPtVCmsxy8i4j5xtEOqaujnhXX 5kq16RK49CMrEnCO7R2C1m2+BXB6IGmaFGadJo3E6a1uHZGQi7jjBg9BFQ== X-Google-Smtp-Source: AGHT+IGcQjCHZiyOF2yBHQu3AHFvPaxcdUD0z+OhbRCxCMqWml/xlomjJu6d50hfzYgLUAJwE9a3kw== X-Received: by 2002:a81:4c0d:0:b0:620:2753:96b8 with SMTP id 00721157ae682-622aff922b6mr164465967b3.12.1715688619785; Tue, 14 May 2024 05:10:19 -0700 (PDT) Received: from smtpclient.apple (162-231-200-43.lightspeed.dybhfl.sbcglobal.net. [162.231.200.43]) by smtp.gmail.com with ESMTPSA id 00721157ae682-6209e3790dasm25475477b3.110.2024.05.14.05.10.19 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 14 May 2024 05:10:19 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-589B2FBB-6CCE-43E9-82F6-951CF1EBA7C4 Content-Transfer-Encoding: 7bit From: Shane Borden Mime-Version: 1.0 (1.0) Subject: Re: Small table selection extremely slow! Date: Tue, 14 May 2024 08:10:08 -0400 Message-Id: <19A7E904-19DC-4692-9319-929480DA117A@gmail.com> References: Cc: MichaelDBA , pgsql-sql@lists.postgresql.org In-Reply-To: To: Bo Guo X-Mailer: iPhone Mail (21E236) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-589B2FBB-6CCE-43E9-82F6-951CF1EBA7C4 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable What is the table definition?

/d+



Shane Borden
sborden76@gmail.com
Sent from my iPhone

On May 14, 2024, at 7:50=E2=80=AFAM, Bo Guo <bo.guo@gisticinc.= com> wrote:

=EF=BB=BF
I am using pgAdmin 4
<= /div>
Operating System
The performance is 0.16 ms when 
<= br>
SELECT gly_id, gly_name FROM azgiv.layers;

We do not experience a= ny slowness on other much larger tables with SELECT= * FROM OtherTable;

Bo
=


On Tue, May 14, 2024 at 4:26=E2=80=AF= AM MichaelDBA <MichaelDBA@sqlex= ec.com> wrote:
You don't elaborate on where you are seeing this "20 seconds".  Than means network, client application stuff, locking/waiting, or other things may come into play here... Please provide more info.


Bo Guo wrote on 5/14/2024 7:11 AM:
=20
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"
<= div class=3D"gmail_signature">
INFO: &= nbsp;table "layers": found 0 removable, 200 nonremovable row versions in 12 out of 12 pages
INFO= :  vacuuming "pg_toast.pg_toast_52182"
INFO: &= nbsp;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
I= NFO:  analyzing "azgiv.layers"
INFO:  "la= yers": 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:

EXPL= AIN (ANALYZE, BUFFERS) SELECT * FROM azgiv.layers;

=
Seq Scan on layers  (cost=3D0.00..14.00 rows=3D200 width=3D233) (actual time=3D0.010..0.087 rows=3D200 loops=3D1)
<= /div>
  Buffers: shared hit=3D12
Plannin= g:
  Buffers: shared hit=3D51
 Planning Time: 0.233 ms
 Execut= ion Time: 0.121 ms

I am afraid that I have missed something obvious.  Please kindly point it out.  Many thank= s!

Bo


Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343

 
<pgadvanced3.jpg>


= --Apple-Mail-589B2FBB-6CCE-43E9-82F6-951CF1EBA7C4--