public inbox for [email protected]  

help / color / mirror / Atom feed

From: MichaelDBA <[email protected]>
To: Bo Guo <[email protected]>
Cc: [email protected]
Subject: Re: Small table selection extremely slow!
Date: Tue, 14 May 2024 07:26:40 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CADHFRcjzJoRiPoJ_5Rf9ynetBPt0R5c4L_N_k-uja=W1+izvVg@mail.gmail.com>
References: <CADHFRcjzJoRiPoJ_5Rf9ynetBPt0R5c4L_N_k-uja=W1+izvVg@mail.gmail.com>

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

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]
  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