michelmilezzi

--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);

michelmilezzi

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

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

# 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

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

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

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;