As an example, let's take the following simple table:
CREATE TABLE unary(a VARCHAR);
-- simple way to make table large
ALTER TABLE unary
ALTER COLUMN a SET STORAGE EXTERNAL;
-- insert one million large rows
INSERT INTO unary
SELECT repeat('a', 8000)
FROM generate_series(0, 100000);
-- update planner statistics on the unary table.
ANALYZE unary;
When I run
EXPLAIN ANALYZE SELECT * FROM unary;
I get the following result:
Seq Scan on unary (cost=0.00..1637.01 rows=100001 width=18) (actual
time=0.009..6.667 rows=100001 loops=1)
Planning Time: 0.105 ms
Execution Time: 8.565 ms
On the other hand, the following command
time sudo -u postgres psql -c "SELECT * FROM unary" -o /dev/null
returns after 17s with:
sudo -u postgres psql -c "SELECT * FROM unary" -o /dev/null 0.01s user
0.01s system 0% cpu 16.912 total
I am running Postgres 14 (installed via apt) on Ubuntu 22.04. All settings
are default.
The machine is a Dell Vostro 7500.
All commands are being run locally, so I don't think this is a network
bandwidth issue. What's going on?