Home > mailing lists

Thread: Small table selection extremely slow!

Small table selection extremely slow!

From

Bo Guo

Date:

14 May, 14:11:56

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!

Re: Small table selection extremely slow!

From

David Rowley

Date:

14 May, 14:16:05

On Tue, 14 May 2024 at 23:12, Bo Guo <bo.guo@gisticinc.com> wrote:
> The following query took 20 seconds on a small table of 108 rows with a dozen columns:
>
> SELECT * FROM azgiv.layers;
>
> 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

One difference between the SELECT and EXPLAIN ANALYZE is that SELECT
will detoast your toasted datums whereas EXPLAIN ANALYZE will not.

It's likely the slowness comes from the detoasting.

David

Re: Small table selection extremely slow!

From

MichaelDBA

Date:

14 May, 14:26:40

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@sqlexec.com

703-600-9343

Attachment

pgadvanced3.jpg

Re: Small table selection extremely slow!

From

Bo Guo

Date:

14 May, 14:50:25

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;

On Tue, May 14, 2024 at 4:26 AM MichaelDBA <MichaelDBA@sqlexec.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@sqlexec.com
703-600-9343

Attachment

pgadvanced3.jpg

Re: Small table selection extremely slow!

From

Bo Guo

Date:

14 May, 15:15:24

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@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 AM, Bo Guo <bo.guo@gisticinc.com> wrote:

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 AM MichaelDBA <MichaelDBA@sqlexec.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@sqlexec.com
703-600-9343
<pgadvanced3.jpg>

Re: Small table selection extremely slow!

From

Bo Guo

Date:

14 May, 16:36:15

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

On Tue, May 14, 2024 at 5:15 AM Bo Guo <bo.guo@gisticinc.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@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 AM, Bo Guo <bo.guo@gisticinc.com> wrote:

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 AM MichaelDBA <MichaelDBA@sqlexec.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@sqlexec.com
703-600-9343
<pgadvanced3.jpg>