From: Ivan Voras <ivoras@freebsd.org>
To: pgsql-performance@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Query Performance - what gives?
Date: Wed, 19 Aug 2009 12:19:17 +0200
Message-ID: <h6gjj5$tfo$1@ger.gmane.org> (raw)
In-Reply-To: <4A8B67DD.2020608@denninger.net>
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ivoras@freebsd.org, pgsql-performance@postgresql.org
Subject: Re: SQL Query Performance - what gives?
In-Reply-To: <h6gjj5$tfo$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox