Re: Index ignored on column containing mostly 0 values - Mailing list pgsql-performance

From Tom Lane
Subject Re: Index ignored on column containing mostly 0 values
Date
Msg-id 23894.1162269629@sss.pgh.pa.us
Whole thread Raw
In response to Index ignored on column containing mostly 0 values  (Leif Mortenson <leiflists@tanukisoftware.com>)
List pgsql-performance
Leif Mortenson <leiflists@tanukisoftware.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

pgsql-performance by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: Index ignored on column containing mostly 0 values
Next
From: "soni de"
Date:
Subject: Re: client crashes in PQfinish