Postgresql – Postgres: extracting multiple bit-columns from single ‘flags’ column after join with 2nd table

bitwise-comparisonoptimizationpostgresqltemporary-tables

I have a query similar to this below. As you can see, there are multiple parts which are very similar.

SELECT 
    id, 
    aaaa, 
    bbbb,
    tags
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 1 <> 0
    ) as "has_children_tag_1"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 2 <> 0
    ) as "has_children_tag_2"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 4 <> 0
    ) as "has_children_tag_3"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B 
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 8 <> 0
    ) as "has_children_tag_4"
FROM A
LEFT JOIN Z
    ON A.id = Z.id

I know one thing, that I cannot split flag column into multiple bit columns, so I have to figure out how to query my existing tables in efficient way.

I am wondering is it possible (I don't know how to do that) to create some kind of temporary table for part:

    SELECT COUNT(*) > 0
    FROM A
    LEFT JOIN B 
        ON A.id = B.parent_id
    WHERE A.buzz=C.buzz

to have a possibility to run on this tmp table WHERE B.tags & 1 <> 0, WHERE B.tags & 2 <> 0, etc.

Any help/suggestion will be appreciated.

Best Answer

You could merge the subqueries using this model:

SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions]

bool_or is an aggregate function that evaluates to true if at least one value is true, so that should lead to the same results as the multiple count(*)>0 in the question, except as one single subquery.

To push these columns into an otherwise unrelated query, you may add the above subquery as a CTE in a WITH clause, and just put it in the FROM list. For instance, modifying your original query:

WITH flags AS
(SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions] )
SELECT 
  id, 
  aaaa, 
  bbbb,
  tags,
  has_children_tag_1,
  has_children_tag_2,
  has_children_tag_3,
  has_children_tag_4
FROM flags,A
LEFT JOIN Z
    ON A.id = Z.id
Related Question