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.