mfvanek / pg-index-health-sql Goto Github PK
View Code? Open in Web Editor NEWpg-index-health-sql is a set of sql-queries for analyzing and maintaining indexes and tables health in Postgresql databases.
License: Apache License 2.0
pg-index-health-sql is a set of sql-queries for analyzing and maintaining indexes and tables health in Postgresql databases.
License: Apache License 2.0
So that you can understand what this script is intended for
Need to add order by
clause to the query
Relates to mfvanek/pg-index-health#362
Need to compare current version of sql query with version by Kirill Borovikov from https://habr.com/ru/companies/tensor/articles/488104/
WITH sch AS (
SELECT
'public'::text sch -- schema
)
, def AS (
SELECT
clr.relname nmt
, cli.relname nmi
, pg_get_indexdef(cli.oid) def
, cli.oid clioid
, clr
, cli
, idx
, (
SELECT
array_agg(T::text ORDER BY f.i)
FROM
(
SELECT
clr.oid rel
, i
, idx.indkey[i] ik
FROM
generate_subscripts(idx.indkey, 1) i
) f
JOIN
pg_attribute T
ON (T.attrelid, T.attnum) = (f.rel, f.ik)
) fld$
FROM
pg_class clr
JOIN
pg_index idx
ON idx.indrelid = clr.oid
JOIN
pg_class cli
ON cli.oid = idx.indexrelid
JOIN
pg_namespace nsp
ON nsp.oid = cli.relnamespace AND
nsp.nspname = (TABLE sch)
WHERE
NOT idx.indisunique AND
idx.indisready AND
idx.indisvalid AND
cli.relam = (
SELECT
oid
FROM
pg_am
WHERE
amname = 'btree'
LIMIT 1
)
ORDER BY
clr.relname, cli.relname
)
, fld AS (
SELECT
*
, ARRAY(
SELECT
(att::pg_attribute).attname
FROM
unnest(fld$) att
) nmf$
, ARRAY(
SELECT
(
SELECT
typname
FROM
pg_type
WHERE
oid = (att::pg_attribute).atttypid
)
FROM
unnest(fld$) att
) tpf$
FROM
def
)
SELECT
nmt
, nmi
, nmf$
, tpf$
, def
FROM
fld
WHERE
tpf$ && ARRAY(
SELECT
typname
FROM
pg_type
WHERE
typname ~ '^_'
)
ORDER BY
1, 2;
explain(analyze, buffers)
Здравствуйте, подскажите пожалуйста, что означает эта строка?
Она присутствует в каждом коде проверки.
WHERE pn.nspname = :schema_name_param::text
Работаю через pgAdmin, и при наличии этой строки, выдает ошибку:
"ERROR: syntax error at or near ":"
LINE 13: psai.schemaname = :schema_name_param::text and"
Без этой строки, всё работает, но возможно не корректно.
See also another queries
table_bloat.txt
index_bloat.txt
It looks like indexes bloat estimation query doesn't work properly on PG 13.
Hi,
You've sourced it under GPL which effectively prevents other libraries from using it under anything rather than GPL. That can lead to distibution problems.
I'd like to use those SQLs in BSD licensed project.
Is it possible to double license those SQLs, or maybe switch altogether to a more permissive license?
Maybe add something like
SELECT (total_time / 1000 / 60) as total, (total_time / calls) as avg, query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 5;
to get stats on query perfomance. It's seems to be partly related to index health.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.