Why Columnar tables, can't use chunk groups filtering on Join-s

[BezBartek]

Hello,

Example to illustrate issue:
Let's create columnar and regular tables, with the same identifiers.

create table columnar (
        identifier bigint not null,
    	column2 varchar not null
) USING columnar;

and simple regular table:

create table regular (
	identifier serial primary key
);

Let's populate them:

INSERT INTO columnar(identifier, column2)
SELECT identifier, 'Some random strings'
FROM generate_series(1,1000000) identifier;

INSERT INTO regular(identifier)
SELECT identifier
FROM generate_series(1,1000000) identifier;

As you can see, regular identifiers and columnar identifiers matches.
Now let's try to select the second column directly from the columnar table.

EXPLAIN ANALYSE SELECT columnar.column2
                FROM columnar
WHERE columnar.identifier = 999 or columnar.identifier = 900000;

This one nicely uses chunk groups filtering, and generates plan like this:

Custom Scan (ColumnarScan) on columnar  (cost=0.00..22.39 rows=9975 width=32) (actual time=1.245..3.473 rows=2 loops=1)
  Filter: ((identifier = 999) OR (identifier = 900000))
  Rows Removed by Filter: 19998
"  Columnar Projected Columns: identifier, column2"
  Columnar Chunk Group Filters: ((identifier = 999) OR (identifier = 900000))
  Columnar Chunk Groups Removed by Filter: 98
Planning Time: 0.329 ms
Execution Time: 3.496 ms

But fetching those records through join, with logically same condition, doing full table scan:

EXPLAIN ANALYSE SELECT columnar.column2
                FROM columnar JOIN regular on regular.identifier = columnar.identifier
WHERE regular.identifier = 999 or regular.identifier = 900000;

Hash Join  (cost=16.79..2798.50 rows=2 width=32) (actual time=1.407..118.409 rows=2 loops=1)
  Hash Cond: (columnar.identifier = regular.identifier)
  ->  Custom Scan (ColumnarScan) on columnar  (cost=0.00..156.70 rows=1000000 width=40) (actual time=1.084..82.798 rows=1000000 loops=1)
"        Columnar Projected Columns: identifier, column2"
  ->  Hash  (cost=16.77..16.77 rows=2 width=4) (actual time=0.036..0.037 rows=2 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Bitmap Heap Scan on regular  (cost=8.87..16.77 rows=2 width=4) (actual time=0.028..0.033 rows=2 loops=1)
              Recheck Cond: ((identifier = 999) OR (identifier = 900000))
              Heap Blocks: exact=2
              ->  BitmapOr  (cost=8.87..8.87 rows=2 width=0) (actual time=0.021..0.022 rows=0 loops=1)
                    ->  Bitmap Index Scan on regular_pkey  (cost=0.00..4.43 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)
                          Index Cond: (identifier = 999)
                    ->  Bitmap Index Scan on regular_pkey  (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: (identifier = 900000)
Planning Time: 0.898 ms
Execution Time: 118.461 ms

What is the reason? Because of that behavior, I will not use the feature, in the place, where it seems to me like a game changer ;/.

Thank you for explanation!

[JamesCaldon]

We've also run into a similar issue, albeit with a join between 2+ columnar tables.
We found that disabling hash joins in our case caused the query planner to use nested loops which did indeed use chunk group filtering properly. We found this behaviour by looking at the tests: columnar_chunk_filtering.sql

We couldn't find this stated anywhere in the documentation, so finding this was non-intuitive.
It would be ideal if the query planner could automatically select this as the approach, or if chunk group filtering could still be done when a hash join is used.

SET enable_mergejoin = false;
SET enable_hashjoin = false;
SET enable_material = false;