Skip to content

Instantly share code, notes, and snippets.

View pramsey's full-sized avatar

Paul Ramsey pramsey

View GitHub Profile
@pramsey
pramsey / gist:ff7cbf70dbe581189565
Created March 25, 2016 19:18
PostgreSQL Parallel GUC Documentation
max_parallel_degree 0
Sets the maximum degree of parallelism for an individual parallel
operation. Note that the requested number of workers may not actually
be available at runtime. Parallel workers are taken from the pool
of processes established by `max-worker-processes`
parallel_tuple_cost 0.1
@pramsey
pramsey / kmeans.sql
Created January 26, 2016 23:45
Simple k-means example
-- Run against https://github.com/pramsey/postgis/tree/kmeans
CREATE TABLE g (id integer, g geometry);
INSERT INTO g
WITH r AS (
SELECT random() u1, random() u2 FROM generate_series(1,10)
),
xy AS (
SELECT a + sqrt(-2*ln(u1))*cos(2*pi()*u2) AS x,
@pramsey
pramsey / vgp.txt
Created January 20, 2016 17:40
valgrinding postgresql problems
# pgsql built with USE_VALGRIND
# OSX 10.11, valgrind from svn trunk
Butterfly:~/Code/postgresql-9.5.0 pramsey$ valgrind --leak-check=no --gen-suppressions=all \
--suppressions=src/tools/valgrind.supp --time-stamp=yes \
--log-file=$HOME/pg-valgrind/%p.log --trace-children=yes \
postgres \
--log_line_prefix="%m %p " \
--log_statement=all \
--shared_buffers=64MB 2>&1 | tee $HOME/pg-valgrind/postmaster.log
@pramsey
pramsey / p2h_distance.sql
Last active January 15, 2016 15:13
Parcel Distance to Hydrant
-- An example of a lateral join driving a nearest-neighbor
-- distance calculation
SELECT
parcels.*,
-- keep the hydrant id around, might be useful later
hydrants.cartodb_id as hydrant_cartodb_id,
-- calculate distance over the spheroid using geography distance
ST_Distance(geography(hydrants.the_geom), geography(parcels.the_geom)) as distance
FROM
-- for this data, removing the duplicate parcel geometries and
@pramsey
pramsey / recursive_fdw.sql
Created January 5, 2016 14:36
Create a recursive connections in PostgreSQL FDW, exhausting your client connections
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
END;
$d$;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
@pramsey
pramsey / pgsql-parallel-postgis.md
Last active December 6, 2015 21:04
Parallel Sequence Scan and PostGIS
@pramsey
pramsey / ccog-readinglist.md
Last active September 3, 2021 00:30
Canadian Council on Geomatics Reading List
@pramsey
pramsey / cdb_delayed_invalidate.sql
Created November 12, 2015 17:59
Delayed Invalidation Trigger
CREATE OR REPLACE FUNCTION public.CDB_Delayed_TableMetadata_Trigger()
RETURNS trigger AS $$
DECLARE upd timestamp;
BEGIN
IF TG_RELID = 'cartodb.CDB_TableMetadata'::regclass::oid THEN
RETURN NULL;
END IF;
SELECT updated_at INTO upd
FROM cartodb.CDB_TableMetadata WHERE tabname = TG_RELID::regclass;
IF upd IS NOT NULL AND (now() - upd) > '20m'::interval THEN
@pramsey
pramsey / torquetile_speed.txt
Created October 28, 2015 22:15
Torque Tiles in SQL Speed tests
Manhatten east of Lincoln Tunnel
14, 4824, 6157
----------------------------------------------------------------------
Tile creation:
SELECT Count(*) from (
SELECT Count(1) AS count
,floor(256*(((2^14)*(20037508 + ST_X(the_geom_webmercator))/40075016)::numeric % 1)) AS x
@pramsey
pramsey / aws_es_tests_20151019.txt
Created October 19, 2015 18:21
AWS Elasticsearch test queries
###############################################################################
# AWS compute optimized instance, 16vcpu, 30gb ram
###############################################################################
# how many trips indexed?
#
# 15M, 22ms
# 26M, 50ms
# 39M, 80ms
# 54M, 93ms