View overall_status_of_indexes.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
COUNT(*) FILTER (WHERE idx_scan = 0) AS no_hit, | |
COUNT(*) FILTER (WHERE idx_scan > 0) AS used, | |
COUNT(*) AS total | |
FROM | |
pg_stat_user_indexes s | |
JOIN pg_index i ON (i.indexrelid = s.indexrelid) | |
WHERE | |
NOT i.indisprimary; |
View find_contraint_ref_fields.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH constraint_ref_fields AS ( | |
SELECT | |
unnest(con1.conkey) AS local_key, | |
unnest(con1.confkey) AS ref_key, | |
con1.conrelid, | |
con1.confrelid | |
FROM | |
pg_class cl | |
JOIN pg_namespace ns ON cl.relnamespace = ns.oid | |
JOIN pg_constraint con1 ON con1.conrelid = cl.oid |
View DataCopyManager.java
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package br.com.michelmilezzi.database.data; | |
import java.io.File; | |
import java.io.FileOutputStream; | |
import java.io.IOException; | |
import java.sql.Connection; | |
import java.sql.SQLException; | |
import java.util.zip.ZipEntry; | |
import java.util.zip.ZipOutputStream; |
View parameter_bind.rb
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Usage: ruby parameter_bind.rb mylogfile.log | |
# Output: every sql command parsed with its parameters | |
sourceFileName = ARGV[0] | |
targetFile = File.open("#{sourceFileName}.sql", 'w') | |
lines = File.read(sourceFileName, encoding: 'utf-8').scan(/(duration:.*)\sexecute.+:\s(.+)(\n.+parameters:(.+))?/) | |
lines.each do |line| | |
if line[2] != nil then |
View index_without_hit.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
relid::regclass AS table, | |
indexrelid::regclass AS index, | |
pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, | |
idx_tup_read, | |
idx_tup_fetch, | |
idx_scan | |
FROM | |
pg_stat_user_indexes | |
JOIN pg_index USING (indexrelid) |
View aws_glue_avoiding_duplicates.py
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import sys | |
import pydevd | |
from awsglue.transforms import * | |
from awsglue.utils import getResolvedOptions | |
from pyspark.context import SparkContext | |
from awsglue.context import GlueContext | |
from awsglue.dynamicframe import DynamicFrame | |
from pyspark.sql.functions import col | |
from awsglue.job import Job |
View idempotent_script.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Table | |
CREATE TABLE IF NOT EXISTS person ( | |
id integer NOT NULL, | |
person_name character varying(40) NOT NULL, | |
updated_date date, | |
CONSTRAINT person_pkey PRIMARY KEY (id) | |
); | |
--Index | |
CREATE INDEX IF NOT EXISTS idx_person_name ON person (person_name); |