Skip to content

Instantly share code, notes, and snippets.

View ringerc's full-sized avatar

Craig Ringer ringerc

108 followers · 4 following
View GitHub Profile
@ringerc
ringerc / gist:7429182
Created November 12, 2013 11:04
Parse, rewrite and plan trees of a simple SELECT from a view, showing subquery expansion
regress=> SELECT * FROM t_even;
LOG: parse tree:
DETAIL: {QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
@ringerc
ringerc / gist:7423819
Last active December 28, 2015 01:49
Diff of parse, rewritten and plan trees from "DELETE FROM t;" against "DELETE FROM t USING t2 WHERE t.id = t2.id;"
--- /tmp/simple-delete 2013-11-12 09:26:55.077406915 +0800
+++ /tmp/join-delete 2013-11-12 09:49:06.172045074 +0800
@@ -1,4 +1,4 @@
-regress=> DELETE FROM t;
+regress=> DELETE FROM t USING t2 WHERE t.id = t2.id;
LOG: parse tree:
DETAIL: {QUERY
:commandType 4
@@ -28,9 +28,27 @@
:lateral false
@ringerc
ringerc / gist:7413693
Last active December 28, 2015 00:29
Simple DELETE FROM t plan, parse, and rewrite trees; PostgreSQL 9.3, see https://wiki.postgresql.org/wiki/Automatically_updatable_security_barrier_views#How_does_updating_a_join_work.3F
regress=> DELETE FROM t;
LOG: parse tree:
DETAIL: {QUERY
:commandType 4
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 1
:hasAggs false
:hasWindowFuncs false
@ringerc
ringerc / gist:7413509
Last active December 28, 2015 00:29
PostgreSQL parse, rewrite, and plan trees for a `delete ... using statement` in Pg 9.3; see https://wiki.postgresql.org/wiki/Automatically_updatable_security_barrier_views#How_does_updating_a_join_work.3F
regress=> DELETE FROM t USING t2 WHERE t.id = t2.id;
LOG: parse tree:
DETAIL: {QUERY
:commandType 4
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 1
:hasAggs false
:hasWindowFuncs false
@ringerc
ringerc / gist:5755247
Last active June 3, 2022 14:53
Awful query demo for http://stackoverflow.com/questions/17037508/sql-when-it-comes-to-not-in-and-not-equal-to-which-is-more-efficient-and-why/17038097#17038097
CREATE UNLOGGED TABLE exclude_test(id integer primary key);
INSERT INTO exclude_test(id) SELECT generate_series(1,50000);
CREATE UNLOGGED TABLE exclude AS SELECT x AS item FROM generate_series(1,40000,4) x;
-- Horrific AND list, takes 80s to plan and execute here:
EXPLAIN ANALYZE SELECT id FROM exclude_test WHERE id <> 1 AND id <> 5 AND id <> 9 AND id <> 13 AND id <> 17 AND id <> 21 AND id <> 25 AND id <> 29 AND id <> 33 AND id <> 37 AND id <> 41 AND id <> 45 AND id <> 49 AND id <> 53 AND id <> 57 AND id <> 61 AND id <> 65 AND id <> 69 AND id <> 73 AND id <> 77 AND id <> 81 AND id <> 85 AND id <> 89 AND id <> 93 AND id <> 97 AND id <> 101 AND id <> 105 AND id <> 109 AND id <> 113 AND id <> 117 AND id <> 121 AND id <> 125 AND id <> 129 AND id <> 133 AND id <> 137 AND id <> 141 AND id <> 145 AND id <> 149 AND id <> 153 AND id <> 157 AND id <> 161 AND id <> 165 AND id <> 169 AND id <> 173 AND id <> 177 AND id <> 181 AND id <> 185 AND id <> 189 AND id <> 193 AND id <> 197 AND id <> 201 AND id <> 205 AND id <>
@ringerc
ringerc / gist:5742942
Created June 9, 2013 09:33
Backtrace of execution of simple PL/PgSQL array access
craig=> DO
$$
BEGIN
PERFORM (ARRAY[1])[1];
END;
$$ LANGUAGE plpgsql;
with a breakpoint set at ExecEvalArrayRef results in:
(gdb) bt