Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

varchar Type segmentby Columns Not Used for Filtering Prior to Decompression #3351

Closed
tylerfontaine opened this issue Jun 21, 2021 · 0 comments · Fixed by #3367
Closed

varchar Type segmentby Columns Not Used for Filtering Prior to Decompression #3351

tylerfontaine opened this issue Jun 21, 2021 · 0 comments · Fixed by #3367

Comments

@tylerfontaine
Copy link
Contributor

Relevant system information:

  • OS: docker
  • PostgreSQL version (output of postgres --version): 13.3
  • TimescaleDB version (output of \dx in psql): 2.3.0
  • Installation method: docker

Describe the bug
segmentby columns of type varchar are not evaluated for exclusion prior to decompression, while segmentby columns of type text are, regardless of casting at query time.

To Reproduce

create table testcasting (a timestamp, b varchar, c text);

select create_hypertable('testcasting', 'a');

insert into testcasting VALUES
(now(), 'a', 'b'),
(now() - interval '1 month', 'c', 'd');

alter table testcasting set (timescaledb.compress, timescaledb.compress_segmentby='b', timescaledb.compress_orderby = 'a');



select show_chunks('testcasting');

select compress_chunk('<some chunk from the above query>');

explain (analyze,buffers) select * from testcasting
where b = 'a'::varchar;

Expected behavior
In the explain, analyze, I would expect something like this, which shows the filter running before compression:

       ->  Custom Scan (DecompressChunk) on _hyper_31_9375_chunk  (cost=1.01..1.01 rows=1000 width=12) (actual time=0.008..0.009 rows=0 loops=1)
        Buffers: shared hit=1
        ->  Seq Scan on compress_hyper_33_9377_chunk  (cost=0.00..1.01 rows=1 width=116) (actual time=0.007..0.007 rows=0 loops=1)
              Filter: (b = 'a'::text)
              Rows Removed by Filter: 1
              Buffers: shared hit=1

Actual behavior
What I see instead is this, showing filtering only happens after decompression:

  ->  Custom Scan (DecompressChunk) on _hyper_31_9375_chunk  (cost=1.01..1.01 rows=1000 width=12) (actual time=0.027..0.028 rows=0 loops=1)
        Filter: ((b)::text = 'a'::text)
        Rows Removed by Filter: 1
        Buffers: shared hit=1
        ->  Seq Scan on compress_hyper_34_9378_chunk  (cost=0.00..1.01 rows=1 width=116) (actual time=0.007..0.008 rows=1 loops=1)
              Buffers: shared hit=1
svenklemm added a commit to svenklemm/timescaledb that referenced this issue Jun 25, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
svenklemm added a commit to svenklemm/timescaledb that referenced this issue Jun 25, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
svenklemm added a commit to svenklemm/timescaledb that referenced this issue Jun 25, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
svenklemm added a commit to svenklemm/timescaledb that referenced this issue Jun 25, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
svenklemm added a commit to svenklemm/timescaledb that referenced this issue Jun 25, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
svenklemm added a commit that referenced this issue Jun 25, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes #3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 29, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 29, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 29, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 29, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 29, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jun 30, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit to mkindahl/timescaledb that referenced this issue Jul 5, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes timescale#3351
mkindahl pushed a commit that referenced this issue Jul 6, 2021
Allow pushdown of RelabelType expressions into the scan below the
DecompressChunk node. When using varchar columns as segmentby
columns constraints on those columns would not be pushed down
because postgres would inject RelabelType nodes that where not
accepted as valid expression for pushdown leading to bad performance
because the filter could only be applied after decompression.

Fixes #3351
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant