From: Tom Lane <tgl@sss.pgh.pa.us>
To: Leif Mortenson <leiflists@tanukisoftware.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: Index ignored on column containing mostly 0 values
Date: Mon, 30 Oct 2006 23:40:29 -0500
Message-ID: <23894.1162269629@sss.pgh.pa.us> (raw)
In-Reply-To: <4546CB3C.4020206@tanukisoftware.com>
References: <4546CB3C.4020206@tanukisoftware.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, leiflists@tanukisoftware.com
Subject: Re: Index ignored on column containing mostly 0 values
In-Reply-To: <23894.1162269629@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox