Skip to content

Instantly share code, notes, and snippets.

View teburd's full-sized avatar

Tom Burdick teburd

82 followers · 112 following
View GitHub Profile
@teburd
teburd / example.sql
Last active December 14, 2015 07:48
query
WITH RECURSIVE search_graph(id, parent, name, depth) AS (
SELECT v.id AS id, v.name AS name, e.vertex_1 AS parent, 1 AS depth
FROM vertices AS v
LEFT JOIN edges AS e ON e.vertex_0 = v.id AND e.edge_type_id = 0
UNION
SELECT v.id AS id, v.name AS name, e.vertex_1 AS parent, sg.depth + 1 AS depth
FROM vertices v, search_graph sg
LEFT JOIN edges AS e ON e.vertex_0 = v.id AND e.edge_type_id = 0
WHERE sg.parent = v.id
)
@teburd
teburd / bad_recursion.sql
Last active December 14, 2015 07:49
Explain Analyze Results
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2889817.55..2895073.57 rows=2102405 width=68) (actual time=345.103..360.131 rows=78331 loops=1)
Sort Key: search_graph.depth
Sort Method: external merge Disk: 4224kB
CTE search_graph
-> Recursive Union (cost=13075.32..2162313.09 rows=2102405 width=56) (actual time=59.622..257.259 rows=78331 loops=1)
-> Merge Right Join (cost=13075.32..18806.76 rows=61235 width=52) (actual time=59.619..97.254 rows=61235 loops=1)
Merge Cond: (e.vertex_0 = v.id)
-> Index Scan using edges_vertex_0_type_idx on edges e (cost=0.00..3669.65 rows=17128 width=32) (actual time=0.710..10.818 rows=17096 loops=1)
@teburd
teburd / gist:5446725
Created April 23, 2013 19:38
lists:keystore
1> A = [].
[]
2> lists:keystore(<<"test">>, 1, A, {<<"test">>, 1}).
[{<<"test">>,1}]
@teburd
teburd / gist:5555866
Created May 10, 2013 17:15
no rank, limit, or order
drake=# explain analyze select id from vertices where name_fts @@ plainto_tsquery('school'::text);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on vertices (cost=37.50..4536.36 rows=2258 width=16) (actual time=1.630..4.424 rows=2307 loops=1)
Recheck Cond: (name_fts @@ plainto_tsquery('school'::text))
-> Bitmap Index Scan on vertices_name_fts_gin_idx (cost=0.00..36.94 rows=2258 width=0) (actual time=1.255..1.255 rows=2307 loops=1)
Index Cond: (name_fts @@ plainto_tsquery('school'::text))
Total runtime: 4.803 ms
(5 rows)
@teburd
teburd / gist:5555879
Created May 10, 2013 17:17
with rank, order by, and limit
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4633.95..4634.20 rows=100 width=51) (actual time=13.523..13.535 rows=100 loops=1)
-> Sort (cost=4633.95..4639.60 rows=2258 width=51) (actual time=13.518..13.522 rows=100 loops=1)
Sort Key: (ts_rank(name_fts, plainto_tsquery('school'::text)))
Sort Method: top-N heapsort Memory: 32kB
-> Bitmap Heap Scan on vertices (cost=37.50..4547.65 rows=2258 width=51) (actual time=2.052..12.442 rows=2307 loops=1)
Recheck Cond: (name_fts @@ plainto_tsquery('school'::text))
-> Bitmap Index Scan on vertices_name_fts_gin_idx (cost=0.00..36.94 rows=2258 width=0) (actual time=1.621..1.621 rows=2307 loops=1)
Index Cond: (name_fts @@ pla
@teburd
teburd / gist:5555907
Last active December 17, 2015 05:09
TSearch Or with order by and lmit
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6391.54..6391.79 rows=100 width=109) (actual time=29.755..29.768 rows=100 loops=1)
-> Sort (cost=6391.54..6402.75 rows=4484 width=109) (actual time=29.754..29.759 rows=100 loops=1)
Sort Key: ((ts_rank(name_fts, plainto_tsquery('school'::text)) + ts_rank(description_fts, plainto_tsquery('school'::text))))
Sort Method: top-N heapsort Memory: 32kB
-> Bitmap Heap Scan on vertices (cost=76.15..6220.17 rows=4484 width=109) (actual time=3.497..28.446 rows=3824 loops=1)
Recheck Cond: ((name_fts @@ plainto_tsquery('school'::text)) OR (description_fts @@ plainto_tsquery('school'::text)))
-> BitmapOr (cost=76.15..76.15 r
@teburd
teburd / gist:5555917
Created May 10, 2013 17:21
No Rank, Order, or Limit
drake=# explain analyze select id from vertices where name_fts @@ plainto_tsquery('school') or description_fts @@ plainto_tsquery('school'); QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on vertices (cost=76.15..6164.12 rows=4484 width=16) (actual time=3.357..7.751 rows=3824 loops=1)
Recheck Cond: ((name_fts @@ plainto_tsquery('school'::text)) OR (description_fts @@ plainto_tsquery('school'::text)))
-> BitmapOr (cost=76.15..76.15 rows=4521 width=0) (actual time=2.750..2.750 rows=0 loops=1)
-> Bitmap Index Scan on vertices_name_fts_gin_idx (cost=0.00..36.94 rows=2258 width=0) (actual time=1.430..1.430 rows=2307 loops=1)
Index Cond: (name_fts @@ plainto_tsquery('school'::text))
-> Bitmap Index Scan
@teburd
teburd / gist:5555930
Created May 10, 2013 17:23
seq scan horrible
drake=# explain analyze select id from vertices where name_fts @@ plainto_tsquery('school') or description_fts @@ plainto_tsquery('school') limit 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..201.42 rows=100 width=16) (actual time=53.723..356.704 rows=100 loops=1)
-> Seq Scan on vertices (cost=0.00..9031.60 rows=4484 width=16) (actual time=53.721..356.685 rows=100 loops=1)
Filter: ((name_fts @@ plainto_tsquery('school'::text)) OR (description_fts @@ plainto_tsquery('school'::text)))
Rows Removed by Filter: 93311
Total runtime: 356.751 ms
(5 rows)
@teburd
teburd / gist:5555949
Created May 10, 2013 17:25
fastest
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=76.15..211.92 rows=100 width=16) (actual time=3.487..3.673 rows=100 loops=1)
-> Bitmap Heap Scan on vertices (cost=76.15..6164.12 rows=4484 width=16) (actual time=3.485..3.641 rows=100 loops=1)
Recheck Cond: ((name_fts @@ plainto_tsquery('school'::text)) OR (description_fts @@ plainto_tsquery('school'::text)))
-> BitmapOr (cost=76.15..76.15 rows=4521 width=0) (actual time=2.886..2.886 rows=0 loops=1)
-> Bitmap Index Scan on vertices_name_fts_gin_idx (cost=0.00..36.94 rows=2258 width=0) (actual time=1.461..1.461 rows=2307 loops=1)
Index Cond: (name_fts @@ plainto_tsquery('school'::text))
-> Bitmap Index Scan on
@teburd
teburd / blah.erl
Last active December 17, 2015 07:09
Erlang
log_attrs(Type, ActionAttrs) ->
case ActionAttrs of
[] ->
lager:debug("empty");
[_] ->
lager:debug("one");
[_, _] ->
lager:debug("two")
end.