postgresql测试题_对许多PostgreSQL查询进行基准测试

On a complex webpage more than hundred queries are executed. I search for a way to benchmark these queries.

I tried to embed EXPLAIN ANALYZE into a query resp. into a psql function, to pick the execution time of every query and compare them.

But EXPLAIN ANALYZE seems not to be embeddable in any way.

Is there another solution to compare the execution time of some queries?

Thanks a lot

解决方案

There is no problem with embedding EXPLAIN in PL/pgSQL:

CREATE OR REPLACE FUNCTION profile(

IN query text,

OUT total_cost double precision,

OUT runtime double precision

) RETURNS record

LANGUAGE plpgsql STRICT AS

$$DECLARE

j json;

BEGIN

EXECUTE 'EXPLAIN (ANALYZE, FORMAT JSON) ' || query INTO j;

total_cost := (j->0->'Plan'->>'Total Cost')::double precision;

runtime := (j->0->'Plan'->>'Actual Total Time')::double precision;

RETURN;

END;$$;

You can use it for example as follows:

test=> SELECT * FROM profile($$SELECT * FROM large WHERE val = 'mama'$$);

┌────────────┬─────────┐

│ total_cost │ runtime │

├────────────┼─────────┤

│ 14542.43 │ 207.836 │

└────────────┴─────────┘

(1 row)

Don't use it with untrusted queries, as the function is vulnerable to SQL injection.