Using index for bitwise operations?

From: Shaul Dar <shauldar(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Using index for bitwise operations?
Date: 2009-06-01 15:46:22
Message-ID: 234efe30906010846p487cd7b0rb32b85184e18572d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I have at column that is a bit array of 16, each bit specifying if a certain
property, out of 16, is present or not. Our typical query select 300
"random" rows (could be located in different blocks) from the table based on
another column+index, and then filters them down to ~50 based on this the
bit field. Currently we have 16 separate indexes built on each bit, and on
our 25M rows table each index takes about 880MB for a total of 14GB! I would
have liked to change this into a single short integer value with a single
index, but I don't know if there is a way to search if specific bits are
set, using a single index? W/o an index this might be overly expensive,
even as a filter (on selected 300 rows).

(I also saw the thread
http://archives.postgresql.org/pgsql-performance/2007-09/msg00283.php. As I
said we are currently using the same multiple index "solution" described in
http://archives.postgresql.org/pgsql-performance/2007-09/msg00283.php) Any
suggestions?

Thanks!

-- Shaul (Email: info(at)shauldar(dot)com)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Peter Sheats 2009-06-01 15:55:53 Best way to load test a postgresql server
Previous Message Greg Smith 2009-06-01 15:34:53 Re: Vacuuming technique doubt