public inbox for [email protected]
help / color / mirror / Atom feedSmall table selection extremely slow!
7+ messages / 4 participants
[nested] [flat]
* Small table selection extremely slow!
@ 2024-05-14 11:11 Bo Guo <[email protected]>
2024-05-14 11:16 ` Re: Small table selection extremely slow! David Rowley <[email protected]>
2024-05-14 11:26 ` Re: Small table selection extremely slow! MichaelDBA <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Bo Guo @ 2024-05-14 11:11 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Small table selection extremely slow!
2024-05-14 11:11 Small table selection extremely slow! Bo Guo <[email protected]>
@ 2024-05-14 11:16 ` David Rowley <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David Rowley @ 2024-05-14 11:16 UTC (permalink / raw)
To: Bo Guo <[email protected]>; +Cc: [email protected]
On Tue, 14 May 2024 at 23:12, Bo Guo <[email protected]> wrote:
> The following query took 20 seconds on a small table of 108 rows with a dozen columns:
>
> SELECT * FROM azgiv.layers;
>
> 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
One difference between the SELECT and EXPLAIN ANALYZE is that SELECT
will detoast your toasted datums whereas EXPLAIN ANALYZE will not.
It's likely the slowness comes from the detoasting.
David
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Small table selection extremely slow!
2024-05-14 11:11 Small table selection extremely slow! Bo Guo <[email protected]>
@ 2024-05-14 11:26 ` MichaelDBA <[email protected]>
2024-05-14 11:50 ` Re: Small table selection extremely slow! Bo Guo <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: MichaelDBA @ 2024-05-14 11:26 UTC (permalink / raw)
To: Bo Guo <[email protected]>; +Cc: [email protected]
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
[email protected] <mailto:[email protected]>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, 3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Small table selection extremely slow!
2024-05-14 11:11 Small table selection extremely slow! Bo Guo <[email protected]>
2024-05-14 11:26 ` Re: Small table selection extremely slow! MichaelDBA <[email protected]>
@ 2024-05-14 11:50 ` Bo Guo <[email protected]>
2024-05-14 12:10 ` Re: Small table selection extremely slow! Shane Borden <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bo Guo @ 2024-05-14 11:50 UTC (permalink / raw)
To: MichaelDBA <[email protected]>; +Cc: [email protected]
I am using pgAdmin 4
Version
8.5
Application Mode
Server
Current User
[email protected]
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 <[email protected]> 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
>
> [email protected] <[email protected]>
>
> 703-600-9343
>
>
>
>
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, 3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Small table selection extremely slow!
2024-05-14 11:11 Small table selection extremely slow! Bo Guo <[email protected]>
2024-05-14 11:26 ` Re: Small table selection extremely slow! MichaelDBA <[email protected]>
2024-05-14 11:50 ` Re: Small table selection extremely slow! Bo Guo <[email protected]>
@ 2024-05-14 12:10 ` Shane Borden <[email protected]>
2024-05-14 12:15 ` Re: Small table selection extremely slow! Bo Guo <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Shane Borden @ 2024-05-14 12:10 UTC (permalink / raw)
To: Bo Guo <[email protected]>; +Cc: MichaelDBA <[email protected]>; [email protected]
--Apple-Mail-589B2FBB-6CCE-43E9-82F6-951CF1EBA7C4
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">What is the table definition?<div><br></div=
><div>/d+</div><div><br></div><div><br></div><div><br id=3D"lineBreakAtBegin=
ningOfSignature"><div dir=3D"ltr">Shane Borden<div>[email protected]<br><d=
iv>Sent from my iPhone</div></div></div><div dir=3D"ltr"><br><blockquote typ=
e=3D"cite">On May 14, 2024, at 7:50=E2=80=AFAM, Bo Guo <bo.guo@gisticinc.=
com> wrote:<br><br></blockquote></div><blockquote type=3D"cite"><div dir=3D=
"ltr">=EF=BB=BF<div dir=3D"ltr">I am using pgAdmin 4<div><blockquote style=3D=
"margin:0 0 0 40px;border:none;padding:0px"><div><div class=3D"gmail-MuiGrid=
-root gmail-MuiGrid-container" style=3D"margin-bottom:8px"><div class=3D"gma=
il-MuiGrid-root gmail-MuiGrid-item gmail-MuiGrid-grid-xs-12 gmail-MuiGrid-gr=
id-sm-3 gmail-MuiGrid-grid-md-3 gmail-MuiGrid-grid-lg-3"><label class=3D"gma=
il-MuiFormLabel-root gmail-MuiInputLabel-root gmail-MuiInputLabel-animated" s=
tyle=3D"font-weight:bold">Version</label></div><div class=3D"gmail-MuiGrid-r=
oot gmail-MuiGrid-item gmail-MuiGrid-grid-xs-12 gmail-MuiGrid-grid-sm-9 gmai=
l-MuiGrid-grid-md-9 gmail-MuiGrid-grid-lg-9"><label class=3D"gmail-MuiFormLa=
bel-root gmail-MuiInputLabel-root gmail-MuiInputLabel-animated">8.5</label><=
/div></div><div class=3D"gmail-MuiGrid-root gmail-MuiGrid-container" style=3D=
"margin-bottom:8px"><div class=3D"gmail-MuiGrid-root gmail-MuiGrid-item gmai=
l-MuiGrid-grid-xs-12 gmail-MuiGrid-grid-sm-3 gmail-MuiGrid-grid-md-3 gmail-M=
uiGrid-grid-lg-3"><label class=3D"gmail-MuiFormLabel-root gmail-MuiInputLabe=
l-root gmail-MuiInputLabel-animated" style=3D"font-weight:bold">Application M=
ode</label></div><div class=3D"gmail-MuiGrid-root gmail-MuiGrid-item gmail-M=
uiGrid-grid-xs-12 gmail-MuiGrid-grid-sm-9 gmail-MuiGrid-grid-md-9 gmail-MuiG=
rid-grid-lg-9"><label class=3D"gmail-MuiFormLabel-root gmail-MuiInputLabel-r=
oot gmail-MuiInputLabel-animated">Server</label></div></div><div class=3D"gm=
ail-MuiGrid-root gmail-MuiGrid-container" style=3D"margin-bottom:8px"><div c=
lass=3D"gmail-MuiGrid-root gmail-MuiGrid-item gmail-MuiGrid-grid-xs-12 gmail=
-MuiGrid-grid-sm-3 gmail-MuiGrid-grid-md-3 gmail-MuiGrid-grid-lg-3"><label c=
lass=3D"gmail-MuiFormLabel-root gmail-MuiInputLabel-root gmail-MuiInputLabel=
-animated" style=3D"font-weight:bold">Current User</label></div><div class=3D=
"gmail-MuiGrid-root gmail-MuiGrid-item gmail-MuiGrid-grid-xs-12 gmail-MuiGri=
d-grid-sm-9 gmail-MuiGrid-grid-md-9 gmail-MuiGrid-grid-lg-9"><label class=3D=
"gmail-MuiFormLabel-root gmail-MuiInputLabel-root gmail-MuiInputLabel-animat=
ed"><a href=3D"mailto:[email protected]">[email protected]</a></=
label></div></div><div class=3D"gmail-MuiGrid-root gmail-MuiGrid-container" s=
tyle=3D"margin-bottom:8px"><div class=3D"gmail-MuiGrid-root gmail-MuiGrid-it=
em gmail-MuiGrid-grid-xs-12 gmail-MuiGrid-grid-sm-3 gmail-MuiGrid-grid-md-3 g=
mail-MuiGrid-grid-lg-3"><label class=3D"gmail-MuiFormLabel-root gmail-MuiInp=
utLabel-root gmail-MuiInputLabel-animated" style=3D"font-weight:bold">Browse=
r</label></div><div class=3D"gmail-MuiGrid-root gmail-MuiGrid-item gmail-Mui=
Grid-grid-xs-12 gmail-MuiGrid-grid-sm-9 gmail-MuiGrid-grid-md-9 gmail-MuiGri=
d-grid-lg-9"><label class=3D"gmail-MuiFormLabel-root gmail-MuiInputLabel-roo=
t gmail-MuiInputLabel-animated">Firefox 125.0</label></div></div><div class=3D=
"gmail-MuiGrid-root gmail-MuiGrid-container" style=3D"margin-bottom:8px"><di=
v class=3D"gmail-MuiGrid-root gmail-MuiGrid-item gmail-MuiGrid-grid-xs-12 gm=
ail-MuiGrid-grid-sm-3 gmail-MuiGrid-grid-md-3 gmail-MuiGrid-grid-lg-3"><labe=
l class=3D"gmail-MuiFormLabel-root gmail-MuiInputLabel-root gmail-MuiInputLa=
bel-animated" style=3D"font-weight:bold">Operating System</label></div><div c=
lass=3D"gmail-MuiGrid-root gmail-MuiGrid-item gmail-MuiGrid-grid-xs-12 gmail=
-MuiGrid-grid-sm-9 gmail-MuiGrid-grid-md-9 gmail-MuiGrid-grid-lg-9"><label c=
lass=3D"gmail-MuiFormLabel-root gmail-MuiInputLabel-root gmail-MuiInputLabel=
-animated">Linux-5.15.143-1-pve-x86_64-with-glibc2.35</label></div></div></d=
iv></blockquote><div>The performance is 0.16 ms when </div></div><div><=
br></div><blockquote style=3D"margin:0 0 0 40px;border:none;padding:0px"><di=
v><div><font face=3D"monospace">SELECT gly_id, gly_name FROM azgiv.layers;</=
font></div></div></blockquote><div><div><br></div><div>We do not experience a=
ny slowness on other much larger tables with <font face=3D"monospace">SELECT=
* FROM OtherTable;</font><br clear=3D"all"><div><div dir=3D"ltr" class=3D"g=
mail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div dir=
=3D"ltr"><div><span style=3D"color:rgb(105,105,105)"><span style=3D"font-siz=
e:12px"><span style=3D"font-family:arial,helvetica,sans-serif"><b><br></b></=
span></span></span></div><div><font color=3D"#696969" face=3D"arial, helveti=
ca, sans-serif"><span style=3D"font-size:12px"><b>Bo</b></span></font></div>=
</div></div></div></div><br></div></div></div><br><div class=3D"gmail_quote"=
><div dir=3D"ltr" class=3D"gmail_attr">On Tue, May 14, 2024 at 4:26=E2=80=AF=
AM MichaelDBA <<a href=3D"mailto:[email protected]">MichaelDBA@sqlex=
ec.com</a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">
<div>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.<br>
<br>
<br>
<span>Bo Guo wrote on 5/14/2024 7:11 AM:</span><br>
<blockquote type=3D"cite">
=20
<div dir=3D"ltr"><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"lt=
r"><div dir=3D"ltr"><div>Hi, </div><div><br></div><div>The
following query took 20 seconds on a small table of 108 rows with a
dozen columns:</div><div><br></div></div></div></div><blockquote style=3D"ma=
rgin:0px 0px 0px 40px;border:none;padding:0px"><div dir=3D"ltr" class=3D"gma=
il_signature"><div dir=3D"ltr"><div dir=3D"ltr"><div><font face=3D"monospace=
">SELECT * FROM azgiv.layers;</font></div></div></div></div></blockquote><di=
v dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div dir=3D"ltr"><d=
iv><br>Here is the vacuum analyze result:</div><div><span style=3D"font-fami=
ly:monospace"><br></span></div></div></div></div><blockquote style=3D"margin=
:0px 0px 0px 40px;border:none;padding:0px"><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr"><div dir=3D"ltr"><div><span style=3D"font-family:=
monospace">VACUUM (VERBOSE,
ANALYZE) azgiv.layers</span></div></div></div></div></blockquote><blockquote=
style=3D"margin:0px 0px 0px 40px;border:none;padding:0px"><div class=3D"gma=
il_signature"><div><div><div><font face=3D"monospace"><br></font></div></div=
></div></div><div class=3D"gmail_signature"><div><div><div><font face=3D"mon=
ospace">INFO: vacuuming "azgiv.layers"</font></div></div></div></div><=
div class=3D"gmail_signature"><div><div><div><font face=3D"monospace">INFO: &=
nbsp;table "layers": found 0 removable, 200
nonremovable row versions in 12 out of 12 pages</font></div></div></div></di=
v><div class=3D"gmail_signature"><div><div><div><font face=3D"monospace">INFO=
: vacuuming "pg_toast.pg_toast_52182"</font></div></div></div></div><d=
iv class=3D"gmail_signature"><div><div><div><font face=3D"monospace">INFO: &=
nbsp;table "pg_toast_52182": index scan bypassed: 35
pages from table (0.69% of total) have 140 dead item identifiers</font></di=
v></div></div></div><div class=3D"gmail_signature"><div><div><div><font face=
=3D"monospace">INFO: table "pg_toast_52182": found 136 removable, 6
nonremovable row versions in 36 out of 5070 pages</font></div></div></div></=
div><div class=3D"gmail_signature"><div><div><div><font face=3D"monospace">I=
NFO: analyzing "azgiv.layers"</font></div></div></div></div><div class=
=3D"gmail_signature"><div><div><div><font face=3D"monospace">INFO: "la=
yers": scanned 12 of 12 pages, containing
200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total
rows</font></div></div></div></div><div class=3D"gmail_signature"><div><div>=
<div><font face=3D"monospace">VACUUM</font></div></div></div></div></blockqu=
ote><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div dir=3D"=
ltr"><div><br>Here is what the explan shows:</div><div><br></div></div></div=
></div><blockquote style=3D"margin:0px 0px 0px 40px;border:none;padding:0px"=
><div class=3D"gmail_signature"><div><div><div><font face=3D"monospace">EXPL=
AIN (ANALYZE, BUFFERS) SELECT * FROM azgiv.layers;</font></div></div></=
div></div></blockquote><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D=
"ltr"><div dir=3D"ltr"><div><font face=3D"monospace"><br></font></div></div>=
</div></div><blockquote style=3D"margin:0px 0px 0px 40px;border:none;padding=
:0px"><div class=3D"gmail_signature"><div><div><div><font face=3D"monospace"=
>Seq Scan on layers (cost=3D0.00..14.00 rows=3D200
width=3D233) (actual time=3D0.010..0.087 rows=3D200 loops=3D1)</font></div><=
/div></div></div><div class=3D"gmail_signature"><div><div><div><font face=3D=
"monospace"> Buffers: shared hit=3D12</font></div></div></div></div><d=
iv class=3D"gmail_signature"><div><div><div><font face=3D"monospace">Plannin=
g:</font></div></div></div></div><div class=3D"gmail_signature"><div><div><d=
iv><font face=3D"monospace"> Buffers: shared hit=3D51</font></div></di=
v></div></div><div class=3D"gmail_signature"><div><div><div><font face=3D"mo=
nospace"> Planning Time: 0.233 ms</font></div></div></div></div><div cl=
ass=3D"gmail_signature"><div><div><div><font face=3D"monospace"> Execut=
ion Time: 0.121 ms</font></div></div></div></div></blockquote><div dir=3D"lt=
r" class=3D"gmail_signature"><div dir=3D"ltr"><div dir=3D"ltr"><div><br></di=
v><div>I am afraid that I have
missed something obvious. Please kindly point it out. Many thank=
s!</div><div><br></div><div>Bo</div></div></div></div></div>
</blockquote>
<br>
<div><br>
<span>
<p class=3D"MsoNormal">Regards,<u></u><u></u></p>
<p class=3D"MsoNormal">Michael Vitale<u></u><u></u></p>
<p class=3D"MsoNormal"><a href=3D"mailto:[email protected]" target=3D=
"_blank">[email protected]</a><u></u><u></u></p>
<p class=3D"MsoNormal">703-600-9343<u></u><u></u></p>
</span><br>
<div><pgadvanced3.jpg></div><br>
<br>
</div>
</div>
</blockquote></div>
</div></blockquote></div></body></html>=
--Apple-Mail-589B2FBB-6CCE-43E9-82F6-951CF1EBA7C4--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Small table selection extremely slow!
2024-05-14 11:11 Small table selection extremely slow! Bo Guo <[email protected]>
2024-05-14 11:26 ` Re: Small table selection extremely slow! MichaelDBA <[email protected]>
2024-05-14 11:50 ` Re: Small table selection extremely slow! Bo Guo <[email protected]>
2024-05-14 12:10 ` Re: Small table selection extremely slow! Shane Borden <[email protected]>
@ 2024-05-14 12:15 ` Bo Guo <[email protected]>
2024-05-14 13:36 ` Re: Small table selection extremely slow! Bo Guo <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bo Guo @ 2024-05-14 12:15 UTC (permalink / raw)
To: Shane Borden <[email protected]>; +Cc: MichaelDBA <[email protected]>; [email protected]
Here is the table definition:
CREATE TABLE IF NOT EXISTS azgiv.layers
(
gly_id integer NOT NULL DEFAULT
nextval('azgiv.layers_gly_id_seq'::regclass),
gly_name text COLLATE pg_catalog."default" NOT NULL,
gly_cus_id integer NOT NULL,
gly_desc text COLLATE pg_catalog."default",
gly_glt_id integer,
gly_tranx_create uuid NOT NULL,
gly_tranx_delete uuid,
gly_filename text COLLATE pg_catalog."default",
gly_rowcount integer,
gly_cgs_id_maint_type integer,
gly_db_instance_rep text COLLATE pg_catalog."default",
gly_topo json,
gly_cgs_id_state integer,
gly_last_sync_rep timestamp with time zone,
gly_esri_fs_url text COLLATE pg_catalog."default",
CONSTRAINT pk_layers PRIMARY KEY (gly_id),
CONSTRAINT layers_gly_cus_id_fkey FOREIGN KEY (gly_cus_id)
REFERENCES lba.lb_customers (cus_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT layers_gly_glt_id_fkey FOREIGN KEY (gly_glt_id)
REFERENCES azgiv.layer_types (glt_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS azgiv.layers
OWNER to lb;
-- Index: layers_idx_uk
-- DROP INDEX IF EXISTS azgiv.layers_idx_uk;
CREATE UNIQUE INDEX IF NOT EXISTS layers_idx_uk
ON azgiv.layers USING btree
(gly_cus_id ASC NULLS LAST, gly_glt_id ASC NULLS LAST,
gly_cgs_id_maint_type ASC NULLS LAST, COALESCE(gly_tranx_delete::text,
''::text) COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;
On Tue, May 14, 2024 at 5:10 AM Shane Borden <[email protected]> wrote:
> What is the table definition?
>
> /d+
>
>
>
> Shane Borden
> [email protected]
> Sent from my iPhone
>
> On May 14, 2024, at 7:50 AM, Bo Guo <[email protected]> wrote:
>
>
> I am using pgAdmin 4
>
> Version
> 8.5
> Application Mode
> Server
> Current User
> [email protected]
> 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 <[email protected]> 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
>>
>> [email protected] <[email protected]>
>>
>> 703-600-9343
>>
>> <pgadvanced3.jpg>
>>
>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Small table selection extremely slow!
2024-05-14 11:11 Small table selection extremely slow! Bo Guo <[email protected]>
2024-05-14 11:26 ` Re: Small table selection extremely slow! MichaelDBA <[email protected]>
2024-05-14 11:50 ` Re: Small table selection extremely slow! Bo Guo <[email protected]>
2024-05-14 12:10 ` Re: Small table selection extremely slow! Shane Borden <[email protected]>
2024-05-14 12:15 ` Re: Small table selection extremely slow! Bo Guo <[email protected]>
@ 2024-05-14 13:36 ` Bo Guo <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Bo Guo @ 2024-05-14 13:36 UTC (permalink / raw)
To: Shane Borden <[email protected]>; +Cc: MichaelDBA <[email protected]>; [email protected]
Thanks for your responses! The mystery is solved - It turned out that
the JSON column in some rows contained rather large data.
Bo
On Tue, May 14, 2024 at 5:15 AM Bo Guo <[email protected]> wrote:
> Here is the table definition:
>
> CREATE TABLE IF NOT EXISTS azgiv.layers
> (
> gly_id integer NOT NULL DEFAULT
> nextval('azgiv.layers_gly_id_seq'::regclass),
> gly_name text COLLATE pg_catalog."default" NOT NULL,
> gly_cus_id integer NOT NULL,
> gly_desc text COLLATE pg_catalog."default",
> gly_glt_id integer,
> gly_tranx_create uuid NOT NULL,
> gly_tranx_delete uuid,
> gly_filename text COLLATE pg_catalog."default",
> gly_rowcount integer,
> gly_cgs_id_maint_type integer,
> gly_db_instance_rep text COLLATE pg_catalog."default",
> gly_topo json,
> gly_cgs_id_state integer,
> gly_last_sync_rep timestamp with time zone,
> gly_esri_fs_url text COLLATE pg_catalog."default",
> CONSTRAINT pk_layers PRIMARY KEY (gly_id),
> CONSTRAINT layers_gly_cus_id_fkey FOREIGN KEY (gly_cus_id)
> REFERENCES lba.lb_customers (cus_id) MATCH SIMPLE
> ON UPDATE NO ACTION
> ON DELETE NO ACTION,
> CONSTRAINT layers_gly_glt_id_fkey FOREIGN KEY (gly_glt_id)
> REFERENCES azgiv.layer_types (glt_id) MATCH SIMPLE
> ON UPDATE NO ACTION
> ON DELETE NO ACTION
> )
>
> TABLESPACE pg_default;
>
> ALTER TABLE IF EXISTS azgiv.layers
> OWNER to lb;
> -- Index: layers_idx_uk
>
> -- DROP INDEX IF EXISTS azgiv.layers_idx_uk;
>
> CREATE UNIQUE INDEX IF NOT EXISTS layers_idx_uk
> ON azgiv.layers USING btree
> (gly_cus_id ASC NULLS LAST, gly_glt_id ASC NULLS LAST,
> gly_cgs_id_maint_type ASC NULLS LAST, COALESCE(gly_tranx_delete::text,
> ''::text) COLLATE pg_catalog."default" ASC NULLS LAST)
> TABLESPACE pg_default;
>
>
>
> On Tue, May 14, 2024 at 5:10 AM Shane Borden <[email protected]> wrote:
>
>> What is the table definition?
>>
>> /d+
>>
>>
>>
>> Shane Borden
>> [email protected]
>> Sent from my iPhone
>>
>> On May 14, 2024, at 7:50 AM, Bo Guo <[email protected]> wrote:
>>
>>
>> I am using pgAdmin 4
>>
>> Version
>> 8.5
>> Application Mode
>> Server
>> Current User
>> [email protected]
>> 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 <[email protected]>
>> 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
>>>
>>> [email protected] <[email protected]>
>>>
>>> 703-600-9343
>>>
>>> <pgadvanced3.jpg>
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-05-14 13:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-14 11:11 Small table selection extremely slow! Bo Guo <[email protected]>
2024-05-14 11:16 ` David Rowley <[email protected]>
2024-05-14 11:26 ` MichaelDBA <[email protected]>
2024-05-14 11:50 ` Bo Guo <[email protected]>
2024-05-14 12:10 ` Shane Borden <[email protected]>
2024-05-14 12:15 ` Bo Guo <[email protected]>
2024-05-14 13:36 ` Bo Guo <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox