Skip to content

Instantly share code, notes, and snippets.

Avatar

Michel Luiz Milezzi michelmilezzi

View GitHub Profile
@michelmilezzi
michelmilezzi / overall_status_of_indexes.sql
Last active Jun 26, 2017
PostgreSQL query that calculates overall status of indexes
View overall_status_of_indexes.sql
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;
@michelmilezzi
michelmilezzi / index_without_hit.sql
Last active Jun 26, 2017
PostgreSQL query that finds indexes without hits
View index_without_hit.sql
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)
@michelmilezzi
michelmilezzi / find_contraint_ref_fields.sql
Last active Jun 26, 2017
PostgreSQL query that finds columns and referenced columns from a constraint
View find_contraint_ref_fields.sql
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
@michelmilezzi
michelmilezzi / DataCopyManager.java
Created Nov 28, 2017
A simple java class to get PostgreSQL COPY content from STDOUT to compressed file
View DataCopyManager.java
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;
@michelmilezzi
michelmilezzi / parameter_bind.rb
Last active Sep 18, 2019
PostgreSQL bind parameters
View parameter_bind.rb
# 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
@michelmilezzi
michelmilezzi / aws_glue_avoiding_duplicates.py
Last active Jun 20, 2020
AWS Glue script showing how to avoid duplicates during a job execution.
View aws_glue_avoiding_duplicates.py
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
@michelmilezzi
michelmilezzi / idempotent_script.sql
Last active Sep 28, 2020
A simple script demonstrating PostgreSQL idempotent capabilities. You can run this script as many times as you wish (it will not give duplicate object error or similar).
View idempotent_script.sql
--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);
You can’t perform that action at this time.