Index of expression over table row or column

On Tuesday, October 15, 2024, Steve Lau <stevelauc@outlook.com> wrote:

I am reading this documentation[1], and it has a sentence that I don’t
quite understand: "The index columns (key values) can be either simple
columns of the underlying table or expressions over the table rows.”, I am
thinking that for the index of expressions, aren’t those expressions over
table column?

Agreed.

The description for pg_index.indkey uses the phrasing “an expression over
the table columns” and this should be made to match.

I could maybe argue for a singular row, meaning the expression can
reference any or all of a single row’s columns, but not plural and not with
existing wording using “table columns”.

David J.

Steve Lau <stevelauc@outlook.com> writes:

I am reading this documentation[1], and it has a sentence that I don’t quite understand: "The index columns (key values) can be either simple columns of the underlying table or expressions over the table rows.”, I am thinking that for the index of expressions, aren’t those expressions over table column? e.g., “CREATE INDEX idx_lower_last_name ON users(LOWER(last_name))”, “last_name" is a column rather than a row.

Consider

CREATE INDEX idx_lower_name ON users(LOWER(last_name || ' ' || first_name));

regards, tom lane

Hi, thanks both for the reply.

The description for pg_index.indkey uses the phrasing “an expression over the table columns” and this should be made to match.

Thanks David for showing me that existing documentation, I agree we should make them match.

Regarding Tom’s reply, IMHO, “LOWER(last_name || ' ' || first_name)” is still an expression over table columns? Would you like to elaborate on it a bit?

Regards, Steve.

On Wed, 2024-10-16 at 04:00 +0000, Steve Lau wrote:

Regarding Tom’s reply, IMHO, “LOWER(last_name || ' ' || first_name)” is still an
expression over table columns? Would you like to elaborate on it a bit?

Well, a table row consists of columns. So something that depends on or uses several
columns can be said to be "on the table row". I'd say that the documentation is
correct, but if it gives you trouble, perhaps it should be improved.

And what would you say about this (silly) example:

CREATE TABLE x (a integer, b integer);
CREATE INDEX ON x(hash_record(x));

Yours,
Laurenz Albe

Hi

On Oct 16, 2024, at 12:17 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

And what would you say about this (silly) example:

CREATE TABLE x (a integer, b integer);
CREATE INDEX ON x(hash_record(x));

When I talk about an expression over something, I mainly think about it at the AST level, I guess the AST of expression “hash_record(x)” will be something like (I tried to parse this statement and print the AST using pg_query.rs, but looks like this library does not have an AST type defined, sorry if my guess is too incorrect):

FunctionCall {
name: “hash_record",
arguments: [
Table {
name: "x"
}
]
}

So it is not table columns or rows IMHO.

Regards, Steve.