Re: Small table selection extremely slow!

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(at)gmail(dot)com> wrote:

> What is the table definition?
>
> /d+
>
>
>
> Shane Borden
> sborden76(at)gmail(dot)com
> Sent from my iPhone
>
> On May 14, 2024, at 7:50 AM, Bo Guo <bo(dot)guo(at)gisticinc(dot)com> wrote:
>
> 
> 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
>>
>> <pgadvanced3.jpg>
>>
>>
>>