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@xxxxxxxxxxxx)
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@xxxxxxxxxxxx)
