pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (8+ messages)  latest in thread

Message-ID: <3FF7A395.4050106@lorenso.com>
Permalink:  ../3FF7A395.4050106@lorenso.com/
Also on:    postgresql.org/message-id/3FF7A395.4050106@lorenso.com

 · 

reply

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