public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shane Borden <[email protected]>
To: Bo Guo <[email protected]>
Cc: MichaelDBA <[email protected]>
Cc: [email protected]
Subject: Re: Small table selection extremely slow!
Date: Tue, 14 May 2024 08:10:08 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CADHFRciuw1WowSDNac0AnN1RBGZkc0kZ8Ty0V=vfJPYgm8Ed2A@mail.gmail.com>
References: <CADHFRciuw1WowSDNac0AnN1RBGZkc0kZ8Ty0V=vfJPYgm8Ed2A@mail.gmail.com>
--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--
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Small table selection extremely slow!
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox