-
-
Save rgreenjr/3637525 to your computer and use it in GitHub Desktop.
-- show running queries (pre 9.2) | |
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query | |
FROM pg_stat_activity | |
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' | |
ORDER BY query_start desc; | |
-- show running queries (9.2) | |
SELECT pid, age(clock_timestamp(), query_start), usename, query | |
FROM pg_stat_activity | |
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' | |
ORDER BY query_start desc; | |
-- kill running query | |
SELECT pg_cancel_backend(procpid); | |
-- kill idle query | |
SELECT pg_terminate_backend(procpid); | |
-- vacuum command | |
VACUUM (VERBOSE, ANALYZE); | |
-- all database users | |
select * from pg_stat_activity where current_query not like '<%'; | |
-- all databases and their sizes | |
select * from pg_user; | |
-- all tables and their size, with/without indexes | |
select datname, pg_size_pretty(pg_database_size(datname)) | |
from pg_database | |
order by pg_database_size(datname) desc; | |
-- cache hit rates (should not be less than 0.99) | |
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio | |
FROM pg_statio_user_tables; | |
-- table index usage rates (should not be less than 0.99) | |
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table | |
FROM pg_stat_user_tables | |
ORDER BY n_live_tup DESC; | |
-- how many indexes are in cache | |
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio | |
FROM pg_statio_user_indexes; | |
-- Dump database on remote host to file | |
$ pg_dump -U username -h hostname databasename > dump.sql | |
-- Import dump into existing database | |
$ psql -d newdb -f dump.sql |
Suppose I have a function with a parameter and is called from any backend language. How can I know what parameter is sent in PostgreSQL?
pg_stat_activity is only giving me what query is run. How do I know which value in parameter is used in the PostgreSQL function?Please help me?
Thanks in advance.same thinks can be easily done with SQL profiler in the SQL server.
Image attached below explaining what I like to know?
@Diwas777, I am facing same request, did you get a solution to get parameter values? Thanks~!
Hi
Can anybody provide me the query to fetch of CPU utilization for each query. From pg_stat_activity I can get the pid and from top or ps I can get the CPU utilization. I want one query to fetch the details
Hey Ron, small world.....I was looking for a few postgres queries and you popped up to the top of list!
You guys might also be interested in my SQL-scripts repo which contain lots of SQL scripts for PostgreSQL and MySQL tested across all versions of PostgreSQL / MySQL / MariaDB - with fully documented headers showing version support and variations to account for version differences:
https://github.com/HariSekhon/SQL-scripts
There are also Google BigQuery SQL queries in there, as well as AWS Athena CloudTrail logs integration DDL, and I have Snowflake queries pending adding there too.
Additionally, in my adjacent DevOps Bash tools repo I've put my advanced .psqlrc
and written scripts for one-touch test environments that boot any version of PostgreSQL, MySQL and MariaDB available in docker and drop you straight in to a psql
/ mysql
shell with all the SQL scripts available in $PWD
for fast easy testing and sourcing, as well as scripts to test any SQL script(s) against a list of versions of PostgreSQL / MySQL / MariaDB to determine its compatibility across versions (I used this to determine the version compatibility for every interesting SQL query which is documented in the headers in each SQL script)
How to find list of tables,which have not been accessed and their last accessed time
Seems like the "[IDLE]" syntax is deprecated, perhaps this is better for active queries on modern Postgres:
SELECT age(clock_timestamp(), query_start), *
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
Very Usefull, Sadly I allways come looking for this one:
select pid, pg_blocking_pids(pid) as blocked_by, query as blocked_query
from pg_stat_activity
where pg_blocking_pids(pid)::text != '{}';
Great post, but outdated, I found myself creating my own version of the scripts often, so I decided to make a gist:
https://gist.github.com/mencargo/79447185034ebabcb49087008fbdc266
@mencargo did you see my SQL-scripts repo mentioned above? I have different versions of these SQL queries for different versions of PostgreSQL / MySQL / MariaDB which are programmatically tested on all the different versions.
@mencargo did you see my SQL-scripts repo mentioned above? I have different versions of these SQL queries for different versions of PostgreSQL / MySQL / MariaDB which are programmatically tested on all the different versions.
Yep, great collection, a bit advanced for my level.
Mine is just a single page, basic but useful stuff to have at hand, focused only on recent PostgreSQL.
If you need SQL queries that work on a specific DB version they're all tested and documented in the headers of each script there all the way up to recent Postgres 13.
They're also mostly PostgreSQL because there are just so many useful queries and interesting things exposed to query in postgres SQL.
Possible division by zero when scanning index usage rates, fix:
CASE WHEN (seq_scan + idx_scan) != 0 THEN 100.0 * idx_scan / (seq_scan + idx_scan) ELSE 0 END AS percent_of_times_index_used,
I usually use x/NULLIF(possible0, 0) for this. Whether MSSQL or PG, etc.
Possible division by zero when scanning index usage rates, fix:
CASE WHEN (seq_scan + idx_scan) != 0 THEN 100.0 * idx_scan / (seq_scan + idx_scan) ELSE 0 END AS percent_of_times_index_used,
I usually use x/NULLIF(possible0, 0) for this. Whether MSSQL or PG, etc.
That case statement pre-checks for zero and only runs that division if it's non-zero.
Checking my scripts, I've done it like this, which I think is more concise:
100 * idx_scan / GREATEST(seq_scan + idx_scan, 1)
taken from:
https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_tables_index_usage.sql
top 10 tables with their size:
select schemaname as table_schema,
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
pg_size_pretty(pg_relation_size(relid)) as data_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
pg_relation_size(relid) desc
limit 10;
-- show running queries (11+)
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
@tychodaimon that query almost identical to one I have here which is currently tested on PostgreSQL 9.2 - 13.0:
https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_running_queries.sql
@HariSekhon You right, i not sure from which version it started, I get it on 11, 12 and also on 13, but condition query != '<IDLE>'
not filter idle queries, only state != 'idle'
give you list filtered from idle. (sorry I was thinking that You are author of this page)
@rgreenjr And thanks for this page! I use Your queries for many years, today I put comment because just tired to fix condition after copy and paste all the time
@tychodaimon yes I used state != 'idle'
in that query too.
I documented my scripts with the versions of Postgres they work on.
You can then copy/paste or source from live psql
prompt for the version of Postgres you're running.
The default scripts work on the latest versions of Postgres while the version suffixed scripts are maintained for older versions.
I also have other scripts there to run the latest scripts all on new versions to check if anything has changed/broken.
@HariSekhon Thank you very much. You did a great job! I am studying your repository now. I commented here because this is the page that google gives me for "postgres running queries" which I am used to
a slightly neater way of presenting the query above..