Postgres Pro
Downloads
Home   >   mailing lists

Thread: Getting truncated queries from pg_stat_statements

Getting truncated queries from pg_stat_statements

From
Tim Uckun
Date:
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

Re: Getting truncated queries from pg_stat_statements

From
Peter Geoghegan
Date:
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

Re: Getting truncated queries from pg_stat_statements

From
Tim Uckun
Date:
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

Re: Getting truncated queries from pg_stat_statements

From
Melvin Davidson
Date:
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.

Re: Getting truncated queries from pg_stat_statements

From
Tim Uckun
Date:
Thanks for that tip. I'll check it out.

On Tue, Jan 20, 2015 at 3:12 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
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.