Greg Stark <greg.stark@enterprisedb.com> wrote:
> Postgres supports a whole lot more scan types than just these two
> and many of them use multiple indexes or indexes that don't
> correspond to ranges of key values at all.
Well, certainly all of the plans I've looked at which use btree
indexes could be handled this way. The fact that the index is scanned
as part of a bitmap process doesn't affect the logic at all, as far as
I can see. Do you see something I'm missing in regard to the btree
indexes?
In regard to other index types, if there is no way to note a GIN scan
such as Index Cond: (text_tsv @@ '''amicus'' & ''brief'''::tsquery)
in a way that can be compared to DML operations, well, that just
means that some other type of lock would have to be used which is
broad enough to cover it. A table lock certainly would. In this
case, a column lock would be more precise and less likely to generate
false positives, so perhaps that will be found to be needed in the
tuning phase.
Although, looking at it, I would think that a predicate lock on a
condition such as this could be tested by seeing if there is a
difference in the truth of the test between "before" and "after"
images. That may well be naive, but I doubt that we've exhausted the
possibilities yet.
> I think you have to forget about any connection between predicates
> and either indexes or scan types. You need a way to represent
> predicates which can be stored and looked up independently of any
> indexes.
Sure. Heap or index pages, tables, columns, table segments -- there
are many options. We can clearly get correct behavior; the question
is about how best to tune it. That's why I was leaning toward an
initial "correct but crude" implementation, building up a large set of
tests for correctness, and then trying different approaches to
balancing the overhead of more accurate tracking against the cost of
dealing with transaction restarts.
> Without any real way to represent predicates this is all pie in the
> sky
And this is 180% opposite from what I just heard at PGCon should be
the focus of discussion at this point. Let's get agreement on what
would be nice user-facing behavior first. You can always critique
implementation suggestions later. Although, looking back I guess I
provoked this by lapsing into thoughts about an implementation path,
so I guess this one's on me. Apologies.
I tend to believe that if Microsoft can handle this, the PostgreSQL
developer community can get there, too -- even if we do have fancier
indexes.
-Kevin