Postgresql – How to one make a partial index to optimize a query for a single value

postgresql

Let's say I have table foos with column thing_id.

What's the smallest index I could make for these queries?

select count(*) from foos where thing_id is null;
select id from foos where thing_id is null;

Conceptually, we just need the index to keep track of a list of primary keys which match the criteria. Is it possible/useful to create a partial index on… no columns?

I tried this and postgres considered it a syntax error:

create index on foos where thing_id is null;

This did work

create index on foos (thing_id) where thing_id is null;

But will this result in unnecessarily writing the value of thing_id (always NULL) for each row?

Best Answer

You always need a column in an index.

You could try to index a constant that occupies only one byte:

SELECT typname FROM pg_type WHERE typlen = 1;

 typname 
---------
 bool
 char
(2 rows)

If you choose boolean, that would be

CREATE INDEX ON foos ((TRUE)) WHERE thing_id IS NULL;

But my recommendation is to index id. Sure, that would make the index somewhat bigger, but your second query could use a much faster index-only scan.

Related Solutions

PostgreSQL – Creating Partial Unique Constraint Spanning Multiple Tables

Not an answer for postgres, but may be useful nevertheless to someone using Oracle who stumbles across this:

Oracle allows partial uniqueness to be enforced across multiple tables using a fast refreshing materialised view. Tom Kyte describes it here. In short, if a join produces any rows on commit, it violates a constraint on the materialised view.

Untested, but in principle it should work like so:

create materialized view log
  on possession with rowid;

create materialized view log
  on toy with rowid;

create materialized view one_special_toy_per_owner
  refresh fast
  on commit
  as select p.owner_id, count(t.special) as special
     from possession p, toy t
     where p.possession_id = t.possession_id
     group by p.owner_id
     having count(t.special) > 1;

alter table one_special_toy_per_owner
  add constraint owner_has_only_one_special_toy
  check (owner_id is null and special is null);

PostgreSQL – Why Seq-Scan Can Be Faster Than Index-Scan

This is quite a common query (pardon the pun! :-) ) from people running queries which perform full table scans (FTS), when the poster feels that the system should make use of the index(es).

Basically, it boils down to the explanation given here. If the tables are so small, the optimiser will say that "it's not worth the bother of going to the index, doing a lookup and then fetching the data, instead, I'll just slurp in all the data and pick out what I need", i.e. perform an FTS.

[EDIT in answer to @txsing's comment]

For an MVCC (multi-version concurrency control) database, you have to traverse every record for a count at a given moment - that's why, for example, a COUNT(*) is much more expensive for MySQL's InnoDB rather than MyISAM.

An excellent explantion (for PostgreSQL) is available here. The guy who wrote this post is a "major contributor" to PostgreSQL (thanks to @dezso for leading me to that post).

Best Answer

Related Solutions

PostgreSQL – Creating Partial Unique Constraint Spanning Multiple Tables

PostgreSQL – Why Seq-Scan Can Be Faster Than Index-Scan

Related Question