jsonb index

index.ts

import pg from 'pg';

async function main() {
  const client = new pg.Client({
    host: '127.0.0.1',
    port: 5432,
    user: 'username',
    password: 'password',
    database: 'test'
  });

  await client.connect();

  await client.query('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
  await client.query('DROP TABLE IF EXISTS test');
  await client.query('CREATE TABLE test (id SERIAL PRIMARY KEY, new jsonb, prev jsonb, created_at timestamp, updated_at timestamp DEFAULT now())');
  await client.query(`CREATE INDEX test_idx ON test USING btree ((new->>'id'))`);
  await client.query(`CREATE INDEX test_idx2 ON test USING btree ((new->>'short_id'))`);
  await client.query(`CREATE INDEX test_idx3 ON test USING btree (created_at DESC)`);
  await client.query(`
    INSERT INTO test (new, prev) 
    SELECT 
      jsonb_build_object(
        'id', i % 100,
        'short_id', '000-' || i,
        'name', 'name-' || i,
        'title', 'title-' || i,
        'created_at', now()::timestamp,
        'updated_at', now()::timestamp,
        'courier_id', 'courier-' || i
      ) as new,
      jsonb_build_object(
        'id', i % 100,
        'short_id', '000-' || i,
        'name', 'name-' || i,
        'title', 'title-' || i,
        'created_at', now()::timestamp,
        'updated_at', now()::timestamp,
        'courier_id', 'courier-' || i
      ) as prev
    FROM generate_series(1, 1000000) AS i
  `);

  await printSize('test');
  await printSize('test_idx');
  await printSize('test_idx2');
  await printSize('test_idx3');

  const now = performance.now();
  const { rows } = await client.query(`
    SELECT new FROM test
    WHERE new->>'short_id' = $1
    ORDER BY created_at DESC LIMIT 1
  `, ['000-' + 3]);
  console.log(`Query took ${performance.now() - now}ms`);
  console.table(rows.length);

  await client.end();

  /**
   * Prints the size of a database relation.
   * @param relation - The name of the relation.
   */
  async function printSize(relation: string) {
    const result = await client.query(`SELECT pg_size_pretty(pg_relation_size('${relation}'))`);
    console.log(`"${relation}" size: ${result.rows[0].pg_size_pretty}`);
  }
}

main();