(unnamed)

text/plain

Download

\set ECHO all
\timing
DROP TABLE perftest;
CREATE TABLE perftest (col text);

-- prime table with one row
INSERT INTO perftest VALUES ('0.364461265208414');

-- continously double the table size
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;
INSERT INTO perftest SELECT random()::text FROM perftest;

-- insert a constant in the middle of the table, for use later
INSERT INTO perftest VALUES ('0.608254158221304');
INSERT INTO perftest SELECT random()::text FROM perftest;
-- 32770 rows

-- vacuum, create index
VACUUM ANALYZE perftest;
CREATE INDEX i_perftest ON perftest (col);
-- reduce chance of checkpoint during tests
CHECKPOINT;

-- turn on logging
SET log_duration = TRUE;
SET client_min_messages = 'log';

-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');

-- first time the entire statement
SET log_statement_stats = TRUE;

-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
PREPARE perftest_prep (text) AS SELECT col FROM perftest WHERE col = $1;
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');

-- now log each query stage
SET log_statement_stats = FALSE;
SET log_parser_stats = TRUE;
SET log_planner_stats = TRUE;
SET log_executor_stats = TRUE;

-- run normal and prepared queries
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
SELECT col FROM perftest WHERE col = '0.608254158221304';
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');
EXECUTE perftest_prep ('0.608254158221304');