Created
August 17, 2011 04:17
-
-
Save fdr/1150804 to your computer and use it in GitHub Desktop.
plv8 experiments and microbenchmarks
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.