plv8 experiments and microbenchmarks

amex data.sql

will=# \d amex
                              Table "public.amex"
 Column |       Type        |                     Modifiers                     
--------+-------------------+---------------------------------------------------
 id     | integer           | not null default nextval('amex_id_seq'::regclass)
 ticker | character varying | 
 date   | date              | 
 data   | text              | 
Indexes:
    "amex_pkey" PRIMARY KEY, btree (id)
    "close_open" btree ((get_numeric('close'::text, data) - get_numeric('open'::text, data)))
    "numeric_close" btree (get_numeric('close'::text, data))

will=# select count(*) from amex;
  count  
---------
 1095288
(1 row)

Time: 147.358 ms
will=# select * from amex limit 2;
   id    | ticker |    date    |                                       data                                       
---------+--------+------------+----------------------------------------------------------------------------------
 1000241 | IMO    | 1996-10-21 | {"open": 41.98, "high": 42.36, "low": 41.98, "close": 42.28, "volume": 966600.0}
 1000242 | IMO    | 1996-10-18 | {"open": 41.05, "high": 41.98, "low": 41.05, "close": 41.98, "volume": 770400.0}
(2 rows)

Time: 0.282 ms

will=# \df+ get_numeric 
List of functions
-[ RECORD 1 ]-------+--------------------------------
Schema              | public
Name                | get_numeric
Result data type    | numeric
Argument data types | key text, json_raw text
Type                | normal
Volatility          | immutable
Owner               | will
Language            | plv8
Source code         | 
                    |   // This is javascript!
                    |   var o = JSON.parse(json_raw);
                    |   return o[key];
                    | 
Description         |

will=# select get_numeric('open', data), get_numeric('close', data) from amex limit 2;
 get_numeric | get_numeric 
-------------+-------------
       41.98 |       42.28
       41.05 |       41.98
(2 rows)

Time: 0.380 ms

-- with index
will=# select get_numeric('open', data), get_numeric('close', data) from amex where get_numeric('close', data) > 200 limit 2;
 get_numeric | get_numeric 
-------------+-------------
         250 |         250
         345 |         345
(2 rows)

Time: 358.132 ms

-- no index
will=# select get_numeric('open', data), get_numeric('close', data) from amex_backup where get_numeric('close', data) > 200 limit 2;
 get_numeric | get_numeric 
-------------+-------------
      198.89 |      200.64
      200.06 |      200.36
(2 rows)

Time: 1222.384 ms


will=# select * from ( select (get_numeric('close', data) - get_numeric('open', data)) as diff, * from amex order by diff desc ) as thing where diff > 100 limit 5;
  diff  |   id    | ticker |    date    |                                         data                                          
--------+---------+--------+------------+---------------------------------------------------------------------------------------
 450.09 |  763996 | FFI    | 2000-09-06 | {"open": 800.16, "high": 1300.26, "low": 800.16, "close": 1250.25, "volume": 31400.0}
    407 |  582721 | EPM    | 2000-09-13 | {"open": 343.0, "high": 750.0, "low": 343.0, "close": 750.0, "volume": 17600.0}
    375 | 1108171 | MFN    | 2000-07-17 | {"open": 875.0, "high": 1340.0, "low": 875.0, "close": 1250.0, "volume": 57800.0}
    375 | 1108168 | MFN    | 2000-07-20 | {"open": 750.0, "high": 1125.0, "low": 750.0, "close": 1125.0, "volume": 2200.0}
    275 | 1108155 | MFN    | 2000-08-08 | {"open": 850.0, "high": 1125.0, "low": 850.0, "close": 1125.0, "volume": 1200.0}
(5 rows)

Time: 0.574 ms


will=# select * from ( select (get_numeric('close', data) - get_numeric('open', data)) as diff, * from amex order by diff desc ) as thing where diff > 100 or (diff < 50 and diff > 40) limit 5;
  diff  |   id    | ticker |    date    |                                         data                                          
--------+---------+--------+------------+---------------------------------------------------------------------------------------
 450.09 |  763996 | FFI    | 2000-09-06 | {"open": 800.16, "high": 1300.26, "low": 800.16, "close": 1250.25, "volume": 31400.0}
    407 |  582721 | EPM    | 2000-09-13 | {"open": 343.0, "high": 750.0, "low": 343.0, "close": 750.0, "volume": 17600.0}
    375 | 1108171 | MFN    | 2000-07-17 | {"open": 875.0, "high": 1340.0, "low": 875.0, "close": 1250.0, "volume": 57800.0}
    375 | 1108168 | MFN    | 2000-07-20 | {"open": 750.0, "high": 1125.0, "low": 750.0, "close": 1125.0, "volume": 2200.0}
    275 | 1108155 | MFN    | 2000-08-08 | {"open": 850.0, "high": 1125.0, "low": 850.0, "close": 1125.0, "volume": 1200.0}
(5 rows)

Time: 0.643 ms

experiment.sql

BEGIN;
-- plv8: http://code.google.com/p/plv8js/
CREATE OR REPLACE LANGUAGE plv8;

-- Create a function that grabs a passed key and coerces to bigint.
CREATE OR REPLACE FUNCTION plv8_project_key(key text, json_raw text)
RETURNS bigint AS $$
  // This is javascript!
  var o = JSON.parse(json_raw);
  return o[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;

-- Example in action
SELECT plv8_project_key('new-thing',
  '{"new-thing": 3, "old-thing": 2}');

-- On with the microbenchmarks

\timing on
-- This creates a new table called 'jsons' with stuff in it
SELECT '{"number": ' || generate_series || '}' AS document
  INTO TEMPORARY TABLE jsons
  FROM generate_series(1, (10^6)::integer);

CREATE INDEX index_jsons_values
  ON jsons (plv8_project_key('number', document));

ANALYZE jsons;

SELECT *
  FROM jsons
  WHERE plv8_project_key('number', document) = 999995;
SELECT *
  FROM jsons
  WHERE plv8_project_key('number', document) = 999995;

EXPLAIN ANALYZE
  SELECT *
  FROM jsons
  WHERE plv8_project_key('number', document) = 5;

EXPLAIN ANALYZE
  SELECT *
  FROM jsons
  WHERE plv8_project_key('number', document) = 5;

\timing

DROP TABLE pg_temp.jsons;

ROLLBACK;

gistfile1.txt

will=# EXPLAIN ANALYZE SELECT *
will-#   FROM jsons
will-#   WHERE plv8_project_key('number', document) = 999995;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Seq Scan on jsons  (cost=0.00..225382.99 rows=4172 width=32) (actual time=3134.089..3134.103 rows=1 loops=1)
   Filter: (plv8_project_key('number'::text, document) = 999995)
 Total runtime: 3134.119 ms
(3 rows)

Time: 3134.428 ms
will=# 
will=# CREATE INDEX index_jsons_values
will-#   ON jsons (plv8_project_key('number', document));
CREATE INDEX
Time: 4820.054 ms
will=# EXPLAIN ANALYZE SELECT *
  FROM jsons                                      
  WHERE plv8_project_key('number', document) = 999995;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsons  (cost=95.36..7682.60 rows=5000 width=32) (actual time=0.035..0.035 rows=1 loops=1)
   Recheck Cond: (plv8_project_key('number'::text, document) = 999995)
   ->  Bitmap Index Scan on index_jsons_values  (cost=0.00..94.11 rows=5000 width=0) (actual time=0.032..0.032 rows=1 loops=1)
         Index Cond: (plv8_project_key('number'::text, document) = 999995)
 Total runtime: 0.072 ms
(5 rows)

Time: 0.477 ms

json domain.sql

will=# \d jsononly 
  Table "public.jsononly"
 Column | Type | Modifiers 
--------+------+-----------
 data   | json | 

will=# \dD
                       List of domains
 Schema | Name | Type | Modifier |           Check           
--------+------+------+----------+---------------------------
 public | json | text |          | CHECK (valid_json(VALUE))
(1 row)

will=# \df+ valid_json
List of functions
-[ RECORD 1 ]-------+----------------------------------------
Schema              | public
Name                | valid_json
Result data type    | boolean
Argument data types | json text
Type                | normal
Volatility          | immutable
Owner               | will
Language            | plv8
Source code         | 
                    |   try { JSON.parse(json); return true }
                    |   catch(e) { return false}
                    | 
Description         | 

will=# insert into jsononly values('not good json');
ERROR:  value for domain json violates check constraint "json_check"
will=# insert into jsononly values('{"good": "json", "is": true}');
INSERT 0 1
Time: 0.301 ms

transcript.txt

postgres=# BEGIN;
BEGIN
postgres=# -- plv8: http://code.google.com/p/plv8js/
postgres=# CREATE OR REPLACE LANGUAGE plv8;
CREATE LANGUAGE
postgres=# 
postgres=# -- Create a function that grabs a passed key and coerces to bigint.
postgres=# CREATE OR REPLACE FUNCTION plv8_project_key(key text, json_raw text)
postgres-# RETURNS bigint AS $$
postgres$#   // This is javascript!
postgres$#   var o = JSON.parse(json_raw);
postgres$#   return o[key];
postgres$# $$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE FUNCTION
postgres=# 
postgres=# -- Example in action
postgres=# SELECT plv8_project_key('new-thing',
postgres(#   '{"new-thing": 3, "old-thing": 2}');
 plv8_project_key 
------------------
                3
(1 row)

postgres=# 
postgres=# -- On with the microbenchmarks
postgres=# 
postgres=# \timing
Timing is on.
postgres=# -- This creates a new table called 'jsons' with stuff in it
postgres=# SELECT '{"number": ' || generate_series || '}' AS document
postgres-#   INTO TEMPORARY TABLE jsons
postgres-#   FROM generate_series(1, (10^6)::integer);
SELECT 1000000
Time: 1810.276 ms
postgres=# 
postgres=# CREATE INDEX index_jsons_values
postgres-#   ON jsons (plv8_project_key('number', document));
CREATE INDEX
Time: 6527.797 ms
postgres=# 
postgres=# ANALYZE jsons;
ANALYZE
Time: 406.367 ms
postgres=# 
postgres=# SELECT *
postgres-#   FROM jsons
postgres-#   WHERE plv8_project_key('number', document) = 999995;
      document      
--------------------
 {"number": 999995}
(1 row)

Time: 0.296 ms
postgres=# SELECT *
postgres-#   FROM jsons
postgres-#   WHERE plv8_project_key('number', document) = 999995;
      document      
--------------------
 {"number": 999995}
(1 row)

Time: 0.208 ms
postgres=# 
postgres=# EXPLAIN ANALYZE
postgres-#   SELECT *
postgres-#   FROM jsons
postgres-#   WHERE plv8_project_key('number', document) = 5;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_jsons_values on jsons  (cost=0.25..8.63 rows=1 width=18) (actual time=0.016..0.017 rows=1 loops=1)
   Index Cond: (plv8_project_key('number'::text, document) = 5)
 Total runtime: 0.030 ms
(3 rows)

Time: 0.283 ms
postgres=# 
postgres=# EXPLAIN ANALYZE
postgres-#   SELECT *
postgres-#   FROM jsons
postgres-#   WHERE plv8_project_key('number', document) = 5;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_jsons_values on jsons  (cost=0.25..8.63 rows=1 width=18) (actual time=0.007..0.007 rows=1 loops=1)
   Index Cond: (plv8_project_key('number'::text, document) = 5)
 Total runtime: 0.021 ms
(3 rows)

Time: 0.251 ms
postgres=# 
postgres=# \timing
Timing is off.
postgres=# 
postgres=# DROP TABLE pg_temp.jsons;
DROP TABLE
postgres=# 
postgres=# ROLLBACK;
ROLLBACK