Skip to content

Instantly share code, notes, and snippets.

@StevenJL

StevenJL/psql_query_most_rows.sql

Last active August 16, 2021 06:30
Show Gist options
  • Save StevenJL/8442163b9ae9815efa37f22d7e44113c to your computer and use it in GitHub Desktop.
Save StevenJL/8442163b9ae9815efa37f22d7e44113c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. StevenJL revised this gist Feb 8, 2021. 1 changed file with 19 additions and 2 deletions.
    21 changes: 19 additions & 2 deletions psql_query_most_rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,24 @@
    # See the 10 SELECT queries which touch the most number of rows.
    # These queries may benefit from adding indexes to reduce the number
    # of rows retrieved/affected.
    SELECT
    rows,
    rows, # rows is the number of rows retrieved/affected by the query
    query
    FROM pg_stat_statements
    WHERE query iLIKE '%SELECT%'
    ORDER BY rows DESC
    LIMIT 100;
    LIMIT 10;

    # query | rows
    # --------+--------
    # SELECT | 701724
    # SELECT | 284772
    # SELECT | 233914
    # SELECT | 196424
    # SELECT | 164421
    # SELECT | 164419
    # SELECT | 164419
    # SELECT | 164419
    # SELECT | 138395
    # SELECT | 117636
    # (10 rows)
  2. StevenJL created this gist Jan 24, 2021.
    7 changes: 7 additions & 0 deletions psql_query_most_rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,7 @@
    SELECT
    rows,
    query
    FROM pg_stat_statements
    WHERE query iLIKE '%SELECT%'
    ORDER BY rows DESC
    LIMIT 100;