Hello everyone.
I need an advice.
I was reworking the patch: added support for the planner, added support for queries with projection, addded support for predicates which could be executed over index data.
And.. I realized that my IndexScan is even more index-only than the original IndexOnlyScan. So, it seems to be a wrong way.
I think the task could be splitted into two:
1. Extended support for index-only-scan
Currently IndexOnlyScan is used only in case when target data is fully located in
index. If we need some additional columns - regular index scan is used anyway.
For example, let's consider such table and index:
CREATE TABLE test_events (
time timestamp ,
event varchar(255),
data jsonb
);
CREATE INDEX on test_events USING btree(time, event);
It is some kind of big table with log events. And let's consinder such query:
SELECT data->>'event_id'
FROM test_events
WHERE
time > (now() - interval '2 year') AND -- indexqual
event = 'tax' AND -- indexqual
extract(ISODOW from time) = 1 --qpquals
ORDER BY time DESC
At the moment IndexScan plan will be used for such query due to result data. But 1/7 of all heap access will be lost. At the same time "extract(ISODOW from time) = 1" (qpqualsl) could be easily calculated over index data.
The idea is simple: extend IndexOnly scan to be used if all query predicates (both indexqual and qpquals) could be calculated over index data. And if all checks are passed - just load tuple from heap to return.
It seems like index-data access is really cheap now and such plan will be faster even for qpquals without high selectivity. At least for READCOMMITED.
I think I will able to create prototype within a few days (most of work is done in current patch rework).
Probably it is not an ne idea - so, is it worth implementation? Maybe I've missed something huge.
2. For extented IndexOnlyScan - add support to avoid heap fetch in case of OFFSET applied to tuple.
If first part is implemented - OFFSET optimisation is much easier to achieve.
Thanks,
Michail.