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 1s6sKc-002EH5-1t for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 13:36:39 +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 1s6sKb-00EipO-Nb for pgsql-sql@arkaria.postgresql.org; Tue, 14 May 2024 13:36:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s6sKb-00EipF-7W for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 13:36:37 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s6sKT-000CK0-4G for pgsql-sql@lists.postgresql.org; Tue, 14 May 2024 13:36:36 +0000 Received: by mail-ej1-x629.google.com with SMTP id a640c23a62f3a-a5a7d28555bso42089466b.1 for ; Tue, 14 May 2024 06:36:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gisticinc-com.20230601.gappssmtp.com; s=20230601; t=1715693787; x=1716298587; 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=m8LPD5GbOtu73UmLCuJao1ZxjdRV8UkP8kYxGlbzZBg=; b=YWPfMBxc1wapgcdlgbUMNjPwFhhhZdo73fWKsyxHMBoeX58yXkA3i//GaWFQgvPdf5 dLJmeW6IXx03h3Bd04zki+/IkW0tdXmaDRV6vsuu/RZMyM8dqoUHMOWtu5Tz4F1EcAIv PXbvT+U2UZmi1w4GlDtxbN/v7LPuSY5eThQwFf0S15FSzZ2+QbQsQAwNpuQpyqRjC68s XYsAZdRTa1PimHsQ1OT3CHZtOtirxzOcT0Rt3RQhDQwAkDAiR34W6w7M2QfNy5bbTq+0 V8ehLMQXdhzwNHnoPrU6MtjyX/Gmce07YCTzorh/eFgsfQzB3pvVEv6sHAwJBe3bvOUO 7p5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715693787; x=1716298587; 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=m8LPD5GbOtu73UmLCuJao1ZxjdRV8UkP8kYxGlbzZBg=; b=YTJ1jL/loJaFQ8AxZt4RezSXCRIRQybQcSfxQY/7nB0PV+eAmO8j3WxMgBP36RzvxZ pi5iWdqUhkMPfC5KBN62f2L3p87WiJrzuJGuJdARZXkRz++7mlTZpOVMkcf/NtgIJNs4 4HbBosdio/z0Egq2keEV7yT51xHeudynlF18R9vXRzLO2hKDf7+efM471XZEIPTtYWMI oGz30byL5a2ch01yjzXbU0Qw2AlheW0XRl76RPcXPArIuKALMr9i/k68uBcKjw8ff6Qz iM7MElohsT+YQ+6dlEjyThSr5AeETYL7ebQ/y0R4MMoFARZuL6mPd2GUkHw/+yH8mFVv y3Cg== X-Forwarded-Encrypted: i=1; AJvYcCVh32cs+VO1cSi5LnlbYde+uXMCaPkT1uXZvlPFHNXbU3v262ZFDYXubX3Mxun0jM71VKlz0bHJYBVREHbpYRGuWn9FisXLqj+2Mte1l+o= X-Gm-Message-State: AOJu0Yw8mX0dQtbuluySJfTqzyUZjEzBnFoJ+R3/yyYea5oWw7aVpqGm e0n9o8Tgs02/brEUyUsZxAWVIFujb15j7fPxPqLdvIu7UdCe3oGIJcHbtf0m8yZkoSyPuVbDUuH tCH1s9+ekUhNWQdK2P/STmhEcGACznaorzKITbA== X-Google-Smtp-Source: AGHT+IFcHLBmokW5M6lCzrktWgqB630GL13hQEwfFfDkVoaqIXmBl5nj6RTE5IBnTGTTZZzk1amuwCU00rWud+Fks14= X-Received: by 2002:a17:907:9710:b0:a5a:2d30:b8c1 with SMTP id a640c23a62f3a-a5a2d54c038mr1061727366b.14.1715693786583; Tue, 14 May 2024 06:36:26 -0700 (PDT) MIME-Version: 1.0 References: <19A7E904-19DC-4692-9319-929480DA117A@gmail.com> In-Reply-To: From: Bo Guo Date: Tue, 14 May 2024 06:36:15 -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="000000000000b16e7a06186a1648" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b16e7a06186a1648 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Bo Guo 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=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 SELEC= T >> * 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 thin= gs >>> 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 >>> it out. Many thanks! >>> >>> Bo >>> >>> >>> >>> Regards, >>> >>> Michael Vitale >>> >>> Michaeldba@sqlexec.com >>> >>> 703-600-9343 >>> >>> >>> >>> >>> --000000000000b16e7a06186a1648 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks for your responses!=C2=A0 The mystery is solved=C2= =A0 -=C2=A0 It turned out that the JSON column in some rows contained rathe= r large data.=C2=A0

Bo

On Tue, May 14, 2024 at 5:15= =E2=80=AFAM Bo Guo <bo.guo@gisti= cinc.com> wrote:
Here is the table definition:

CREATE TABLE IF NOT EXISTS azgiv.layers
(
=C2=A0 =C2=A0 gly_= id integer NOT NULL DEFAULT nextval('azgiv.layers_gly_id_seq'::regc= lass),
=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 COLLATE 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_tranx_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 COLLATE 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 timestamp with time zone,
=C2=A0 =C2=A0 gly_esri_fs_url text CO= LLATE pg_catalog."default",
=C2=A0 =C2=A0 CONSTRAINT pk_layers= PRIMARY KEY (gly_id),
=C2=A0 =C2=A0 CONSTRAINT layers_gly_cus_id_fkey F= OREIGN KEY (gly_cus_id)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 REFERENCES lba.lb_cu= stomers (cus_id) MATCH SIMPLE
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ON UPDATE NO A= CTION
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ON DELETE NO ACTION,
=C2=A0 =C2=A0 = CONSTRAINT layers_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 ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF = EXISTS azgiv.layers
=C2=A0 =C2=A0 OWNER to lb;
-- Index: layers_idx_u= k

-- DROP INDEX IF EXISTS azgiv.layers_idx_uk;

CREATE UNIQUE = INDEX IF NOT EXISTS layers_idx_uk
=C2=A0 =C2=A0 ON azgiv.layers USING bt= ree
=C2=A0 =C2=A0 (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, = 9;'::text) COLLATE pg_catalog."default" ASC NULLS LAST)
= =C2=A0 =C2=A0 TABLESPACE pg_default;

<= /div>


On Tue, May 14, 2024 at 5:10=E2=80= =AFAM 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=E2=80=AFAM, Bo Guo <bo.guo@gisticinc.com> wrote:

=EF=BB=BF
I am using= pgAdmin 4
<= label>Firefox 125.0
Linux-5.15.143-1-pve-x86_64-with-glibc2.35
The performance is 0.16 ms when=C2=A0

SELECT gly_id, gly_name FROM azgiv.layers;=

We do not experien= ce any slowness on other much larger tables with S= ELECT * FROM OtherTable;

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


--000000000000b16e7a06186a1648--