Small table selection extremely slow!

From: Bo Guo <bo(dot)guo(at)gisticinc(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Small table selection extremely slow!
Date: 2024-05-14 11:11:56
Message-ID: CADHFRcjzJoRiPoJ_5Rf9ynetBPt0R5c4L_N_k-uja=W1+izvVg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David Rowley 2024-05-14 11:16:05 Re: Small table selection extremely slow!
Previous Message Feike Steenbergen 2024-05-03 16:43:42 Re: How to pass arguments in postgres to sql scripts.