Re: Index Skip Scan

On Mon, Apr 6, 2020 at 1:14 PM Floris Van Nee <florisvannee(at)optiver(dot)com> wrote:
>
> > > > On Sun, Apr 05, 2020 at 04:30:51PM +0530, Dilip Kumar wrote:
> > > >
> > > > I was just wondering how the distinct will work with the "skip scan"
> > > > if we have some filter? I mean every time we select the unique row
> > > > based on the prefix key and that might get rejected by an external
> > > > filter right?
> > >
>
> Yeah, you're correct. This patch only handles the index conditions and doesn't handle any filters correctly. There's a check in the planner for the IndexScan for example that only columns that exist in the index are used. However, this check is not sufficient as your example shows. There's a number of ways we can force a 'filter' rather than an 'index condition' and still choose a skip scan (WHERE b!=0 is another one I think). This leads to incorrect query results.

Right

> This patch would need some logic in the planner to never choose the skip scan in these cases. Better long-term solution is to adapt the rest of the executor to work correctly in the cases of external filters (this ties in with the previous visibility discussion as well, as that's basically also an external filter, although a special case).

I agree

> In the patch I posted a week ago these cases are all handled correctly, as it introduces this extra logic in the Executor.

Okay, So I think we can merge those fixes in Dmitry's patch set.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com