Re: contrib/pg_stat_statements

Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com> wrote:

> Attached is a patch that adds sourceText to QueryDesc.

It worked fine surprisingly :) . Internal and C functions don't use
executor, so we can ignore trivial function calls (ex. operators).
Furthermore, it is ok if QueryDesc doesn't have queryText
because the result is counted up in the upper statement.

> But is the idea of extending QueryDesc generally acceptable? Is it OK
> to make a copy of the query string?

The only thing I'm worried about is that QueryDesc lives longer than
its queryText. Can I assume it never occurs?

> I tested with modified pg_stat_statements (removed toplevel checks),

Stack of counters would be better. The attached is modified to do so.
It might be worth thinking about adding counters that are equivalent
to total_time and self_time in in pg_stat_user_functions.

=# CREATE OR REPLACE FUNCTION plfn(integer) RETURNS bigint AS
$$
DECLARE
i bigint;
BEGIN
SELECT count(*) INTO i FROM pg_class;
SELECT count(*) INTO i FROM pg_class;
SELECT count(*) INTO i FROM generate_series(1, $1);
RETURN i;
END;
$$
LANGUAGE plpgsql;

=# SELECT sum(plfn(10000)) FROM generate_series(1, 100);
=# SELECT query, calls, total_time, rows FROM pg_stat_statements;

query | calls | total_time | rows
-------------------------------------------------------+-------+------------+------
SELECT sum(plfn(10000)) FROM generate_series(1, 100); | 1 | 428 | 1
SELECT count(*) FROM pg_class | 200 | 32 | 200
SELECT count(*) FROM generate_series(1, $1 ) | 100 | 309 | 100
(3 rows)

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center