Using index for bitwise operations?
От | Shaul Dar |
---|---|
Тема | Using index for bitwise operations? |
Дата | |
Msg-id | 234efe30906010846p487cd7b0rb32b85184e18572d@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Using index for bitwise operations?
Re: Using index for bitwise operations? Re: Using index for bitwise operations? |
Список | 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@shauldar.com)
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@shauldar.com)
В списке pgsql-performance по дате отправления: