Hi,
I think it's not bad approach if performance is important. I don't know
how b-tree index will work with bitset datatype, but I assume it should
treat is as 256bit number (maybe someone more competive in internals
will answer this).
Please bear in mind, that this approach will work well only on query
You have written.
Because You ask on performance, I will add this topic You may want to
test and think about it
PG by default uses text transfer mode, so if you transfer your data
from/to server those will be transferred as 256 0/1 character string.
You may to think about storing tags as e.g. 4 long (64bit) fields, or 2
type 4 UUIDs (128bit) and use composite index. If you have ability to
use binary transfer and on your client side bitest will be mapped to
some "reasonable" type, then You won, otherwise (in binary mode) you
should get nice boost when you will store, those values in types I have
wrote.
Of course those are only some concepts, personally I have never made
such things.
Regards,
Radek
On Wed, 14 Sep 2011 17:58:58 +0200, Antonio Vieiro wrote:
Hi again,
Thanks for the tip. In fact I was thinking of creating an index on
the bitmask, so I could use:
... where t.bits = :mymask
directly, avoiding a full table scan. I assume this is possible
(indexing bit and comparing bits), isn't it?
Thanks,
Antonio
El 14/09/11 15:58, Radosław Smogura escribió:
On Wed, 14 Sep 2011 12:00:35 +0200, Antonio Vieiro wrote:
Hi all,
One of my entities 'E' may be 'tagged' with an arbitrary set of 256
tags 'T'.
A first approach could be to add a M:N relationship between 'E' and
'T'.
A second way to do this could be to add a BIT(256) datatype to 'E',
setting bits to '1' if the entity is tagged with each one of the
256
tags (i.e. using a 'bitmask' on the set of tags).
Since querying entities 'E' with a certain set of tags 'T' must be
very fast I was wondering if the second approach would be faster.
What
do you think?
Thanks for any hints,
Antonio
I assume each entity may have one or more different tags.
Actually performing test like
... where (t.bits & :mymask) = :mymask
should be quite fast and faster then creating additional relations,
but
only if it's highly probable that your query will almost always scan
whole table.
The advantage of indexes is that the index is used 1st and tail
(slower)
parts of query will always get "subset" of table. In bitset, You
will
probably scan whole table.
So I think, you should do some performance test for large number of
data, and compare both ways. I think bitset will be fast for really
small data, but M:N relations may be faster for really large data
sets.
You need to measure size of your database too, in M:N case with 256
tags
it may be quite large.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
