cstore foreignscan is faster than Seqscan，but is slower than bitmapheadscan

[Layamon]

since cstore_fdw has a skiplist index, but can we have a foreign bitmap head scan that is not slower than postgresql bitmap head scan. so we can get a better performence, I am about to do so, is it feasible?

[Layamon]

running a simple sql " select avg(..) from a where .." , the performence of cstore_fdw is much better than with join sql ,like "select avg(..) from a ,b where" , Are we going to implement a foreignjion? or any advice so I can try?

[mtuncer]

Hey @yummyliu I was pretty sure I responded to this one on the same day, but I don't see it.

implementing foreign join would be a bit tricky on cstore_fdw.

You would need to implement join operation between stripes on selected columns. There are There are 2 levels of skip lists on both stripe and block level. You would need to take that into account when performing joins.

One optimization you should do is to prune out stripe joins that are known to be empty before loading stripe data.

You may have memory problems when dealing tables with very large number of columns (1000+) if join result includes all rows.

[Layamon]

thank @mtuncer what do you think of the first question "since cstore_fdw has a skiplist index, but can we have a foreign bitmap head scan that is not slower than postgresql bitmap head scan?"

[mtuncer]

bitmap heap scan is generally performs better when you are fetching large portion of fairly static data set. This looks like a good fit for cstore_fdw.

However, even if you have a bitmap index, you will need to read whole stripe/block data when you are trying to fetch a row. Having a bitmap index will only save you from reading stripe headers from disk. I am not quite sure if performance gains outweighs maintenance effort to keep bitmap index up to date.

As a foreign data wrapper you will need to keep bitmap indexes on the disk all the time. And read from the disk upon each query. You can't (perhaps not supposed to) keep them in memory between queries. There goes another benefit of using bitmap index.

What type of queries do you want to improve with this method ? I can have some recommendations depending on your requirements.