Skip to content

Instantly share code, notes, and snippets.

Avatar

Amit Chhajer amitchhajer

View GitHub Profile
View storage_space_table.sql
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS index
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS table
FROM (
SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS table_name
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
View bloated_tables.sql
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
round(avg((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric),1) AS tbloat,
round(avg(CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END), 0) AS wastedbytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
@amitchhajer
amitchhajer / add_row.sh
Created Mar 31, 2017 — forked from kristjan/add_row.sh
Google Spreadsheet row insertion example
View add_row.sh
# $auth from auth.sh
# $spreadsheet_id from get_spreadsheets.sh
# $worksheet_id from get_worksheets.sh
curl \
--header "Authorization: GoogleLogin auth=$auth" \
--header 'Content-Type: application/atom+xml' \
-d @data.xml \
-X POST \
"https://spreadsheets.google.com/feeds/list/$spreadsheet_id/$worksheet_id/private/full"
# Example data in data.xml
@amitchhajer
amitchhajer / fibanacci_recursive_2_vars
Created Oct 13, 2014
Fibonaaci recursive with 2 variables
View fibanacci_recursive_2_vars
<?php
function fibo($n, $first =1, $second=1)
{
if($n==1 || $n==2)
return $second;
else
return fibo($n-1, $second,($first + $second));
}
View ubuntu1204_php54
apt-get install -y ppa-purge
add-apt-repository -y ppa:ondrej/php5-oldstable
apt-get update
ppa-purge ppa:ondrej/php5
apt-get update && apt-get upgrade -y && apt-get autoremove -y && apt-get autoclean -y
@amitchhajer
amitchhajer / git_branches_specific_user
Last active Jan 2, 2016
Get all the branches in git for specific user
View git_branches_specific_user
git for-each-ref --format='%(committerdate) %09 %(authorname) %09 %(refname)' | sort -k5n -k2M -k3n -k4n | grep 'user'
@amitchhajer
amitchhajer / delete_duplicate.mysql
Created Jul 20, 2013
Delete duplicate records from database leaving one of them.
View delete_duplicate.mysql
delete from my_table mt1, my_table mt2 where mt1.fieldX = mt2.fieldX and mt1.fieldY = mt2.fieldY and mt1.primary_key > mt2.primary_key
View increment_decrement.py
import sublime
import sublime_plugin
class NumberCommand(sublime_plugin.TextCommand):
def run(self, edit):
selection = self.view.sel()
for region in selection:
try:
value = int(self.view.substr(region))
self.view.replace(edit, region, str(self.op(value)))
@amitchhajer
amitchhajer / mysql formatted result
Created Jan 10, 2013
Formatted query result mysql
View mysql formatted result
After into mysql cli
use command " pager less -SFX " and then use mysql cli normally
You can use arrow keys as well as pageup/down keys.
@amitchhajer
amitchhajer / Count Code lines
Created Jan 5, 2013
Count number of code lines in git repository per user
View Count Code lines
git ls-files -z | xargs -0n1 git blame -w | perl -n -e '/^.*\((.*?)\s*[\d]{4}/; print $1,"\n"' | sort -f | uniq -c | sort -n