packages feed

pg-extras (empty) → 0.0.1

raw patch · 29 files changed

+1537/−0 lines, 29 filesdep +HUnitdep +basedep +bytestringsetup-changed

Dependencies added: HUnit, base, bytestring, pg-extras, postgresql-simple, raw-strings-qq, text, time

Files

+ CHANGES.md view
@@ -0,0 +1,3 @@+### Version 0.0.1++Initial implementation.
+ LICENSE view
@@ -0,0 +1,22 @@+The MIT License (MIT)++Copyright © Paweł Urbanek 2020++Permission is hereby granted, free of charge, to any person obtaining+a copy of this software and associated documentation files (the+"Software"), to deal in the Software without restriction, including+without limitation the rights to use, copy, modify, merge, publish,+distribute, sublicense, and/or sell copies of the Software, and to+permit persons to whom the Software is furnished to do so, subject to+the following conditions:++The above copyright notice and this permission notice shall be+included in all copies or substantial portions of the Software.++THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,+EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF+MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.+IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY+CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,+TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE+SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
+ Setup.hs view
@@ -0,0 +1,2 @@+import Distribution.Simple+main = defaultMain
+ pg-extras.cabal view
@@ -0,0 +1,84 @@+cabal-version:       2.4+-- Initial package description 'pg-extras.cabal' generated by+-- 'cabal init'.  For further documentation, see+-- http://haskell.org/cabal/users-guide/++name:                pg-extras+version:             0.0.1+synopsis:            PostgreSQL database performance insights.+description:+  Haskell port of Heroku PG Extras. The goal of this project is to provide a powerful insights into PostgreSQL database for Ruby on Rails apps that are not using the default Heroku PostgreSQL plugin.++license:             MIT+license-file:        LICENSE+author:              Paweł Urbanek+maintainer:          Paweł Urbanek <contact@pawelurbanek.com>+copyright:+  (c) 2020 Paweł Urbanek+category:            Database+build-type:          Simple+extra-source-files:+  CHANGES.md++tested-with:+  GHC ==7.6.3+   || ==7.8.4+   || ==7.10.3+   || ==8.0.2+   || ==8.2.2+   || ==8.4.4+   || ==8.6.5+   || ==8.8.4+   || ==8.10.2++source-repository head+  type:     git+  location: http://github.com/pawurb/haskell-pg-extras++library+  ghc-options:     -Wall -fno-warn-name-shadowing+  hs-source-dirs:  src+  exposed-modules: PGExtras+  other-modules:+                   PGExtras.Queries.AllLocks+                   PGExtras.Queries.Bloat+                   PGExtras.Queries.Blocking+                   PGExtras.Queries.CacheHit+                   PGExtras.Queries.Calls+                   PGExtras.Queries.Extensions+                   PGExtras.Queries.IndexCacheHit+                   PGExtras.Queries.IndexSize+                   PGExtras.Queries.IndexUsage+                   PGExtras.Queries.KillAll+                   PGExtras.Queries.Locks+                   PGExtras.Queries.LongRunningQueries+                   PGExtras.Queries.Mandelbrot+                   PGExtras.Queries.RecordsRank+                   PGExtras.Queries.SeqScans+                   PGExtras.Queries.TableCacheHit+                   PGExtras.Queries.TableIndexesSize+                   PGExtras.Queries.TableSize+                   PGExtras.Queries.TotalIndexSize+                   PGExtras.Queries.TotalTableSize+                   PGExtras.Queries.UnusedIndexes+                   PGExtras.Queries.VacuumStats+                   PGExtras.Helpers+  build-depends:+    base                >= 4.13.0 && < 4.14+    , postgresql-simple >= 0.6.3 && < 0.7+    , text              >= 1.2.4 && < 1.3+    , time              >= 1.9.3 && < 1.10+    , bytestring        >= 0.10.10 && < 0.11+    , raw-strings-qq    >= 1.1 && < 1.2+  default-language:    Haskell2010++test-suite test+  default-language:   Haskell2010+  type:               exitcode-stdio-1.0+  hs-source-dirs:     test+  main-is:            Main.hs+  build-depends:+    pg-extras+    , base              >= 4.13.0 && < 4.14+    , text              >= 1.2.4 && < 1.3+    , HUnit             >= 1.6.1 && < 1.7
+ src/PGExtras.hs view
@@ -0,0 +1,347 @@+{-# LANGUAGE OverloadedStrings #-}++module PGExtras (+  extrasAllLocksRows,+  extrasAllLocks,+  extrasBloatRows,+  extrasBloat,+  extrasBlockingRows,+  extrasBlocking,+  extrasCacheHitRows,+  extrasCacheHit,+  extrasCallsRows,+  extrasCalls,+  extrasExtensionsRows,+  extrasExtensions,+  extrasIndexCacheHitRows,+  extrasIndexCacheHit,+  extrasIndexSizeRows,+  extrasIndexSize,+  extrasIndexUsageRows,+  extrasIndexUsage,+  extrasKillAllRows,+  extrasKillAll,+  extrasLocksRows,+  extrasLocks,+  extrasLongRunningQueriesRows,+  extrasLongRunningQueries,+  extrasMandelbrotRows,+  extrasMandelbrot,+  extrasRecordsRankRows,+  extrasRecordsRank,+  extrasSeqScansRows,+  extrasSeqScans,+  extrasTableCacheHitRows,+  extrasTableCacheHit,+  extrasTableIndexesSizeRows,+  extrasTableIndexesSize,+  extrasTableSizeRows,+  extrasTableSize,+  extrasTotalIndexSizeRows,+  extrasTotalIndexSize,+  extrasTotalTableSizeRows,+  extrasTotalTableSize,+  extrasUnusedIndexesRows,+  extrasUnusedIndexes,+  extrasVacuumStatsRows,+  extrasVacuumStats+) where++import Database.PostgreSQL.Simple+import qualified Data.ByteString.Char8 as Char8+import qualified Data.Text as Text+import Data.Time (ZonedTime)++import PGExtras.Queries.AllLocks (allLocksSQL, displayAllLocks)+import PGExtras.Queries.Bloat (bloatSQL, displayBloat)+import PGExtras.Queries.Blocking (blockingSQL, displayBlocking)+import PGExtras.Queries.CacheHit (cacheHitSQL, displayCacheHit)+import PGExtras.Queries.Calls (callsSQL, displayCalls)+import PGExtras.Queries.Extensions (extensionsSQL, displayExtensions)+import PGExtras.Queries.IndexCacheHit (indexCacheHitSQL, displayIndexCacheHit)+import PGExtras.Queries.IndexSize (indexSizeSQL, displayIndexSize)+import PGExtras.Queries.IndexUsage (indexUsageSQL, displayIndexUsage)+import PGExtras.Queries.KillAll (killAllSQL, displayKillAll)+import PGExtras.Queries.Locks (locksSQL, displayLocks)+import PGExtras.Queries.LongRunningQueries (longRunningQueriesSQL, displayLongRunningQueries)+import PGExtras.Queries.Mandelbrot (mandelbrotSQL, displayMandelbrot)+import PGExtras.Queries.RecordsRank (recordsRankSQL, displayRecordsRank)+import PGExtras.Queries.SeqScans (seqScansSQL, displaySeqScans)+import PGExtras.Queries.TableCacheHit (tableCacheHitSQL, displayTableCacheHit)+import PGExtras.Queries.TableIndexesSize (tableIndexesSizeSQL, displayTableIndexesSize)+import PGExtras.Queries.TableSize (tableSizeSQL, displayTableSize)+import PGExtras.Queries.TotalIndexSize (totalIndexSizeSQL, displayTotalIndexSize)+import PGExtras.Queries.TotalTableSize (totalTableSizeSQL, displayTotalTableSize)+import PGExtras.Queries.UnusedIndexes (unusedIndexesSQL, displayUnusedIndexes)+import PGExtras.Queries.VacuumStats (vacuumStatsSQL, displayVacuumStats)++-- AllLocks++extrasAllLocksRows :: [Char] -> IO [(Maybe Int, Maybe Text.Text, Maybe Text.Text, Maybe Bool, Maybe Text.Text, Maybe Text.Text, Maybe ZonedTime)]+extrasAllLocksRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn allLocksSQL++extrasAllLocks :: [Char] -> IO ()+extrasAllLocks databaseUrl = do+  rows <- extrasAllLocksRows databaseUrl+  displayAllLocks rows++-- Bloat++extrasBloatRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Rational, Maybe Text.Text)]+extrasBloatRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn bloatSQL++extrasBloat :: [Char] -> IO ()+extrasBloat databaseUrl = do+  rows <- extrasBloatRows databaseUrl+  displayBloat rows++-- Blocking++extrasBlockingRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)]+extrasBlockingRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn blockingSQL++extrasBlocking :: [Char] -> IO ()+extrasBlocking databaseUrl = do+  rows <- extrasBlockingRows databaseUrl+  displayBlocking rows++-- CacheHit++extrasCacheHitRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text)]+extrasCacheHitRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn cacheHitSQL++extrasCacheHit :: [Char] -> IO ()+extrasCacheHit databaseUrl = do+  rows <- extrasCacheHitRows databaseUrl+  displayCacheHit rows++-- Calls++extrasCallsRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)]+extrasCallsRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn callsSQL++extrasCalls :: [Char] -> IO ()+extrasCalls databaseUrl = do+  rows <- extrasCallsRows databaseUrl+  displayCalls rows++-- Extensions++extrasExtensionsRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)]+extrasExtensionsRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn extensionsSQL++extrasExtensions :: [Char] -> IO ()++extrasExtensions databaseUrl = do+  rows <- extrasExtensionsRows databaseUrl+  displayExtensions rows++-- IndexCacheHit++extrasIndexCacheHitRows :: [Char] -> IO [(Maybe Text.Text, Maybe Int, Maybe Int, Maybe Int, Maybe Text.Text)]+extrasIndexCacheHitRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn indexCacheHitSQL++extrasIndexCacheHit :: [Char] -> IO ()+extrasIndexCacheHit databaseUrl = do+  rows <- extrasIndexCacheHitRows databaseUrl+  displayIndexCacheHit rows++-- IndexSize++extrasIndexSizeRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text)]+extrasIndexSizeRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn indexSizeSQL++extrasIndexSize :: [Char] -> IO ()+extrasIndexSize databaseUrl = do+  rows <- extrasIndexSizeRows databaseUrl+  displayIndexSize rows++-- IndexUsage++extrasIndexUsageRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text, Maybe Int)]+extrasIndexUsageRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn indexUsageSQL++extrasIndexUsage :: [Char] -> IO ()+extrasIndexUsage databaseUrl = do+  rows <- extrasIndexUsageRows databaseUrl+  displayIndexUsage rows++-- KillAll++extrasKillAllRows :: [Char] -> IO [(Maybe Bool, Maybe Text.Text)]+extrasKillAllRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn killAllSQL++extrasKillAll :: [Char] -> IO ()+extrasKillAll databaseUrl = do+  rows <- extrasKillAllRows databaseUrl+  displayKillAll rows++-- Locks++extrasLocksRows :: [Char] -> IO [(Maybe Int, Maybe Text.Text, Maybe Text.Text, Maybe Bool, Maybe Text.Text, Maybe Text.Text, Maybe ZonedTime)]+extrasLocksRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn locksSQL++extrasLocks :: [Char] -> IO ()+extrasLocks databaseUrl = do+  rows <- extrasLocksRows databaseUrl+  displayLocks rows++-- LongRunningQueries++extrasLongRunningQueriesRows :: [Char] -> IO [(Maybe Int, Maybe ZonedTime, Maybe Text.Text)]+extrasLongRunningQueriesRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn longRunningQueriesSQL++extrasLongRunningQueries :: [Char] -> IO ()+extrasLongRunningQueries databaseUrl = do+  rows <- extrasLongRunningQueriesRows databaseUrl+  displayLongRunningQueries rows++-- Mandelbrot++extrasMandelbrotRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text)]+extrasMandelbrotRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn mandelbrotSQL++extrasMandelbrot :: [Char] -> IO ()+extrasMandelbrot databaseUrl = do+  rows <- extrasMandelbrotRows databaseUrl+  displayMandelbrot rows++-- RecordsRank++extrasRecordsRankRows :: [Char] -> IO [(Maybe Text.Text, Maybe Int)]+extrasRecordsRankRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn recordsRankSQL++extrasRecordsRank :: [Char] -> IO ()+extrasRecordsRank databaseUrl = do+  rows <- extrasRecordsRankRows databaseUrl+  displayRecordsRank rows++-- SeqScans++extrasSeqScansRows :: [Char] -> IO [(Maybe Text.Text, Maybe Int)]+extrasSeqScansRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn seqScansSQL++extrasSeqScans :: [Char] -> IO ()+extrasSeqScans databaseUrl = do+  rows <- extrasSeqScansRows databaseUrl+  displaySeqScans rows++-- TableCacheHit++extrasTableCacheHitRows :: [Char] -> IO [(Maybe Text.Text, Maybe Int, Maybe Int, Maybe Int, Maybe Text.Text)]+extrasTableCacheHitRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn tableCacheHitSQL++extrasTableCacheHit :: [Char] -> IO ()+extrasTableCacheHit databaseUrl = do+  rows <- extrasTableCacheHitRows databaseUrl+  displayTableCacheHit rows++-- TableIndexesSize++extrasTableIndexesSizeRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text)]+extrasTableIndexesSizeRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn tableIndexesSizeSQL++extrasTableIndexesSize :: [Char] -> IO ()+extrasTableIndexesSize databaseUrl = do+  rows <- extrasTableIndexesSizeRows databaseUrl+  displayTableIndexesSize rows++-- TableSize++extrasTableSizeRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text)]+extrasTableSizeRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn tableSizeSQL++extrasTableSize :: [Char] -> IO ()+extrasTableSize databaseUrl = do+  rows <- extrasTableSizeRows databaseUrl+  displayTableSize rows++-- TotalIndexSize++extrasTotalIndexSizeRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text)]+extrasTotalIndexSizeRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn totalIndexSizeSQL++extrasTotalIndexSize :: [Char] -> IO ()+extrasTotalIndexSize databaseUrl = do+  rows <- extrasTotalIndexSizeRows databaseUrl+  displayTotalIndexSize rows++-- TotalTableSize++extrasTotalTableSizeRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text)]+extrasTotalTableSizeRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn totalTableSizeSQL++extrasTotalTableSize :: [Char] -> IO ()+extrasTotalTableSize databaseUrl = do+  rows <- extrasTotalTableSizeRows databaseUrl+  displayTotalTableSize rows++-- UnusedIndexes++extrasUnusedIndexesRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Int)]+extrasUnusedIndexesRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn unusedIndexesSQL++extrasUnusedIndexes :: [Char] -> IO ()+extrasUnusedIndexes databaseUrl = do+  rows <- extrasUnusedIndexesRows databaseUrl+  displayUnusedIndexes rows++-- VacuumStats++extrasVacuumStatsRows :: [Char] -> IO [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)]+extrasVacuumStatsRows databaseUrl = do+  conn <- dbConnection databaseUrl+  query_ conn vacuumStatsSQL++extrasVacuumStats :: [Char] -> IO ()+extrasVacuumStats databaseUrl = do+  rows <- extrasVacuumStatsRows databaseUrl+  displayVacuumStats rows++-- Other++dbConnection :: [Char] -> IO Connection+dbConnection databaseUrl = do+  connectPostgreSQL $ Char8.pack databaseUrl
+ src/PGExtras/Helpers.hs view
@@ -0,0 +1,47 @@+module PGExtras.Helpers (+  maybeText,+  maybeInt,+  maybeRational,+  maybeBool,+  maybeZonedTime+) where+++import qualified Data.Text as Text+import Data.Time (ZonedTime)+import Data.Ratio++nullString :: [Char]+nullString = "NULL"++maybeInt :: Maybe Int -> [Char]+maybeInt Nothing = nullString+maybeInt (Just x) = show x++maybeRational :: Maybe Rational -> [Char]+maybeRational Nothing = nullString+maybeRational (Just x) = showRational x++maybeText :: Maybe Text.Text -> [Char]+maybeText Nothing = nullString+maybeText (Just x) = Text.unpack(x)++maybeBool :: Maybe Bool -> [Char]+maybeBool Nothing = nullString+maybeBool (Just x) = show x++maybeZonedTime :: Maybe ZonedTime -> [Char]+maybeZonedTime Nothing = nullString+maybeZonedTime (Just x) = show x++showRational :: Rational -> [Char]+showRational rat = (if num < 0 then "-" else "") ++ (shows d ("." ++ take 4 (go next)))+    where+        (d, next) = abs num `quotRem` den+        num = numerator rat+        den = denominator rat++        go 0 = ""+        go x = let (d, next) = (10 * x) `quotRem` den+               in shows d (go next)+
+ src/PGExtras/Queries/AllLocks.hs view
@@ -0,0 +1,42 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.AllLocks (allLocksSQL, displayAllLocks) where++import PGExtras.Helpers (maybeText, maybeInt, maybeBool, maybeZonedTime)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Data.Time (ZonedTime)+import Control.Monad (forM_)+import Data.List (intercalate)++allLocksSQL :: Query+allLocksSQL = [r|SELECT+  pg_stat_activity.pid,+  pg_class.relname,+  pg_locks.transactionid,+  pg_locks.granted,+  pg_locks.mode,+  pg_stat_activity.query AS query_snippet,+  pg_stat_activity.query_start+FROM pg_stat_activity,pg_locks left+OUTER JOIN pg_class+  ON (pg_locks.relation = pg_class.oid)+WHERE pg_stat_activity.query <> '<insufficient privilege>'+  AND pg_locks.pid = pg_stat_activity.pid+  AND pg_stat_activity.pid <> pg_backend_pid() order by query_start;|]++displayAllLocks :: [(Maybe Int, Maybe Text.Text, Maybe Text.Text, Maybe Bool, Maybe Text.Text, Maybe Text.Text, Maybe ZonedTime)] -> IO ()+displayAllLocks rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5, arg6, arg7) ->+    putStrLn $ maybeInt(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeBool(arg4) ++ " | " ++ maybeText(arg5) ++ " | " ++ maybeText(arg6) ++ " | " ++ maybeZonedTime(arg7)++description :: [Char]+description = "Queries with active locks"++tableHeaders :: [[Char]]+tableHeaders = ["procpid", "relname", "transactionid", "granted", "query_snippet", "mode", "query_start"]+
+ src/PGExtras/Queries/Bloat.hs view
@@ -0,0 +1,86 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.Bloat (bloatSQL, displayBloat) where++import PGExtras.Helpers (maybeText, maybeRational)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++bloatSQL :: Query+bloatSQL = [r|WITH constants AS (+  SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma+), bloat_info AS (+  SELECT+    ma,bs,schemaname,tablename,+    (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,+    (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2+  FROM (+    SELECT+      schemaname, tablename, hdr, ma, bs,+      SUM((1-null_frac)*avg_width) AS datawidth,+      MAX(null_frac) AS maxfracsum,+      hdr+(+        SELECT 1+count(*)/8+        FROM pg_stats s2+        WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename+      ) AS nullhdr+    FROM pg_stats s, constants+    GROUP BY 1,2,3,4,5+  ) AS foo+), table_bloat AS (+  SELECT+    schemaname, tablename, cc.relpages, bs,+    CEIL((cc.reltuples*((datahdr+ma-+      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta+  FROM bloat_info+  JOIN pg_class cc ON cc.relname = bloat_info.tablename+  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'+), index_bloat AS (+  SELECT+    schemaname, tablename, bs,+    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,+    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols+  FROM bloat_info+  JOIN pg_class cc ON cc.relname = bloat_info.tablename+  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'+  JOIN pg_index i ON indrelid = cc.oid+  JOIN pg_class c2 ON c2.oid = i.indexrelid+)+SELECT+  type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste+FROM+(SELECT+  'table' as type,+  schemaname,+  tablename as object_name,+  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,+  CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste+FROM+  table_bloat+    UNION+SELECT+  'index' as type,+  schemaname,+  tablename || '::' || iname as object_name,+  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,+  CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste+FROM+  index_bloat) bloat_summary+ORDER BY raw_waste DESC, bloat DESC;|]++displayBloat :: [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Rational, Maybe Text.Text)] -> IO ()+displayBloat rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeRational(arg4) ++ " | " ++ maybeText(arg5)++description :: [Char]+description = "Table and index bloat in your database ordered by most wasteful"++tableHeaders :: [[Char]]+tableHeaders = ["type", "schemaname", "object_name", "bloat", "waste"]
+ src/PGExtras/Queries/Blocking.hs view
@@ -0,0 +1,40 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.Blocking (blockingSQL, displayBlocking) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++blockingSQL :: Query+blockingSQL = [r|SELECT bl.pid AS blocked_pid,+  ka.query AS blocking_statement,+  now() - ka.query_start AS blocking_duration,+  kl.pid AS blocking_pid,+  a.query AS blocked_statement,+  now() - a.query_start AS blocked_duration+FROM pg_catalog.pg_locks bl+JOIN pg_catalog.pg_stat_activity a+  ON bl.pid = a.pid+JOIN pg_catalog.pg_locks kl+  JOIN pg_catalog.pg_stat_activity ka+    ON kl.pid = ka.pid+ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid+WHERE NOT bl.granted;|]++displayBlocking :: [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayBlocking rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5, arg6) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeText(arg4) ++ " | " ++ maybeText(arg5) ++ " | " ++ maybeText(arg6)++description :: [Char]+description = "Queries holding locks other queries are waiting to be released"++tableHeaders :: [[Char]]+tableHeaders = ["blocked_pid", "blocking_statement", "blocking_duration", "blocking_pid", "blocked_statement", "blocked_duration"]
+ src/PGExtras/Queries/CacheHit.hs view
@@ -0,0 +1,34 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.CacheHit (cacheHitSQL, displayCacheHit) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++cacheHitSQL :: Query+cacheHitSQL = [r|SELECT+'index hit rate' AS name,+(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio+FROM pg_statio_user_indexes+UNION ALL+SELECT 'table hit rate' AS name,+sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio+FROM pg_statio_user_tables;|]++displayCacheHit :: [(Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayCacheHit rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2)++description :: [Char]+description = "Index and table hit rate"++tableHeaders :: [[Char]]+tableHeaders = ["name", "ratio"]
+ src/PGExtras/Queries/Calls.hs view
@@ -0,0 +1,33 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.Calls (callsSQL, displayCalls) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++callsSQL :: Query+callsSQL = [r|SELECT query AS qry,+interval '1 millisecond' * total_time AS exec_time,+to_char((total_time/sum(total_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,+to_char(calls, 'FM999G999G990') AS ncalls,+interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time+FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)+ORDER BY calls DESC LIMIT 10;|]++displayCalls :: [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayCalls rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeText(arg4) ++ " | " ++ maybeText(arg5)++description :: [Char]+description = "10 queries that have highest frequency of execution"++tableHeaders :: [[Char]]+tableHeaders = ["qry", "exec_time", "prop_exec_time", "ncalls", "sync_io_time"]
+ src/PGExtras/Queries/Extensions.hs view
@@ -0,0 +1,25 @@+{-# LANGUAGE OverloadedStrings #-}++module PGExtras.Queries.Extensions (extensionsSQL, displayExtensions) where++import Database.PostgreSQL.Simple+import PGExtras.Helpers (maybeText)+import Control.Monad (forM_)+import qualified Data.Text as Text+import Data.List (intercalate)++extensionsSQL :: Query+extensionsSQL = "SELECT name, default_version, installed_version, comment FROM pg_available_extensions ORDER BY installed_version;"++displayExtensions :: [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayExtensions rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeText(arg4)++description :: [Char]+description = "Available and installed extensions"++tableHeaders :: [[Char]]+tableHeaders = ["name", "default_version", "installed_version", "comment"]
+ src/PGExtras/Queries/IndexCacheHit.hs view
@@ -0,0 +1,40 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.IndexCacheHit (indexCacheHitSQL, displayIndexCacheHit) where++import PGExtras.Helpers (maybeText, maybeInt)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++indexCacheHitSQL :: Query+indexCacheHitSQL = [r|SELECT+  relname AS name,+  idx_blks_hit AS buffer_hits,+  idx_blks_read AS block_reads,+  idx_blks_hit + idx_blks_read AS total_read,+  CASE (idx_blks_hit + idx_blks_read)::float+    WHEN 0 THEN 'Insufficient data'+    ELSE (idx_blks_hit / (idx_blks_hit + idx_blks_read)::float)::text+  END ratio+FROM+  pg_statio_user_tables+ORDER BY+  idx_blks_hit / (idx_blks_hit + idx_blks_read + 1)::float DESC;|]++displayIndexCacheHit :: [(Maybe Text.Text, Maybe Int, Maybe Int, Maybe Int, Maybe Text.Text)] -> IO ()+displayIndexCacheHit rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeInt(arg2) ++ " | " ++ maybeInt(arg3) ++ " | " ++ maybeInt(arg4) ++ " | " ++ maybeText(arg5)++description :: [Char]+description = "Calculates your cache hit rate for reading indexes"++tableHeaders :: [[Char]]+tableHeaders = ["name", "buffer_hits", "block_reads", "total_read", "ratio"]+
+ src/PGExtras/Queries/IndexSize.hs view
@@ -0,0 +1,35 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.IndexSize (indexSizeSQL, displayIndexSize) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++indexSizeSQL :: Query+indexSizeSQL = [r|SELECT c.relname AS name,+  pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size+FROM pg_class c+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)+WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')+AND n.nspname !~ '^pg_toast'+AND c.relkind='i'+GROUP BY c.relname+ORDER BY sum(c.relpages) DESC;|]++displayIndexSize :: [(Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayIndexSize rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2)++description :: [Char]+description = "The size of indexes, descending by size"++tableHeaders :: [[Char]]+tableHeaders = ["name", "ratio"]
+ src/PGExtras/Queries/IndexUsage.hs view
@@ -0,0 +1,37 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.IndexUsage (indexUsageSQL, displayIndexUsage) where++import PGExtras.Helpers (maybeText, maybeInt)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++indexUsageSQL :: Query+indexUsageSQL = [r|SELECT relname,+   CASE idx_scan+     WHEN 0 THEN 'Insufficient data'+     ELSE (100 * idx_scan / (seq_scan + idx_scan))::text+   END percent_of_times_index_used,+   n_live_tup rows_in_table+ FROM+   pg_stat_user_tables+ ORDER BY+   n_live_tup DESC;|]++displayIndexUsage :: [(Maybe Text.Text, Maybe Text.Text, Maybe Int)] -> IO ()+displayIndexUsage rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2)++ " | " ++ maybeInt(arg3)++description :: [Char]+description = "Index hit rate (effective databases are at 99% and up)"++tableHeaders :: [[Char]]+tableHeaders = ["relname", "percent_of_times_index_used", "rows_in_table"]+
+ src/PGExtras/Queries/KillAll.hs view
@@ -0,0 +1,31 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.KillAll (killAllSQL, displayKillAll) where++import PGExtras.Helpers (maybeBool)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++killAllSQL :: Query+killAllSQL = [r|SELECT pg_terminate_backend(pid), 't' as t FROM pg_stat_activity+  WHERE pid <> pg_backend_pid()+  AND query <> '<insufficient privilege>'+  AND datname = current_database();|]++displayKillAll :: [(Maybe Bool, Maybe Text.Text)] -> IO ()+displayKillAll rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, _) ->+    putStrLn $ maybeBool(arg1)++description :: [Char]+description = "Kill all the active database connections"++tableHeaders :: [[Char]]+tableHeaders = ["success"]+
+ src/PGExtras/Queries/Locks.hs view
@@ -0,0 +1,42 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.Locks (locksSQL, displayLocks) where++import PGExtras.Helpers (maybeText, maybeInt, maybeBool, maybeZonedTime)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Data.Time (ZonedTime)+import Control.Monad (forM_)+import Data.List (intercalate)++locksSQL :: Query+locksSQL = [r|SELECT+  pg_stat_activity.pid,+  pg_class.relname,+  pg_locks.transactionid,+  pg_locks.granted,+  pg_locks.mode,+  pg_stat_activity.query AS query_snippet,+  pg_stat_activity.query_start+FROM pg_stat_activity,pg_locks left+OUTER JOIN pg_class+  ON (pg_locks.relation = pg_class.oid)+WHERE pg_stat_activity.query <> '<insufficient privilege>'+  AND pg_locks.pid = pg_stat_activity.pid+  AND pg_locks.mode IN ('ExclusiveLock', 'AccessExclusiveLock', 'RowExclusiveLock')+  AND pg_stat_activity.pid <> pg_backend_pid() order by query_start;|]++displayLocks :: [(Maybe Int, Maybe Text.Text, Maybe Text.Text, Maybe Bool, Maybe Text.Text, Maybe Text.Text, Maybe ZonedTime)] -> IO ()+displayLocks rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5, arg6, arg7) ->+    putStrLn $ maybeInt(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeBool(arg4) ++ " | " ++ maybeText(arg5) ++ " | " ++ maybeText(arg6) ++ " | " ++ maybeZonedTime(arg7)++description :: [Char]+description = "Queries with active exclusive locks"++tableHeaders :: [[Char]]+tableHeaders = ["procpid", "relname", "transactionid", "granted", "query_snippet", "mode", "query_start"]
+ src/PGExtras/Queries/LongRunningQueries.hs view
@@ -0,0 +1,40 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.LongRunningQueries (longRunningQueriesSQL, displayLongRunningQueries) where++import PGExtras.Helpers (maybeInt, maybeText, maybeZonedTime)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)+import Data.Time (ZonedTime)++longRunningQueriesSQL :: Query+longRunningQueriesSQL = [r|SELECT+  pid,+  pg_stat_activity.query_start,+  query AS query+FROM+  pg_stat_activity+WHERE+  pg_stat_activity.query <> ''::text+  AND state <> 'idle'+  AND now() - pg_stat_activity.query_start > interval '5 minutes'+ORDER BY+  now() - pg_stat_activity.query_start DESC;|]++displayLongRunningQueries :: [(Maybe Int, Maybe ZonedTime, Maybe Text.Text)] -> IO ()+displayLongRunningQueries rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3) ->+    putStrLn $ maybeInt(arg1) ++ " | " ++ maybeZonedTime(arg2) ++ " | " ++ maybeText(arg3)++description :: [Char]+description = "All queries longer than five minutes by descending duration"++tableHeaders :: [[Char]]+tableHeaders = ["pid", "query_start", "query"]+
+ src/PGExtras/Queries/Mandelbrot.hs view
@@ -0,0 +1,46 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.Mandelbrot (mandelbrotSQL, displayMandelbrot) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++mandelbrotSQL :: Query+mandelbrotSQL = [r|WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (++    SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0+    FROM (select -2.2 + 0.031 * i, i from generate_series(0,101) as i) as xgen(x,ix),+         (select -1.5 + 0.031 * i, i from generate_series(0,101) as i) as ygen(y,iy)+    UNION ALL+    SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * 2 + CY, I + 1+    FROM Z+    WHERE X * X + Y * Y < 16::float+    AND I < 100+    )+SELECT array_to_string(array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@#### ', LEAST(GREATEST(I,1),27), 1)),''), 't' as t+FROM (+      SELECT IX, IY, MAX(I) AS I+      FROM Z+      GROUP BY IY, IX+      ORDER BY IY, IX+     ) AS ZT+GROUP BY IY+ORDER BY IY;|]++displayMandelbrot :: [(Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayMandelbrot rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, _) ->+    putStrLn $ maybeText(arg1)++description :: [Char]+description = "The mandelbrot set"++tableHeaders :: [[Char]]+tableHeaders = ["name", "ratio"]
+ src/PGExtras/Queries/RecordsRank.hs view
@@ -0,0 +1,33 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.RecordsRank (recordsRankSQL, displayRecordsRank) where++import PGExtras.Helpers (maybeText, maybeInt)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++recordsRankSQL :: Query+recordsRankSQL = [r|SELECT+  relname AS name,+  n_live_tup AS estimated_count+FROM+  pg_stat_user_tables+ORDER BY+  n_live_tup DESC;|]++displayRecordsRank :: [(Maybe Text.Text, Maybe Int)] -> IO ()+displayRecordsRank rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeInt(arg2)++description :: [Char]+description = "All tables and the number of rows in each ordered by number of rows descending"++tableHeaders :: [[Char]]+tableHeaders = ["name", "estimated_count"]
+ src/PGExtras/Queries/SeqScans.hs view
@@ -0,0 +1,32 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.SeqScans (seqScansSQL, displaySeqScans) where++import PGExtras.Helpers (maybeText, maybeInt)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++seqScansSQL :: Query+seqScansSQL = [r|SELECT+  relname AS name,+  seq_scan as count+FROM+  pg_stat_user_tables+ORDER BY seq_scan DESC;|]++displaySeqScans :: [(Maybe Text.Text, Maybe Int)] -> IO ()+displaySeqScans rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeInt(arg2)++description :: [Char]+description = "Count of sequential scans by table descending by order"++tableHeaders :: [[Char]]+tableHeaders = ["name", "count"]
+ src/PGExtras/Queries/TableCacheHit.hs view
@@ -0,0 +1,39 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.TableCacheHit (tableCacheHitSQL, displayTableCacheHit) where++import PGExtras.Helpers (maybeText, maybeInt)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++tableCacheHitSQL :: Query+tableCacheHitSQL = [r|SELECT+  relname AS name,+  heap_blks_hit AS buffer_hits,+  heap_blks_read AS block_reads,+  heap_blks_hit + heap_blks_read AS total_read,+  CASE (heap_blks_hit + heap_blks_read)::float+    WHEN 0 THEN 'Insufficient data'+    ELSE (heap_blks_hit / (heap_blks_hit + heap_blks_read)::float)::text+  END ratio+FROM+  pg_statio_user_tables+ORDER BY+  heap_blks_hit / (heap_blks_hit + heap_blks_read + 1)::float DESC;|]++displayTableCacheHit :: [(Maybe Text.Text, Maybe Int, Maybe Int, Maybe Int, Maybe Text.Text)] -> IO ()+displayTableCacheHit rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeInt(arg2) ++ " | " ++ maybeInt(arg3) ++ " | " ++ maybeInt(arg4) ++ " | " ++ maybeText(arg5)++description :: [Char]+description = "Calculates your cache hit rate for reading tables"++tableHeaders :: [[Char]]+tableHeaders = ["name", "buffer_hits", "block_reads", "total_read", "ratio"]
+ src/PGExtras/Queries/TableIndexesSize.hs view
@@ -0,0 +1,34 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.TableIndexesSize (tableIndexesSizeSQL, displayTableIndexesSize) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++tableIndexesSizeSQL :: Query+tableIndexesSizeSQL = [r|SELECT c.relname AS table,+  pg_size_pretty(pg_indexes_size(c.oid)) AS index_size+FROM pg_class c+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)+WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')+AND n.nspname !~ '^pg_toast'+AND c.relkind IN ('r', 'm')+ORDER BY pg_indexes_size(c.oid) DESC;|]++displayTableIndexesSize :: [(Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayTableIndexesSize rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2)++description :: [Char]+description = "Total size of all the indexes on each table, descending by size"++tableHeaders :: [[Char]]+tableHeaders = ["name", "indexes_size"]
+ src/PGExtras/Queries/TableSize.hs view
@@ -0,0 +1,34 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.TableSize (tableSizeSQL, displayTableSize) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++tableSizeSQL :: Query+tableSizeSQL = [r|SELECT c.relname AS name,+  pg_size_pretty(pg_table_size(c.oid)) AS size+FROM pg_class c+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)+WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')+AND n.nspname !~ '^pg_toast'+AND c.relkind IN ('r', 'm')+ORDER BY pg_table_size(c.oid) DESC;|]++displayTableSize :: [(Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayTableSize rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2)++description :: [Char]+description = "Size of the tables (excluding indexes), descending by size"++tableHeaders :: [[Char]]+tableHeaders = ["name", "size"]
+ src/PGExtras/Queries/TotalIndexSize.hs view
@@ -0,0 +1,33 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.TotalIndexSize (totalIndexSizeSQL, displayTotalIndexSize) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++totalIndexSizeSQL :: Query+totalIndexSizeSQL = [r|SELECT pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size,+  't' as t+FROM pg_class c+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)+WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')+AND n.nspname !~ '^pg_toast'+AND c.relkind='i';|]++displayTotalIndexSize :: [(Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayTotalIndexSize rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, _) ->+    putStrLn $ maybeText(arg1)++description :: [Char]+description = "Total size of all indexes in MB"++tableHeaders :: [[Char]]+tableHeaders = ["size"]
+ src/PGExtras/Queries/TotalTableSize.hs view
@@ -0,0 +1,34 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.TotalTableSize (totalTableSizeSQL, displayTotalTableSize) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++totalTableSizeSQL :: Query+totalTableSizeSQL = [r|SELECT c.relname AS name,+  pg_size_pretty(pg_total_relation_size(c.oid)) AS size+FROM pg_class c+LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)+WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')+AND n.nspname !~ '^pg_toast'+AND c.relkind IN ('r', 'm')+ORDER BY pg_total_relation_size(c.oid) DESC;|]++displayTotalTableSize :: [(Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayTotalTableSize rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2)++description :: [Char]+description = "Size of the tables (including indexes), descending by size"++tableHeaders :: [[Char]]+tableHeaders = ["name", "size"]
+ src/PGExtras/Queries/UnusedIndexes.hs view
@@ -0,0 +1,36 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.UnusedIndexes (unusedIndexesSQL, displayUnusedIndexes) where++import PGExtras.Helpers (maybeText, maybeInt)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++unusedIndexesSQL :: Query+unusedIndexesSQL = [r|SELECT+  schemaname || '.' || relname AS table,+  indexrelname AS index,+  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,+  idx_scan as index_scans+FROM pg_stat_user_indexes ui+JOIN pg_index i ON ui.indexrelid = i.indexrelid+WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192+ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,+pg_relation_size(i.indexrelid) DESC;|]++displayUnusedIndexes :: [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Int)] -> IO ()+displayUnusedIndexes rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeInt(arg4)++description :: [Char]+description = "Unused and almost unused indexes"++tableHeaders :: [[Char]]+tableHeaders = ["name", "ratio"]
+ src/PGExtras/Queries/VacuumStats.hs view
@@ -0,0 +1,65 @@+{-# LANGUAGE OverloadedStrings #-}+{-# LANGUAGE QuasiQuotes #-}++module PGExtras.Queries.VacuumStats (vacuumStatsSQL, displayVacuumStats) where++import PGExtras.Helpers (maybeText)+import Database.PostgreSQL.Simple+import Text.RawString.QQ+import qualified Data.Text as Text+import Control.Monad (forM_)+import Data.List (intercalate)++vacuumStatsSQL :: Query+vacuumStatsSQL = [r|WITH table_opts AS (+  SELECT+    pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts+  FROM+     pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid+), vacuum_settings AS (+  SELECT+    oid, relname, nspname,+    CASE+      WHEN relopts LIKE '%autovacuum_vacuum_threshold%'+        THEN substring(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*')::integer+        ELSE current_setting('autovacuum_vacuum_threshold')::integer+      END AS autovacuum_vacuum_threshold,+    CASE+      WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%'+        THEN substring(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*')::real+        ELSE current_setting('autovacuum_vacuum_scale_factor')::real+      END AS autovacuum_vacuum_scale_factor+  FROM+    table_opts+)+SELECT+  vacuum_settings.nspname AS schema,+  vacuum_settings.relname AS table,+  to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') AS last_vacuum,+  to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,+  to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,+  to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,+  to_char(autovacuum_vacuum_threshold+       + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold,+  CASE+    WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup+    THEN 'yes'+  END AS expect_autovacuum+FROM+  pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid+    INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid+ORDER BY 1;|]++displayVacuumStats :: [(Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text, Maybe Text.Text)] -> IO ()+displayVacuumStats rows = do+  putStrLn $ description+  putStrLn $ intercalate " | " tableHeaders+  forM_ rows $ \(arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8) ->+    putStrLn $ maybeText(arg1) ++ " | " ++ maybeText(arg2) ++ " | " ++ maybeText(arg3) ++ " | " ++ maybeText(arg4) ++ " | " ++ maybeText(arg5) ++ " | " ++ maybeText(arg6) ++ " | " ++ maybeText(arg7) ++ " | " ++ maybeText(arg8)++description :: [Char]+description = "Dead rows and whether an automatic vacuum is expected to be triggered"++tableHeaders :: [[Char]]+tableHeaders = ["schema", "table", "last_vacuum", "last_autovacuum", "rowcount", "dead_rowcount", "autovacuum_threshold", "expect_autovacuum"]+
+ test/Main.hs view
@@ -0,0 +1,161 @@+module Main where++import Test.HUnit+import System.Exit (exitFailure, exitSuccess)++import PGExtras (+  extrasAllLocksRows,+  extrasBloatRows,+  extrasBlockingRows,+  extrasCacheHitRows,+  extrasExtensionsRows,+  extrasIndexCacheHitRows,+  extrasIndexSizeRows,+  extrasIndexUsageRows,+  extrasKillAllRows,+  extrasLocksRows,+  extrasLongRunningQueriesRows,+  extrasMandelbrotRows,+  extrasRecordsRankRows,+  extrasSeqScansRows,+  extrasTableCacheHitRows,+  extrasTableIndexesSizeRows,+  extrasTableSizeRows,+  extrasTotalIndexSizeRows,+  extrasTotalTableSizeRows,+  extrasUnusedIndexesRows,+  extrasVacuumStatsRows)++databaseUrl :: [Char]+databaseUrl = "postgres://postgres:secret@localhost:5432/haskell-pg-extras-test"++testAllLocks :: Test+testAllLocks = TestCase ( do+  rows <- extrasAllLocksRows databaseUrl+  assertBool "AllLocks rows present" ((length rows) >= 0))++testBloat :: Test+testBloat = TestCase ( do+  rows <- extrasBloatRows databaseUrl+  assertBool "Bloat rows present" ((length rows) >= 0))++testBlocking :: Test+testBlocking = TestCase ( do+  rows <- extrasBlockingRows databaseUrl+  assertBool "Blocking rows present" ((length rows) >= 0))++testCacheHit :: Test+testCacheHit = TestCase ( do+  rows <- extrasCacheHitRows databaseUrl+  assertBool "CacheHit rows present" ((length rows) >= 0))++testExtensions :: Test+testExtensions = TestCase ( do+  rows <- extrasExtensionsRows databaseUrl+  assertBool "Extensions rows present" ((length rows) >= 0))++testIndexCacheHit :: Test+testIndexCacheHit = TestCase ( do+  rows <- extrasIndexCacheHitRows databaseUrl+  assertBool "IndexCacheHit rows present" ((length rows) >= 0))++testIndexSizeRows :: Test+testIndexSizeRows = TestCase ( do+  rows <- extrasIndexSizeRows databaseUrl+  assertBool "IndexSize rows present" ((length rows) >= 0))++testIndexUsageRows :: Test+testIndexUsageRows = TestCase ( do+  rows <- extrasIndexUsageRows databaseUrl+  assertBool "IndexUsage rows present" ((length rows) >= 0))++testKillAll :: Test+testKillAll = TestCase ( do+  rows <- extrasKillAllRows databaseUrl+  assertBool "KillAll rows present" ((length rows) >= 0))++testLocks :: Test+testLocks = TestCase ( do+  rows <- extrasLocksRows databaseUrl+  assertBool "Locks rows present" ((length rows) >= 0))++testLongRunningQueries :: Test+testLongRunningQueries = TestCase ( do+  rows <- extrasLongRunningQueriesRows databaseUrl+  assertBool "LongRunningQueries rows present" ((length rows) >= 0))++testMandelbrot :: Test+testMandelbrot = TestCase ( do+  rows <- extrasMandelbrotRows databaseUrl+  assertBool "Mandelbrot rows present" ((length rows) >= 0))++testRecordsRank :: Test+testRecordsRank = TestCase ( do+  rows <- extrasRecordsRankRows databaseUrl+  assertBool "RecordsRank rows present" ((length rows) >= 0))++testSeqScans :: Test+testSeqScans = TestCase ( do+  rows <- extrasSeqScansRows databaseUrl+  assertBool "SeqScans rows present" ((length rows) >= 0))++testIndexesSize :: Test+testIndexesSize = TestCase ( do+  rows <- extrasTableIndexesSizeRows databaseUrl+  assertBool "TableIndexesSize rows present" ((length rows) >= 0))++testTableSize :: Test+testTableSize = TestCase ( do+  rows <- extrasTableSizeRows databaseUrl+  assertBool "TableSize rows present" ((length rows) >= 0))++testTotalIndexSize :: Test+testTotalIndexSize = TestCase ( do+  rows <- extrasTotalIndexSizeRows databaseUrl+  assertBool "TotalIndexSize rows present" ((length rows) >= 0))++testTotalTableSize :: Test+testTotalTableSize = TestCase ( do+  rows <- extrasTotalTableSizeRows databaseUrl+  assertBool "TotalTableSize rows present" ((length rows) >= 0))++testUnusedIndexes :: Test+testUnusedIndexes = TestCase ( do+  rows <- extrasUnusedIndexesRows databaseUrl+  assertBool "UnusedIndexes rows present" ((length rows) >= 0))++testVacuumStats :: Test+testVacuumStats = TestCase ( do+  rows <- extrasVacuumStatsRows databaseUrl+  assertBool "VacuumStats rows present" ((length rows) >= 0))++tests = TestList [+  TestLabel "" testAllLocks,+  TestLabel "" testBloat,+  TestLabel "" testBlocking,+  TestLabel "" testCacheHit,+  TestLabel "" testExtensions,+  TestLabel "" testIndexCacheHit,+  TestLabel "" testIndexSizeRows,+  TestLabel "" testIndexUsageRows,+  TestLabel "" testKillAll,+  TestLabel "" testLocks,+  TestLabel "" testLongRunningQueries,+  TestLabel "" testMandelbrot,+  TestLabel "" testRecordsRank,+  TestLabel "" testSeqScans,+  TestLabel "" testIndexesSize,+  TestLabel "" testTableSize,+  TestLabel "" testTotalIndexSize,+  TestLabel "" testTotalTableSize,+  TestLabel "" testUnusedIndexes,+  TestLabel "" testVacuumStats]++main :: IO ()+main = do+    counts2 <- runTestTT (test [+            tests+            ])+    if (errors counts2 + failures counts2 == 0)+        then exitSuccess+        else exitFailure