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;