Getting truncated queries from pg_stat_statements
I am trying to get some slow query information and the results from my
query are truncated at 2047 characters. Some of my queries are very long
so they get truncated in the select. Is there a way around this?
Here is my query.
WITH query_stats AS (
SELECT
query::text,
(total_time / 1000 / 60) as total_minutes,
(total_time / calls) as average_time,
calls
FROM
pg_stat_statements
INNER JOIN
pg_database ON pg_database.oid = pg_stat_statements.dbid
WHERE
pg_database.datname = current_database()
)
SELECT
query,
total_minutes,
average_time,
calls,
total_minutes * 100.0 / (SELECT SUM(total_minutes) FROM
query_stats) AS total_percent
FROM
query_stats
WHERE
calls >= 10
AND average_time >= 20
ORDER BY
average_time DESC
LIMIT 100
On Mon, Jan 19, 2015 at 2:37 PM, Tim Uckun <timuckun@gmail.com> wrote:
I am trying to get some slow query information and the results from my query
are truncated at 2047 characters. Some of my queries are very long so they
get truncated in the select. Is there a way around this?
Yes. This is fixed in PostgreSQL 9.4, so that query texts can be of
virtually unlimited size and still be stored. Otherwise, it depends on
your track_activity_query_size setting.
--
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Ok thanks. I am still on 9.3 so I'll adjust that setting.
On Tue, Jan 20, 2015 at 12:15 PM, Peter Geoghegan <
peter.geoghegan86@gmail.com> wrote:
Show quoted text
On Mon, Jan 19, 2015 at 2:37 PM, Tim Uckun <timuckun@gmail.com> wrote:
I am trying to get some slow query information and the results from my
query
are truncated at 2047 characters. Some of my queries are very long so
they
get truncated in the select. Is there a way around this?
Yes. This is fixed in PostgreSQL 9.4, so that query texts can be of
virtually unlimited size and still be stored. Otherwise, it depends on
your track_activity_query_size setting.--
Regards,
Peter Geoghegan
Since you are on 9.3. you might want to consider using PgBadger as a better
way to get information about slow queries.
http://sourceforge.net/projects/pgbadger/
On Mon, Jan 19, 2015 at 6:41 PM, Tim Uckun <timuckun@gmail.com> wrote:
Ok thanks. I am still on 9.3 so I'll adjust that setting.
On Tue, Jan 20, 2015 at 12:15 PM, Peter Geoghegan <
peter.geoghegan86@gmail.com> wrote:On Mon, Jan 19, 2015 at 2:37 PM, Tim Uckun <timuckun@gmail.com> wrote:
I am trying to get some slow query information and the results from my
query
are truncated at 2047 characters. Some of my queries are very long so
they
get truncated in the select. Is there a way around this?
Yes. This is fixed in PostgreSQL 9.4, so that query texts can be of
virtually unlimited size and still be stored. Otherwise, it depends on
your track_activity_query_size setting.--
Regards,
Peter Geoghegan
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Thanks for that tip. I'll check it out.
On Tue, Jan 20, 2015 at 3:12 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:
Show quoted text
Since you are on 9.3. you might want to consider using PgBadger as a
better way to get information about slow queries.
http://sourceforge.net/projects/pgbadger/On Mon, Jan 19, 2015 at 6:41 PM, Tim Uckun <timuckun@gmail.com> wrote:
Ok thanks. I am still on 9.3 so I'll adjust that setting.
On Tue, Jan 20, 2015 at 12:15 PM, Peter Geoghegan <
peter.geoghegan86@gmail.com> wrote:On Mon, Jan 19, 2015 at 2:37 PM, Tim Uckun <timuckun@gmail.com> wrote:
I am trying to get some slow query information and the results from my
query
are truncated at 2047 characters. Some of my queries are very long
so they
get truncated in the select. Is there a way around this?
Yes. This is fixed in PostgreSQL 9.4, so that query texts can be of
virtually unlimited size and still be stored. Otherwise, it depends on
your track_activity_query_size setting.--
Regards,
Peter Geoghegan--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.