EXPLAIN ANALYZE does not return accurate execution times

From: Mark Mizzi <mizzimark2001(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: EXPLAIN ANALYZE does not return accurate execution times
Date: 2022-10-27 13:28:14
Message-ID: CANQK6i8FeAXhTa9F6p8JKoVRHene5da7vLjrA3+P=A2XR6J2pQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Julien Rouhaud 2022-10-27 13:35:10 Re: EXPLAIN ANALYZE does not return accurate execution times
Previous Message Stefan Knecht 2022-10-27 12:48:37 Re: Value Too long varchar(100)