rapimo

-- sample data
set max_parallel_workers_per_gather=1;

CREATE TABLE data AS
SELECT n, chr(n) as g, i FROM generate_series(97,122) n, generate_series(1,1e6) i ORDER BY i;

SELECT CASE g
    WHEN 'a' THEN 'one'
    WHEN 'b' THEN 'one'
    WHEN 'c' THEN 'one'

rapimo

SELECT hstore(array_agg(v), array_agg(c::text)) FROM (
SELECT v, COUNT(*) as c ,1 as agg from  unnest(ARRAY['foo','bar','baz','foo']) v GROUP BY v) t
GROUP BY agg

--> "bar"=>"1", "baz"=>"1", "foo"=>"2"

rapimo

CREATE EXTENSION istore;
CREATE TABLE data AS
SELECT (i%30)::int as event_id, (random()*100 + 10)::int as events
FROM generate_series(1,1e6) i;

SELECT
SUM(events) FILTER (WHERE event_id = 0) as "0_events",
SUM(events) FILTER (WHERE event_id = 1) as "1_events",
SUM(events) FILTER (WHERE event_id = 2) as "2_events",
SUM(events) FILTER (WHERE event_id = 3) as "3_events",

rapimo

\timing

CREATE EXTENSION istore;

CREATE TABLE foo AS SELECT i%10 as id, i FROM generate_series(1,1e7::int) i;

SELECT CASE 
WHEN id = 1 THEN 10 WHEN id = 2 THEN 20 WHEN id = 3 THEN 30 WHEN id = 4 THEN 40 WHEN id = 5 THEN 50 
WHEN id = 6 THEN 60 WHEN id = 7 THEN 70 WHEN id = 8 THEN 80 WHEN id = 9 THEN 90 
ELSE 0 END, 

rapimo

#!/bin/sh

# script to add apt.postgresql.org to sources.list

# from command like
CODENAME="$1"
# lsb_release is the best interface, but not always available
if [ -z "$CODENAME" ]; then
    CODENAME=$(lsb_release -cs 2>/dev/null)
fi

rapimo

CREATE TABLE small (
    a smallint,
    b smallint,
    c integer,
    d integer,
    e date
)

CREATE TABLE big (
    c integer,

rapimo

# create ramdisk of 500MB (500 x 2048)
diskutil erasevolume HFS+ "ramdisk" `hdiutil attach -nomount ram://1024000`
# create pg tablespace to use ramdisk
psql -c "CREATE TABLESPACE ramdisk LOCATION '/Volumes/ramdisk/'" postgres
# delete ramdisk
hdiutil eject /Volumes/ramdisk/

rapimo

#!/bin/sh

BREW_POSTGRES_DIR=`brew info postgres | awk '{print $1"/bin"}' | grep "/postgresql/"`
LION_POSTGRES_DIR=`which postgres | xargs dirname`
LION_PSQL_DIR=`which psql | xargs dirname`

sudo mkdir -p $LION_POSTGRES_DIR/archive
sudo mkdir -p $LION_PSQL_DIR/archive

for i in `ls $BREW_POSTGRES_DIR`

rapimo

get https://github.com/gregs1104/pgtune
see http://www.postgresql.org/docs/8.4/static/kernel-resources.html for reasonable kernel conf

play around with kernel settings without restart

    sudo sysctl -w kern.sysv.shmmax=4409466880 #4GB must be a multiplier of 4096
    kern.sysv.shmmax: 4194304 -> 1073741824

shmall must be shmax / pagesize
get pagesize with

rapimo

Select (SELECT hstore(array_agg(v), array_agg(c::text)) FROM (
  SELECT v, COUNT(*) as c from unnest(array_agg(country)) v GROUP BY v) t 
 ) countries FROM test;

-- =>  "de"=>"10669", "en"=>"21542", "fr"=>"21573", "it"=>"10783", "us"=>"21774"