indexes and unique constraints

postgres.sql

Create the table with the unique constraint

    testdb=# CREATE TABLE "main_a_b" (
        "a_id" integer,
        "b_id" integer,
        UNIQUE ("a_id", "b_id")
    );
    CREATE TABLE

Conditional query on first column

    testdb=# EXPLAIN Select a_id from main_a_b where a_id = 1;
                                          
                                          QUERY PLAN                                      
    --------------------------------------------------------------------------------------
     Bitmap Heap Scan on main_a_b  (cost=4.24..14.91 rows=11 width=4)
       Recheck Cond: (a_id = 1)
       ->  Bitmap Index Scan on main_a_b_a_id_b_id_key  (cost=0.00..4.24 rows=11 width=0)
             Index Cond: (a_id = 1)
    (4 rows)

Create an explicit index, try again

    testdb=# CREATE INDEX test_indx ON main_a_b (a_id);
    CREATE INDEX

    testdb=# EXPLAIN Select a_id from main_a_b where a_id = 1;
                                   
                                   QUERY PLAN                                
    -------------------------------------------------------------------------
     Bitmap Heap Scan on main_a_b  (cost=4.24..14.91 rows=11 width=4)
       Recheck Cond: (a_id = 1)
       ->  Bitmap Index Scan on test_indx  (cost=0.00..4.24 rows=11 width=0)
             Index Cond: (a_id = 1)
    (4 rows)

Identical plans, identical costs.

sqlite.md

See SQLite create table documentation:

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.

In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the following schemas are logically equivalent:

1. CREATE TABLE t1(a, b UNIQUE);
2. CREATE TABLE t1(a, b PRIMARY KEY);
3. CREATE TABLE t1(a, b); CREATE UNIQUE INDEX t1b ON t1(b);