To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: SQL Query Performance - what gives?
Date: Wed, 19 Aug 2009 12:19:17 +0200
Lines: 23
Message-ID: <h6gjj5$tfo$1@ger.gmane.org>
References: <4A8B04C7.6060702@denninger.net>
	<4A8AC75C0200002500029C8A@gw.wicourts.gov>
	<4A8B1606.2040209@denninger.net>
	<4A8ADDEA0200002500029CA3@gw.wicourts.gov>
	<4A8B334F.1010801@denninger.net> <9800.1250647338@sss.pgh.pa.us>
	<4A8B67DD.2020608@denninger.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.22 (X11/20090817)
In-Reply-To: <4A8B67DD.2020608@denninger.net>
Cc: pgsql-sql@postgresql.org

Karl Denninger wrote:

> The bitmask allows the setting of multiple permissions but the table
> definition doesn't have to change (well, so long as the bits fit into a
> word!)  Finally, this is a message forum - the actual code itself is
> template-driven and the bitmask permission structure is ALL OVER the
> templates; getting that out of there would be a really nasty rewrite,
> not to mention breaking the user (non-developer, but owner)
> extensibility of the current structure.
> 
> Is there a way to TELL the planner how to deal with this, even if it
> makes the SQL non-portable or is a hack on the source mandatory?

You could maybe create function indexes for common bitmap operations; 
for example if it's common to check a single bit you could create 32 
indexes, on (field & 1), (field & 2), (field & 4), etc. You could also 
maybe extend this so if you need to query multiple bits you decompose 
them into individual single-bit queries, e.g. instead of (field & 3) you 
do ((field & 1) and (field & 2)).

I suppose there will be a break-even point in complexity before which 
the above approach will be very slow but after it it should scale better 
then the alternative.