public
Created

plv8 experiments and microbenchmarks

  • Download Gist
experiment.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.