Re: Small table selection extremely slow!

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

Bo

On Tue, May 14, 2024 at 5:15 AM Bo Guo <bo(dot)guo(at)gisticinc(dot)com> wrote:

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