Thread: Index of expression over table row or column

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? 

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
forthe 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
tomatch.
 

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
youlike 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
likethis 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.