Skip indexes are not in use

[vmalyutin]

Hey, I am trying to tame columnar store in our enterprise solution and faced this behaviour

select org, sum(saleit), sum(costit) from sale_agg_cs_top where dt = '2021-01-01'::date and it = 0 group by org

execution plan would be

GroupAggregate (cost=214852.39..214859.59 rows=270 width=68) (actual time=596.516..597.755 rows=981 loops=1) Group Key: org Buffers: shared hit=22080 -> Sort (cost=214852.39..214853.17 rows=315 width=13) (actual time=596.500..596.593 rows=995 loops=1) Sort Key: org Sort Method: quicksort Memory: 71kB Buffers: shared hit=22080 -> Foreign Scan on sale_agg_cs_top (cost=0.00..214839.32 rows=315 width=13) (actual time=1.652..595.635 rows=995 loops=1) Filter: ((dt = '2021-01-01'::date) AND (it = 0)) Rows Removed by Filter: 4362836 CStore File: /var/lib/pgsql/10_5432/data/cstore_fdw/16409/197166589 CStore File Size: 263228026 Buffers: shared hit=22080 Planning time: 0.126 ms Execution time: 598.100 ms

but when I involve a join

with fact as ( select org, sum(saleit), sum(costit) from sale_agg_cs_top where dt = '2021-01-01'::date and it = 0 group by org ) select f.org, ka."JustDescription", f.* from fact as f join "TableDescription" as ka on ka.org = f.org

I encounter a great degradation

Nested Loop (cost=214859.86..215160.24 rows=270 width=101) (actual time=1799.279..1804.731 rows=981 loops=1) Buffers: shared hit=25023 CTE fact -> GroupAggregate (cost=214852.39..214859.59 rows=270 width=68) (actual time=1799.247..1800.919 rows=981 loops=1) Group Key: sale_agg_cs_top.org Buffers: shared hit=22080 -> Sort (cost=214852.39..214853.17 rows=315 width=13) (actual time=1799.213..1799.333 rows=995 loops=1) Sort Key: sale_agg_cs_top.org Sort Method: quicksort Memory: 71kB Buffers: shared hit=22080 -> Foreign Scan on sale_agg_cs_top (cost=0.00..214839.32 rows=315 width=13) (actual time=6.196..1798.565 rows=995 loops=1) Filter: ((dt = '2021-01-01'::date) AND (it = 0)) Rows Removed by Filter: 13497703 CStore File: /var/lib/pgsql/10_5432/data/cstore_fdw/16409/197166589 CStore File Size: 263228026 Buffers: shared hit=22080 -> CTE Scan on fact f (cost=0.00..5.40 rows=270 width=68) (actual time=1799.253..1801.392 rows=981 loops=1) Buffers: shared hit=22080 -> Index Scan using "pКонтрагент" on "Контрагент" ka (cost=0.28..1.09 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=981) Index Cond: ("@Лицо" = f.org) Buffers: shared hit=2943 Planning time: 1.960 ms Execution time: 1805.475 ms

As you can see first time it scaned 4362836 and second time 13497703. The table sale_agg_cs_top has 13498698 rows. That's why I think skip indexes are not in use. All tables were analyzed.

Versions PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit cstore_fdw 1.7 CentOS 7.7.1908

[mtuncer]

Citus extension has recently started supporting columnar tables please see https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-compression-for-postgres

It uses PostgreSQL's recent table access method features and have better performance than the architecture cstore_fdw was build on.

We strongly recommend switching to citus columnar tables.

[vmalyutin]

Murat,  Thank you very much. I really appreciate it.

Will try cirus extension out when our Postgres is upgrated.

Best regards.

On April 27, 2021 at 12:46 AM, Murat Tuncer @.***> wrote:

Citus extension has recently started supporting columnar tables please see https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-compression-for-postgres

It uses PostgreSQL's recent table access method features and have better performance than the architecture cstore_fdw was build on.

We strongly recommend switching to citus columnar tables.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.