Re: SQL Query Performance - what gives?

От: Ivan Voras
Тема: Re: SQL Query Performance - what gives?
Дата: ,
Msg-id: h6gjj5$tfo$1@ger.gmane.org
(см: обсуждение, исходный текст)
Ответ на: Re: [SQL] SQL Query Performance - what gives?  (Karl Denninger)
Список: pgsql-performance

Скрыть дерево обсуждения

SQL Query Performance - what gives?  (Karl Denninger, )
 Re: SQL Query Performance - what gives?  ("Kevin Grittner", )
  Re: SQL Query Performance - what gives?  (Karl Denninger, )
   Re: SQL Query Performance - what gives?  ("Kevin Grittner", )
    Re: SQL Query Performance - what gives?  (Karl Denninger, )
     Re: [SQL] SQL Query Performance - what gives?  (Tom Lane, )
      Re: [SQL] SQL Query Performance - what gives?  (Karl Denninger, )
       Re: [SQL] SQL Query Performance - what gives?  (Pierre Frédéric Caillaud<>, )
       Re: SQL Query Performance - what gives?  (Ivan Voras, )

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.


В списке pgsql-performance по дате сообщения:

От: Scott Marlowe
Дата:
Сообщение: Re: PG 8.3 and server load
От: Kevin Kempter
Дата:
Сообщение: Query tuning