Avoid 1:1 Relationships in Your Data Model

Jim_Knicely · July 2019

1:1 relationships exist in row-oriented databases because of performance concerns. As a columnar database, there is no reason in Vertica to separate large tables out into separate structures.

That is, instead of joining two large fact tables together, combine them into a single table!

Example:

dbadmin=> \d big1;
                                List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
public | big1  | a      | int  |    8 |         | f        | f           |
(1 row)

dbadmin=> \d big2;
                                List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
public | big2  | b      | int  |    8 |         | f        | f           |
(1 row)

dbadmin=> SELECT COUNT(*) FROM big1;
   COUNT
------------
5000000000
(1 row)

dbadmin=> SELECT COUNT(*) FROM big2;
   COUNT
------------
5000000000
(1 row)

dbadmin=> \timing
Timing is on.

dbadmin=> SELECT COUNT(*) FROM big1 JOIN big2 ON b = a;
   COUNT
------------
5000000000
(1 row)

Time: First fetch (1 row): 352338.000 ms. All rows formatted: 352338.038 ms

dbadmin=> SELECT COUNT(b) FROM big1 JOIN big2 ON b = a;
   COUNT
------------
5000000000
(1 row)

Time: First fetch (1 row): 311465.060 ms. All rows formatted: 311465.092 ms

dbadmin=> CREATE TABLE big1_big2 AS SELECT a, b FROM big1 JOIN big2 ON b = a;
CREATE TABLE
Time: First fetch (0 rows): 1192135.459 ms. All rows formatted: 1192135.481 ms

dbadmin=> SELECT COUNT(b) FROM big1_big2;
   COUNT
------------
5000000000
(1 row)

Time: First fetch (1 row): 15068.069 ms. All rows formatted: 15080.214 ms

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/DirectedQueries/RewritingQueries.htm

Have fun!