plv8 experiments and microbenchmarks

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
-- 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;

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

This clearly fits in memory, and is one million records (try bumping "10^6" it to "10^7" or "10^8" records, for a different kind of fun...). But look at that execution time in EXPLAIN ANALYZE: 1s / 0.021ms = 47600, on one processor. The larger number (0.251ms) is calculated from the client-side, 'psql'.

With 10^8 documents (100 million). Here we can see the effects of not fitting in memory. Also, we can get a sense of how fast indexing is, in this case about 130,000 records per second in the bulk-load scenario.

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^8)::integer);
SELECT 100000000
Time: 206070.884 ms
postgres=# 
postgres=# CREATE INDEX index_jsons_values
postgres-#   ON jsons (plv8_project_key('number', document));
CREATE INDEX
Time: 733559.485 ms
postgres=# 
postgres=# ANALYZE jsons;
ANALYZE
Time: 45902.619 ms
postgres=# 
postgres=# SELECT *
postgres-#   FROM jsons
postgres-#   WHERE plv8_project_key('number', document) = 999995;
      document      
--------------------
 {"number": 999995}
(1 row)

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

Time: 0.285 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..19.49 rows=1 width=20) (actual time=34.900..34.902 rows=1 loops=1)
   Index Cond: (plv8_project_key('number'::text, document) = 5)
 Total runtime: 34.934 ms
(3 rows)

Time: 42.840 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..19.49 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=1)
   Index Cond: (plv8_project_key('number'::text, document) = 5)
 Total runtime: 0.034 ms
(3 rows)

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