From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Shaul Dar <shauldar(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Using index for bitwise operations? |
Date: | 2009-06-01 16:18:15 |
Message-ID: | 18855.1243873095@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Shaul Dar <shauldar(at)gmail(dot)com> writes:
> 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!
Ouch. One possibility is to replace the bitarray with an integer array
(k is in the int[] array iff bit k was set in the bitarray) and then use
the GIST or GIN indexing capabilities of contrib/intarray. I also seem
to recall having seen a module that provides GIST indexing for bitops
on plain integers --- have you looked on pgfoundry?
This isn't necessarily better than what you're doing, as btree indexes
are a lot better optimized than GIST/GIN. But it would be worth looking
into.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Віталій Тимчишин | 2009-06-01 16:19:12 | Re: Very inefficient query plan with disjunction in WHERE clause |
Previous Message | Richard Huxton | 2009-06-01 16:17:14 | Re: Using index for bitwise operations? |