Dawid Kuroczko <qnex42@xxxxxxxxx> writes: > Don't worry about "index bloat". These additional indexes will be used > only when your main (foo_abc_index) is not used, so there won't be > any duplicate data in them. The main index will have _all_ the tuples in them, even where some of the columns are NULL, so this will in fact use extra space. It will also cause extra i/o on every update of a record with NULL in one of the columns. To minimize the extra space you could make it Dawid Kuroczko <qnex42@xxxxxxxxx> writes: CREATE UNIQUE INDEX foo_ab_index ON foo (a,b) WHERE c IS NULL AND a is NOT NULL and b IS NOT NULL; CREATE UNIQUE INDEX foo_ac_index ON foo (a,c) WHERE b IS NULL AND a is NOT NULL and c IS NOT NULL; CREATE UNIQUE INDEX foo_bc_index ON foo (b,c) WHERE a IS NULL AND b is NOT NULL and c IS NOT NULL; CREATE UNIQUE INDEX foo_a_index ON foo (a) WHERE a IS NOT NULL AND b IS NULL and c is NULL; CREATE UNIQUE INDEX foo_b_index ON foo (b) WHERE b IS NOT NULL AND a IS NULL and c is NULL; CREATE UNIQUE INDEX foo_c_index ON foo (c) WHERE c IS NOT NULL AND a IS NULL and b is NULL; To avoid indexing the same tuples in multiple indexes. None of this will prevent you from inserting multiple <null,null,null> records though. -- greg ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
