Skip to content

Instantly share code, notes, and snippets.

View kmatt's full-sized avatar
😐

Matt Keranen kmatt

😐
25 followers · 23 following
  • SE US
View GitHub Profile
@kmatt
kmatt / psql_upsert.sql
Last active August 29, 2015 13:56
PostgreSQL upsert pattern from writable CTE
WITH upsert AS (
UPDATE target
SET col1 = s.col1,
col2 = s.col2,
...
FROM source s
WHERE s.key = target.key
RETURNING s.*
)
INSERT INTO target ( ... )
@kmatt
kmatt / query_planner.markdown
Created March 19, 2014 17:25 — forked from hgmnz/query_planner.markdown

Types of index scans

Indexes

Sequential Scan:

  • Read every row in the table
  • No reading of index. Reading from indexes is also expensive.
@kmatt
kmatt / BBEdit-TextWrangler_RegEx_Cheat_Sheet.txt
Created March 27, 2014 00:37 — forked from ccstone/BBEdit-TextWrangler_RegEx_Cheat_Sheet.txt
————————————————————————————————————————————————————————————————————————————————————————————————————
BBEDIT/TEXTWRANGLER REGULAR EXPRESSION GUIDE MODIFIED 2014-01-13 : 00:12
————————————————————————————————————————————————————————————————————————————————————————————————————
NOTES:
The PCRE engine (Perl Compatible Regular Expressions) is what BBEdit and TextWrangler use.
Items I'm unsure of are marked '# PCRE?'. The list while fairly comprehensive is not complete.
@kmatt
kmatt / DrillOnCentos
Last active August 29, 2015 13:59
Building Apache Drill on CentOS 6.5
#Based on https://cwiki.apache.org/confluence/display/DRILL/Compiling+Drill+from+source
wget http://repos.fedorapeople.org/repos/dchen/apache-maven/epel-apache-maven.repo -O /etc/yum.repos.d/epel-apache-maven.repo
yum install apache-maven
ln -s /usr/share/apache-maven/bin/mvn /usr/bin/
yum install java-1.7.0-openjdk.x86_64
yum install java-1.7.0-openjdk-devel
@kmatt
kmatt / rediff.py
Created April 16, 2014 20:38 — forked from yoav-steinberg/rediff.py
import urlparse
import argparse
import redis
import sys
from multiprocessing import Pool
import signal
def parse_redis_url(s):
url = urlparse.urlparse(s)
if not url.scheme:
@kmatt
kmatt / wer_all.R
Created April 23, 2014 19:27 — forked from glamp/wer_all.R
library(plyr)
find_zones <- function(x) {
x.mean <- mean(x)
x.sd <- sd(x)
boundaries <- seq(-4, 4)
# creates a set of zones for each point in x
zones <- sapply(boundaries, function(i) {
i * rep(x.sd, length(x))
})
@kmatt
kmatt / New New Index Bloat Query
Created July 7, 2014 19:31 — forked from jberkus/New New Index Bloat Query
WITH btree_index_atts AS (
SELECT nspname, relname, reltuples, relpages, indrelid, relam,
regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
indexrelid as index_oid
FROM pg_index
JOIN pg_class ON pg_class.oid=pg_index.indexrelid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
JOIN pg_am ON pg_class.relam = pg_am.oid
WHERE pg_am.amname = 'btree'
),
@kmatt
kmatt / rrdxml.py
Last active August 29, 2015 14:06 — forked from afternoon/rrdxml.py
#!/usr/bin/env python
"""Export CSV from an RRD XML dump.
Usage: rrdxml.py file.xml rra
Where rra is the 0-based index for the RRA you want to dump.
"""
from csv import writer
from itertools import chain, izip
@kmatt
kmatt / gist:98934ebb62b5eb87ece9
Created January 19, 2015 05:10
Compile PostgreSQL 9.4 with dtrace on FreeBSD 10
If make fails with "failed to resolve INP_IPV4":
$ pwd
/usr/ports/databases/postgresql94-server
$ kldload dtraceall
$ echo"dtraceall_load=YES" >> /boot/loader.conf
$ make
@kmatt
kmatt / zfsgetall.txt
Last active August 29, 2015 14:14
FreeBDS v10 ZFS config for PostgreSQL v9.3/4 DWH
# 16GB RAM / 6 x 10K RPM HDDs striped / 4 CPU
# DWH / OLAP workloads - 128K blocksize providing better querytimes
# Using old benchw datasets to compare platforms (http://benchw.sourceforge.net/)
# Started with these at OS install defaults, subsequently adjusting between 2 - 8 GB
kern.ipc.shmmax=8576069632
kern.ipc.shmall=2093767
kern.ipc.semmap=256
NAME PROPERTY VALUE SOURCE