From: D. Dante Lorenso <dante@lorenso.com>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexing a Boolean or Null column?
Date: Sat, 03 Jan 2004 23:24:37 -0600
Message-ID: <3FF7A395.4050106@lorenso.com> (raw)
In-Reply-To: <3FF79764.6060805@familyhealth.com.au>
References: <3FF769EA.7090508@lorenso.com>
<3FF79764.6060805@familyhealth.com.au>
Christopher Kings-Lynne wrote:
> > Will an index on the 'data_is_active' column be used or work
> > as I expect? I'm assuming that I may have a million entries
> > sharing the same 'data_lookup_key' and I'll be using that to
> > search for the active version of the row.
> An index just on a boolean column won't be 'selective enough'.
> eg. The index will only be able to choose 50% of the table -
> since it's faster to do a full table scan in that case, the
> index won't get used.
Ok, so ...evenly distributed data on small set of values forces
sequential scan since that's faster. I expected that based on
what I've read so far.
> A multi keyed index, however will work a bit better, eg an index
> over (data_lookup_key, data_is_active).
>
> That way, the index will first be able to find the correct
> key (which is nicely selective) and then will be able to
> halve the resulting ? search space to get the active ones.
I'm not using the 50% TRUE / 50% FALSE model. My model will be
more like only ONE value IS TRUE for 'is_active' for each
'data_lookup_key' in my table. All the rest are FALSE. In
this case for 100 rows all having the same 'data_lookup_key'
we are looking at a 99% FALSE / 1% TRUE model ... and what I'll
be searching for is the ONE TRUE.
In this case, it WILL pay off to have the index on a boolean
column, yes? Will I win my debate with my collegue? ;-)
I think Tom Lanes suggestion of partial indexes is what I need to
look into.
> BTW, you shouldn't use 'banana', 'pear', etc as the data_lookup_key,
> you should make another table like this: ... And then replace the
> data_lookup_key col with a column of integers that is a foreign
> key to the names table - waaaaay faster to process.
Gotcha, yeah, I'm targeting as close to 3NF as I get get. Was just
trying to be generic for my example ... bad example, oops.
Dante
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: dante@lorenso.com, chriskl@familyhealth.com.au
Subject: Re: Indexing a Boolean or Null column?
In-Reply-To: <3FF7A395.4050106@lorenso.com>
* 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