Re: Small table selection extremely slow! - Mailing list pgsql-sql

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 <sborden76@gmail.com> wrote:

What is the table definition?

/d+

Shane Borden

sborden76@gmail.com

Sent from my iPhone

On May 14, 2024, at 7:50 AM, Bo Guo <bo.guo@gisticinc.com> wrote:



I am using pgAdmin 4

Version

8.5

Application Mode

Server

Current User

pgadmin@linearbench.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@sqlexec.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@sqlexec.com

703-600-9343 

<pgadvanced3.jpg>