Re: Index ignored on column containing mostly 0 values

Leif Mortenson <leiflists(at)tanukisoftware(dot)com> writes:
> Having a column containing large numbers of null or 0 values seems fairly
> common.

You would likely be better off to use NULL as a no-value placeholder,
instead of an arbitrarily chosen regular value (which the planner cannot
be certain does not match any entries in the other table...)

> Is there way to tell Postgres to create an index of all values with
> meaning. Ie all non-0 values? None that I could find.

Partial index. Though I'm not sure that would help here. The problem
is that the nestloop join you want would be spectacularly awful if there
happened to be any zeroes in bars.id, and the planner's statistical
estimates allow some probability of that happening.

regards, tom lane