On 14/08/2019 20:32, Ashwin Agrawal wrote:
> On Wed, Aug 14, 2019 at 2:51 AM Ashutosh Sharma wrote:
>> 2) Is there a chance that IndexOnlyScan would ever be required for
>> zedstore tables considering the design approach taken for it?
>
> We have not given much thought to IndexOnlyScans so far. But I think
> IndexOnlyScan definitely would be beneficial for zedstore as
> well. Even for normal index scans as well, fetching as many columns
> possible from Index itself and only getting rest of required columns
> from the table would be good for zedstore. It would help to further
> cut down IO. Ideally, for visibility checking only TidTree needs to be
> scanned and visibility checked with the same, so the cost of checking
> is much lower compared to heap (if VM can't be consulted) but still is
> a cost. Also, with vacuum, if UNDO log gets trimmed, the visibility
> checks are pretty cheap. Still given all that, having VM type thing to
> optimize the same further would help.
Hmm, yeah. An index-only scan on a zedstore table could perform the "VM
checks" by checking the TID tree in the zedstore. It's not as compact as
the 2 bits per TID in the heapam's visibility map, but it's pretty good.
>> Further, I tried creating a zedstore table with btree index on one of
>> it's column and loaded around 50 lacs record into the table. When the
>> indexed column was scanned (with enable_seqscan flag set to off), it
>> went for IndexOnlyScan and that took around 15-20 times more than it
>> would take for IndexOnly Scan on heap table just because IndexOnlyScan
>> in zedstore always goes to heap as the visibility check fails.
Currently, an index-only scan on zedstore should be pretty much the same
speed as a regular index scan. All the visibility checks will fail, and
you end up fetching every row from the table, just like a regular index
scan. So I think what you're seeing is that the index fetches on a
zedstore table is much slower than on heap.
Ideally, on a column store the index fetches would only fetch the needed
columns, but I don't think that's been implemented yet, so all the
columns are fetched. That can make a big difference, if you have a wide
table with lots of columns, but only actually need a few of them. Was
your test case something like that?
We haven't spent much effort on optimizing index fetches yet, so I hope
there's many other little tweaks there as well, that we can do to make
it faster.
- Heikki