simple_join_test_uuid_vs_int.sql

-- Postgres 9.5.3.0 on OSX

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS u, v, i, j, m, n;

-- Tables
CREATE TABLE u (uuid UUID PRIMARY KEY, x INT, y INT); 
CREATE TABLE v (uuid UUID PRIMARY KEY, x INT, y INT); 
CREATE TABLE i (id SERIAL PRIMARY KEY, x INT, y INT); 
CREATE TABLE j (id SERIAL PRIMARY KEY, x INT, y INT); 
CREATE TABLE n (id BIGSERIAL PRIMARY KEY, x INT, y INT); 
CREATE TABLE m (id BIGSERIAL PRIMARY KEY, x INT, y INT); 


-- UUID tables
INSERT INTO u (uuid, x, y)
  SELECT
    uuid_generate_v4(),
    floor(random()*(1000-1)+1),
    floor(random()*(1000-1)+1)
FROM
  generate_series(1, 10*1000*1000);
-- Time: 130630.286 ms

INSERT INTO v(uuid, x, y)
SELECT
  uuid, 
  floor(random()*(1000-1)+1),
  floor(random()*(1000-1)+1)
 FROM u;
-- Time: 115547.952 ms

-- INT tables
INSERT INTO i (x, y)
  SELECT
    floor(random()*(1000-1)+1),
    floor(random()*(1000-1)+1)
FROM
  generate_series(1, 10*1000*1000);
-- Time: 75386.801 ms

INSERT INTO j(id, x, y)
SELECT
  id, 
  floor(random()*(1000-1)+1),
  floor(random()*(1000-1)+1)
 FROM i;
-- Time: 68258.274 ms

-- BIGINT tables
INSERT INTO n (x, y)
  SELECT
    floor(random()*(1000-1)+1),
    floor(random()*(1000-1)+1)
FROM
  generate_series(1, 10*1000*1000);
-- Time: 74340.289 ms

INSERT INTO m(id, x, y)
SELECT
  id, 
  floor(random()*(1000-1)+1),
  floor(random()*(1000-1)+1)
 FROM n;
-- Time: 68720.659 ms


-- Simple joins

-- Join on UUID
SELECT count(*) FROM u, v WHERE u.uuid = v.uuid;
-- Time: 21985.111 ms, 16619.589 ms

-- Join on Int PK
SELECT count(*) FROM i, j WHERE i.id = j.id;
-- Time: 14852.755 ms

-- Join on Bigint PK
SELECT count(*) FROM m, n WHERE n.id = m.id;
-- Time: 15263.313 ms, 9964.211 ms


-- UUID is around 10% with 1 Million records and 44% with 10 Million.

EXPLAIN ANALYZE SELECT count(*) FROM u, v WHERE u.uuid = v.uuid;

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                             QUERY PLAN                                                             │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Aggregate  (cost=922712.90..922712.91 rows=1 width=0) (actual time=18853.558..18853.558 rows=1 loops=1)                            │
│   ->  Hash Join  (cost=337526.59..897712.61 rows=10000115 width=0) (actual time=5608.202..17864.654 rows=10000000 loops=1)         │
│         Hash Cond: (u.uuid = v.uuid)                                                                                               │
│         ->  Seq Scan on u  (cost=0.00..163696.15 rows=10000115 width=16) (actual time=0.038..2252.644 rows=10000000 loops=1)       │
│         ->  Hash  (cost=163696.15..163696.15 rows=10000115 width=16) (actual time=5595.571..5595.571 rows=10000000 loops=1)        │
│               Buckets: 131072  Batches: 256  Memory Usage: 2859kB                                                                  │
│               ->  Seq Scan on v  (cost=0.00..163696.15 rows=10000115 width=16) (actual time=0.013..2063.690 rows=10000000 loops=1) │
│ Planning time: 0.446 ms                                                                                                            │
│ Execution time: 18854.615 ms                                                                                                       │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

EXPLAIN ANALYZE SELECT count(*) FROM m, n WHERE n.id = m.id;

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                   QUERY PLAN                                                                   │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Aggregate  (cost=802469.79..802469.80 rows=1 width=0) (actual time=12833.559..12833.559 rows=1 loops=1)                                        │
│   ->  Merge Join  (cost=65.50..777470.14 rows=9999860 width=0) (actual time=1.054..11776.011 rows=10000000 loops=1)                            │
│         Merge Cond: (m.id = n.id)                                                                                                              │
│         ->  Index Only Scan using m_pkey on m  (cost=0.43..313740.34 rows=9999860 width=8) (actual time=0.083..3105.649 rows=10000000 loops=1) │
│               Heap Fetches: 10000000                                                                                                           │
│         ->  Index Only Scan using n_pkey on n  (cost=0.43..313740.34 rows=9999860 width=8) (actual time=0.036..3160.630 rows=10000000 loops=1) │
│               Heap Fetches: 10000000                                                                                                           │
│ Planning time: 0.413 ms                                                                                                                        │
│ Execution time: 12833.617 ms                                                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘