Subject: Small table selection extremely slow!

From: Bo Guo <[email-masked]>
Date: Tue, 14 May 2024 04:11:56 -0700

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

---

From: David Rowley <[email-masked]>
Date: Tue, 14 May 2024 23:16:05 +1200

On Tue, 14 May 2024 at 23:12, Bo Guo <[email-masked]> wrote:
> The following query took 20 seconds on a small table of 108 rows with a dozen columns:
>
> 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

---

From: MichaelDBA <[email-masked]>
Date: Tue, 14 May 2024 07:26:40 -0400

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:
>     INFO:  table "layers": found 0 removable, 200 nonremovable row
>     versions in 12 out of 12 pages
>     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:  "layers": scanned 12 of 12 pages, containing 200 live rows
>     and 0 dead rows; 200 rows in sample, 200 estimated total rows
>     VACUUM
>
>
> I am afraid that I have missed something obvious.  Please kindly point
> it out.  Many thanks!
>
> Bo

Regards,

Michael Vitale

[email-masked] <mailto:[email-masked]>

703-600-9343

---

From: Bo Guo <[email-masked]>
Date: Tue, 14 May 2024 04:50:25 -0700

I am using pgAdmin 4

Version
8.5
Application Mode
Server
Current User
[email-masked]
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-masked]> 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.
>
>
> [email-masked] <[email-masked]>
>
> 703-600-9343
>
>
>
>

---

From: Shane Borden <[email-masked]>
Date: Tue, 14 May 2024 08:10:08 -0400

What is the table definition?

/d+

Shane Borden
[email-masked]

Sent from my iPhone

On May 14, 2024, at 7:50 AM, Bo Guo <[email-masked]> wrote:

I am using pgAdmin 4

Version

8.5

Application Mode

Server

Current User

[email-masked]

Browser

Firefox 125.0

Operating System

We do not experience any slowness on other much larger tables with SELECT * FROM OtherTable;

Bo

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.

Hi,

The
following query took 20 seconds on a small table of 108 rows with a
dozen columns:

VACUUM (VERBOSE,
ANALYZE) azgiv.layers

INFO:  table "layers": found 0 removable, 200
nonremovable row versions in 12 out of 12 pages

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:  "layers": scanned 12 of 12 pages, containing
200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total
rows

VACUUM

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

703-600-9343

---

From: Bo Guo <[email-masked]>
Date: Tue, 14 May 2024 05:15:24 -0700

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-masked]> wrote:

>> means network, client application stuff, locking/waiting, or other things
>> may come into play here... Please provide more info.
>>
>>
>> INFO:  table "layers": found 0 removable, 200 nonremovable row versions
>> in 12 out of 12 pages

---

From: Bo Guo <[email-masked]>
Date: Tue, 14 May 2024 06:36:15 -0700

Thanks for your responses!  The mystery is solved  -  It turned out that
the JSON column in some rows contained rather large data.

Bo