Benchmarking

See motivations at issue #13. For all tests,

DROP SCHEMA IF EXISTS bench1 CASCADE;
CREATE SCHEMA bench1;
-- ... do all ... drop schema.

Examples from old v0.1.0, to compare "all in one array": select * from dataset.vmeta_summary,

id	urn	pkey	lang	n_cols	n_rows
15	datasets-br:br_city_codes	state/lexLabel	pt	9	5570
11	datasets:country_codes			56	250
12	datasets:world_cities	geonameid		4	23018
1	lexml:autoridade	id	pt	9	601

CREATE TABLE bench1.br_city_codes AS 
  SELECT c FROM dataset.big WHERE source=15; -- 5570
CREATE TABLE bench1.br_city_codes_vw AS 
  SELECT * FROM dataset.vw_br_city_codes; -- 5570
CREATE TABLE bench1.br_city_codes_array AS 
  SELECT jsonb_agg(c) as x FROM dataset.big WHERE source=15; -- 1

CREATE TABLE bench1.country_codes AS 
  SELECT c FROM dataset.big WHERE source=11; -- 250
CREATE TABLE bench1.country_codes_vw AS 
  SELECT * FROM dataset.vw_country_codes; -- 250
CREATE TABLE bench1.country_codes_array AS 
  SELECT jsonb_agg(c) as x FROM dataset.big WHERE source=11; -- 1

CREATE TABLE bench1.world_cities AS 
  SELECT c FROM dataset.big WHERE source=12; -- 23018
CREATE TABLE bench1.world_cities_vw AS 
  SELECT * FROM dataset.vw_world_cities; -- 23018
CREATE TABLE bench1.world_cities_array AS 
  SELECT jsonb_agg(c) as x FROM dataset.big WHERE source=12; -- 1

CREATE TABLE bench1.autoridade AS 
  SELECT c FROM dataset.big WHERE source=1; -- 601
CREATE TABLE bench1.autoridade_vw AS 
  SELECT * FROM dataset.vw_autoridade; -- 601
CREATE TABLE bench1.autoridade_array AS 
  SELECT jsonb_agg(c) as x FROM dataset.big WHERE source=1; -- 1

Disk-usage

Based on SQL-VIEWs pgvw_class_usage and pgvw_nsclass_usage of step1-lib.sql.

Example: SELECT * FROM pgvw_class_usage WHERE nspname='bench1' AND relname LIKE 'world_cit%';. Result: "table_size=8192 bytes" for world_cities_array (one array) and "2056 kB" for world_cities (many rows).

RESULTS:

relname	total_bytes	table_bytes	table_size	percent
autoridade	262144	253952	248 kB	100%
autoridade_vw	1531904	1523712	1488 kB	91%
autoridade_array	65536	8192	8192 bytes	~3%
br_city_codes	761856	753664	736 kB	100%
br_city_codes_vw	606208	598016	584 kB	79%
br_city_codes_array	262144	8192	8192 bytes	~1%
country_codes	204800	196608	192 kB	100%
country_codes_vw	155648	147456	144 kB	75%
country_codes_array	106496	8192	8192 bytes	~4%
world_cities	2113536	2105344	2056 kB	100%
world_cities_vw	1531904	1523712	1488 kB	72%
world_cities_array	507904	8192	8192 bytes	~0.4%

Conclusions:

• usual SQL table is lite compared with JSONb-per-row, average of 80% of disk-usage. Same order of magnitude.
• JSONb-array ranges from 10 to 100 times more economic tham both! Shows the best disk-usage performance.
  ~1/20 is an average reference fraction.

Performance

Based on EXPLAIN ANALYZE. Lets start with a VIEW for each *_array table, that get back all the rows, by jsonb_array_elements() function, that splits all lines into JSON-rows. Them a view do casts and renames, in both, dataset.vw_br_city_codes and this simulator:

CREATE VIEW bench1.br_city_codes_simul AS  -- simulates br_city_codes_vw
  SELECT c ->> 0 AS name,  c ->> 1 AS state,
    c ->> 2 AS wdid,       c ->> 3 AS idibge,
    c ->> 4 AS lexlabel,   (c ->> 5)::integer AS creation,
    (c ->> 6)::integer AS extinction,  
    c ->> 7 AS postalcode_ranges,  c ->> 8 AS notes
   FROM  (SELECT jsonb_array_elements(x) FROM  bench1.br_city_codes_array) t(c);

EXPLAIN ANALYZE SELECT * FROM bench1.br_city_codes_simul; -- average ~25 ms. 
EXPLAIN ANALYZE SELECT * FROM dataset.vw_br_city_codes; -- average ~20 ms. Comparing with this case.
EXPLAIN ANALYZE SELECT * FROM bench1.br_city_codes_vw; -- ~1 ms. Is a reference as real table

EXPLAIN ANALYZE SELECT * FROM bench1.br_city_codes_simul
  WHERE state='SP' AND wdid>'Q7' AND idibge::int>3524204; -- average ~12 ms. 
EXPLAIN ANALYZE SELECT * FROM dataset.vw_br_city_codes
  WHERE state='SP' AND wdid>'Q7' AND idibge::int>3524204; -- average ~6 ms. 
EXPLAIN ANALYZE SELECT * FROM bench1.br_city_codes_vw
  WHERE state='SP' AND wdid>'Q7' AND idibge::int>3524204; -- average ~2.5 ms. 
-- -- --

CREATE VIEW bench1.world_cities_simul AS  -- simulates world_cities
  SELECT c ->> 0 AS name,        c ->> 1 AS country, 
         c ->> 2 AS subcountry,  (c ->> 3)::integer AS geonameid
  FROM  (SELECT jsonb_array_elements(x) FROM  bench1.world_cities_array) t(c);

EXPLAIN ANALYZE SELECT * FROM bench1.world_cities_simul; -- average ~50 ms. 
EXPLAIN ANALYZE SELECT * FROM dataset.vw_world_cities; -- average ~50 ms. Comparing with this case.
EXPLAIN ANALYZE SELECT * FROM bench1.world_cities_vw; -- ~4 ms. Is a reference as real table

EXPLAIN ANALYZE SELECT * FROM bench1.world_cities_simul
 WHERE name>'Vatovavy' AND geonameid<1260000; -- 30 ms
EXPLAIN ANALYZE SELECT * FROM dataset.vw_world_cities
 WHERE name>'Vatovavy' AND geonameid<1260000; -- 21 ms
EXPLAIN ANALYZE SELECT * FROM bench1.world_cities_vw
 WHERE name>'Vatovavy' AND geonameid<1260000;  -- 7ms

All are with no indexation. Conclusions:

• for little tables (less tham 1000 rows) the performance differences are negligenciable.

• with br_city_codes* (5570 rows, 9 cols): good performance of JSONb's when comapared with real SQL. Twice as slow is valid for online aplications.

• with world_cities* (23018 rows, 4 cols): medium performance for JSON's, and comparing JSON-array with JSON-rows, no expressive advantage for rows.

General conclusion: lets use "one tabular JSONb for all dataset, an JSON-array-of-arrays" (the JTD label "tab-aoa").
And, when really need perfornace, materialize the VIEW.