Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s6r4J-0028cg-L7 for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 12:15:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s6r4J-00DxuN-Dn for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 12:15:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s6r4I-00Dxnc-O1 for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 12:15:43 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s6r4F-0009kR-L9 for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 12:15:41 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a599a298990so25566466b.2 for ; Tue, 14 May 2024 05:15:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gisticinc-com.20230601.gappssmtp.com; s=20230601; t=1715688936; x=1716293736; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=W6I+aIDCkGi7NDngIJzQ0xjfENckmOb6sDazvOsQo+4=; b=EOjoYUVf4V5S5qun+pixpzNOuKFpx9uobuW3wHQHtHnYeoTniXT0e0IY1z3uOVoj+N T886IXHDaDvlhHyCGdupH+SNeDdWbBtXH+pSG8aAeFIAIZXS/Wm5W2fFGbTIb+njTFPS bcoaN9a1Wnvves4pI2GGrQ6P0fRU2kJ+zGcZx9SEmVxeCNcF161w5EKNbOLO42knD1A/ 0nrKA5F4cIiRVOMgu0I3MkCNlB4ivYvhIBg6xQtJd4acPFfq6yx8KhuxTluGfg8Bornk nQtPHr3i53rzI6Y7AYYrIY1bxQ98crP09WcOWjyHSlzTwASZsQ7RBdQpQVVLIOAV/Yn6 16cg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715688936; x=1716293736; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=W6I+aIDCkGi7NDngIJzQ0xjfENckmOb6sDazvOsQo+4=; b=M8TEbMNDBJU8maphY9i1b+LIskikI9KDhhwJXTvE9kw3UZJXTCAThmMsiCgYQsrgRH kwOjMZmkhTi37f00MlRQTqJVy9K6hqaXKAcq2CMO5C6PmpeicAc7EpFqlYtMZIV9kBy1 DxCMt4GhDxfjnWiibZ8I+KdaNZwhzEtg4orLHGxGDt67BBKRMeKoeoIxcsNRBZ9rdko+ C3I2TnK2VkkfBdgK2V+U/H0sjI6WWB1cCtqyemhIOLO6qfrkOkvW0VvUnH36cVmGDyfu +9ZG3NxiUV28E60sojtIS8Ic36LyKxHEdOaR+oT9MAYXlnTPQgu3RIw/tVaQKCk+/C+B aL2g== X-Forwarded-Encrypted: i=1; AJvYcCVZ4DOoXNgQVMznZIqKD0d2CtStRpmDRGYQuTdIEq0Uc6SxNSXcdCA+j7pLd7EX2e3gfycHrDNGNa9y14gR5izMJOSbRNwBzlJL9hw3c70= X-Gm-Message-State: AOJu0YzMN0Rs/A2RFMhPEQNOJAuwHFvTHI/pAbzay5QHcbev9dtjlLzN 3w5YnAF2zE17DmTqMMzupmDvmFfbRi4BAmVP7e83ts1ao03z6Pmbf0LyH/a7LI3hQIFLGz/FPFi zvnpOScZ9LUbeiFRKsl4AIcuW/S0YHV77PfddEg== X-Google-Smtp-Source: AGHT+IE2SeYcwSN30P5kIs3bSMQs+ISxIGOzEo5X7np8M9bn6L0nno0QG+0dQiyWsO1/Zm8TH7S15rV6moJ/KFkD6vg= X-Received: by 2002:a17:906:3b97:b0:a5a:6f4f:e54e with SMTP id a640c23a62f3a-a5a6f4fe659mr326564966b.65.1715688936646; Tue, 14 May 2024 05:15:36 -0700 (PDT) MIME-Version: 1.0 References: <19A7E904-19DC-4692-9319-929480DA117A@gmail.com> In-Reply-To: <19A7E904-19DC-4692-9319-929480DA117A@gmail.com> From: Bo Guo Date: Tue, 14 May 2024 05:15:24 -0700 Message-ID: Subject: Re: Small table selection extremely slow! To: Shane Borden Cc: MichaelDBA , pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000009d48da061868f536" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009d48da061868f536 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Shane Borden = wrote: > What is the table definition? > > /d+ > > > > Shane Borden > sborden76@gmail.com > Sent from my iPhone > > On May 14, 2024, at 7:50=E2=80=AFAM, Bo Guo wrote: > > =EF=BB=BF > 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=E2=80=AFAM MichaelDBA wrote: > >> You don't elaborate on where you are seeing this "20 seconds". Than >> means network, client application stuff, locking/waiting, or other thing= s >> 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=3D0.00..14.00 rows=3D200 width=3D233) (actual >> time=3D0.010..0.087 rows=3D200 loops=3D1) >> Buffers: shared hit=3D12 >> Planning: >> Buffers: shared hit=3D51 >> Planning Time: 0.233 ms >> Execution Time: 0.121 ms >> >> >> I am afraid that I have missed something obvious. Please kindly point i= t >> out. Many thanks! >> >> Bo >> >> >> >> Regards, >> >> Michael Vitale >> >> Michaeldba@sqlexec.com >> >> 703-600-9343 >> >> >> >> >> --0000000000009d48da061868f536 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Here i= s the table definition:

<= blockquote style=3D"margin:0 0 0 40px;border:none;padding:0px">
CREATE TAB= LE IF NOT EXISTS azgiv.layers
(
=C2=A0 =C2=A0 gly_id integer NOT NULL= DEFAULT nextval('azgiv.layers_gly_id_seq'::regclass),
=C2=A0 = =C2=A0 gly_name text COLLATE pg_catalog."default" NOT NULL,
= =C2=A0 =C2=A0 gly_cus_id integer NOT NULL,
=C2=A0 =C2=A0 gly_desc text C= OLLATE pg_catalog."default",
=C2=A0 =C2=A0 gly_glt_id integer,=
=C2=A0 =C2=A0 gly_tranx_create uuid NOT NULL,
=C2=A0 =C2=A0 gly_tran= x_delete uuid,
=C2=A0 =C2=A0 gly_filename text COLLATE pg_catalog."= default",
=C2=A0 =C2=A0 gly_rowcount integer,
=C2=A0 =C2=A0 gly_= cgs_id_maint_type integer,
=C2=A0 =C2=A0 gly_db_instance_rep text COLLAT= E pg_catalog."default",
=C2=A0 =C2=A0 gly_topo json,
=C2=A0= =C2=A0 gly_cgs_id_state integer,
=C2=A0 =C2=A0 gly_last_sync_rep timest= amp with time zone,
=C2=A0 =C2=A0 gly_esri_fs_url text COLLATE pg_catalo= g."default",
=C2=A0 =C2=A0 CONSTRAINT pk_layers PRIMARY KEY (g= ly_id),
=C2=A0 =C2=A0 CONSTRAINT layers_gly_cus_id_fkey FOREIGN KEY (gly= _cus_id)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 REFERENCES lba.lb_customers (cus_id= ) MATCH SIMPLE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ON UPDATE NO ACTION
=C2=A0= =C2=A0 =C2=A0 =C2=A0 ON DELETE NO ACTION,
=C2=A0 =C2=A0 CONSTRAINT laye= rs_gly_glt_id_fkey FOREIGN KEY (gly_glt_id)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = REFERENCES azgiv.layer_types (glt_id) MATCH SIMPLE
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 ON UPDATE NO ACTION
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ON DELETE NO ACTI= ON
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS azgiv.la= yers
=C2=A0 =C2=A0 OWNER to lb;
-- Index: layers_idx_uk

-- DRO= P INDEX IF EXISTS azgiv.layers_idx_uk;

CREATE UNIQUE INDEX IF NOT EX= ISTS layers_idx_uk
=C2=A0 =C2=A0 ON azgiv.layers USING btree
=C2=A0 = =C2=A0 (gly_cus_id ASC NULLS LAST, gly_glt_id ASC NULLS LAST, gly_cgs_id_ma= int_type ASC NULLS LAST, COALESCE(gly_tranx_delete::text, ''::text)= COLLATE pg_catalog."default" ASC NULLS LAST)
=C2=A0 =C2=A0 TA= BLESPACE pg_default;



On Tue, May 14, 2024 at 5:10=E2=80=AFAM Shane Borde= n <sborden76@gmail.com> wr= ote:
What is the table definition?

/d+

<= /div>


Shane Borden
sborden76@gmail.com
Sent from my = iPhone

On M= ay 14, 2024, at 7:50=E2=80=AFAM, Bo Guo <bo.guo@gisticinc.com> wrote:

=EF=BB=BF
I am using pgAdmin 4
Version
=
The performance is 0.16 ms when=C2=A0

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=E2=80=AFAM = MichaelDBA <= MichaelDBA@sqlexec.com> wrote:
You don't elaborate on where you are seeing this "20 seconds".=C2=A0 Than means network, client application s= tuff, locking/waiting, or other things may come into play here... Please provide more info.


Bo Guo wrote on 5/14/2024 7:11 AM:
=20
Hi,=C2=A0=C2=A0

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: =C2=A0vacuuming "azgiv.layers"
INFO: =C2=A0table "layers": found 0 removable, 200 nonremovable row versions in 12 out of 12 pages
I= NFO: =C2=A0vacuuming "pg_toast.pg_toast_52182"
=
INFO: =C2=A0table "pg_toast_52182": index scan bypassed: = 35 pages from table (0.69% of total) have 140 dead item identifiers
INFO: =C2=A0table "pg_toast_52182": found 136 re= movable, 6 nonremovable row versions in 36 out of 5070 pages
<= /div>
INFO: =C2=A0analyzing "azgiv.layers"
I= NFO: =C2=A0"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)=C2=A0SELECT * FROM azgiv.layers;
=
<= div dir=3D"ltr">

<= /div>
Seq Scan on layers =C2=A0(cost=3D0.00..14.00 rows=3D200 width=3D233) (actual time=3D0.010..0.087 rows=3D200 loops=3D1)
=
=C2=A0 Buffers: shared hit=3D12
Pl= anning:
<= div>
=C2=A0 Buffers: shared hit=3D51
=C2=A0Planning Time: 0.233 ms
= =C2=A0Execution Time: 0.121 ms
<= div dir=3D"ltr" class=3D"gmail_signature">

I am afraid that I have missed something obvious.=C2=A0 Please kindly point it out.=C2=A0 Many than= ks!

Bo


Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343

=C2=A0
<pgadvanced3.jpg>


--0000000000009d48da061868f536--