Re: Index Skip Scan

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
Cc: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, florisvannee(at)optiver(dot)com, pg(at)bowt(dot)ie, jesper(dot)pedersen(at)redhat(dot)com, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, jtc331(at)gmail(dot)com, rafia(dot)pghackers(at)gmail(dot)com, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, bhushan(dot)uparkar(at)gmail(dot)com, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Subject: Re: Index Skip Scan
Date: 2020-03-12 02:08:18
Message-ID: CAApHDvoJk45vpBs97mAzu_dZqgwpdbP7eavYPrQK=QYHaRJ1zw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, 11 Mar 2020 at 16:44, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>>
>>
>> I think the UniqueKeys may need to be changed from using
>> EquivalenceClasses to use Exprs instead.
>
>
> When I try to understand why UniqueKeys needs EquivalenceClasses,
> see your comments here. I feel that FuncExpr can't be
> used to as a UniquePath even we can create unique index on f(a)
> and f->strict == true. The reason is even we know a is not null,
> f->strict = true. it is still be possible that f(a) == null. unique index
> allows more than 1 null values. so shall we move further to use varattrno
> instead of Expr? if so, we can also use a list of Bitmapset to present multi
> unique path of a single RelOptInfo.

We do need some method to determine if NULL values are possible. At
the base relation level that can probably be done by checking NOT NULL
constraints and strict base quals. At higher levels, we can use strict
join quals as proofs.

As for bit a Bitmapset of varattnos, that would certainly work well at
the base relation level when there are no unique expression indexes,
but it's not so simple with join relations when the varattnos only
mean something when you know which base relation it comes from. I'm
not saying that Lists of Exprs is ideal, but I think trying to
optimise some code that does not yet exist is premature.

There was some other talk in [1] on how we might make checking if a
List contains a given Node. That could be advantageous in a few
places in the query planner, and it might be useful for this too.

[1] https://www.postgresql.org/message-id/CAKJS1f8v-fUG8YpaAGj309ZuALo3aEk7f6cqMHr_AVwz1fKXug%40mail.gmail.com

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Wong 2020-03-12 02:21:08 Re: GSOC 2020 - Develop Performance Farm Benchmarks and Website (2020)
Previous Message Tom Lane 2020-03-12 00:34:28 Re: v13 latest snapshot build error