From: | Shane Borden <sborden76(at)gmail(dot)com> |
---|---|
To: | Bo Guo <bo(dot)guo(at)gisticinc(dot)com> |
Cc: | MichaelDBA <MichaelDBA(at)sqlexec(dot)com>, pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: Small table selection extremely slow! |
Date: | 2024-05-14 12:10:08 |
Message-ID: | 19A7E904-19DC-4692-9319-929480DA117A@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
What is the table definition?
/d+
Shane Borden
sborden76(at)gmail(dot)com
Sent from my iPhone
On May 14, 2024, at 7:50 AM, Bo Guo <bo(dot)guo(at)gisticinc(dot)com> wrote:
I am using pgAdmin 4
Version
8.5
Application Mode
Server
Current User
pgadmin(at)linearbench(dot)com
Browser
Firefox 125.0
Operating System
Linux-5.15.143-1-pve-x86_64-with-glibc2.35
The performance is 0.16 ms when
SELECT gly_id, gly_name FROM azgiv.layers;
We do not experience any slowness on other much larger tables with SELECT * FROM OtherTable;
Bo
On Tue, May 14, 2024 at 4:26 AM MichaelDBA <MichaelDBA(at)sqlexec(dot)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:
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
Regards,
Michael Vitale
Michaeldba(at)sqlexec(dot)com
703-600-9343
<pgadvanced3.jpg>
From | Date | Subject | |
---|---|---|---|
Next Message | Bo Guo | 2024-05-14 12:15:24 | Re: Small table selection extremely slow! |
Previous Message | Bo Guo | 2024-05-14 11:50:25 | Re: Small table selection extremely slow! |