public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bo Guo <[email protected]>
To: Shane Borden <[email protected]>
Cc: MichaelDBA <[email protected]>
Cc: [email protected]
Subject: Re: Small table selection extremely slow!
Date: Tue, 14 May 2024 05:15:24 -0700
Message-ID: <CADHFRcjFjCi-5X8NgZM9z5JK2QjQLumOAELQM0oo5K1NG=YyLw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CADHFRciuw1WowSDNac0AnN1RBGZkc0kZ8Ty0V=vfJPYgm8Ed2A@mail.gmail.com>
	<[email protected]>

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

> What is the table definition?
>
> /d+
>
>
>
> Shane Borden
> [email protected]
> Sent from my iPhone
>
> On May 14, 2024, at 7:50 AM, Bo Guo <[email protected]> wrote:
>
> 
> I am using pgAdmin 4
>
> Version
> 8.5
> Application Mode
> Server
> Current User
> [email protected]
> 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 protected]> 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
>>
>> [email protected] <[email protected]>
>>
>> 703-600-9343
>>
>> <pgadvanced3.jpg>
>>
>>
>>

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Small table selection extremely slow!
  In-Reply-To: <CADHFRcjFjCi-5X8NgZM9z5JK2QjQLumOAELQM0oo5K1NG=YyLw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox