Discussed in Pg-hackers, always replace SeqScan with vectorized CustomScan may not always make sense, especially for OLTP queries.
It's better to introduce cost to determine whether to replace.
Here are the discussion details.
Konstantin wrote:
4. As far as I understand you now always try to replace SeqScan with your custom vectorized scan. But it makes sense only if there are quals for this scan or aggregation is performed.
In other cases batch+unbatch just adds extra overhead, doesn't it?
Hubert wrote:
Probably extra overhead for heap format and query like 'select i from t;' without qual, projection, aggregation.
But with column store, VectorScan could directly read batch, and no additional batch cost. Column store is the better choice for OLAP queries.
Konstantin wrote:
Generally, yes.
But will it be true for the query with a lot of joins?
select * from T1 join T2 on (T1.pk=T2.fk) join T3 on (T2.pk=T3.fk) join T4 ...
How can batching improve performance in this case?
Also if query contains LIMIT clause or cursors, then batching can cause fetching of useless records (which never will be requested by client).
Discussed in Pg-hackers, always replace SeqScan with vectorized CustomScan may not always make sense, especially for OLTP queries. It's better to introduce cost to determine whether to replace.
Here are the discussion details.