Re: Small table selection extremely slow!

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 <michaelvitale(at)sqlexec(dot)com>
>
> 703-600-9343
>
>
>
>