pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex of expression over table row or column
6+ messages / 4 participants
[nested] [flat]
* Index of expression over table row or column
@ 2024-10-16 03:07 Steve Lau <stevelauc@outlook.com>
0 siblings, 2 replies; 6+ messages in thread
From: Steve Lau @ 2024-10-16 03:07 UTC (permalink / raw)
To: pgsql-docs
Hi, folks!
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.
[1] https://www.postgresql.org/docs/17/index-api.html#INDEX-API
Regards, Steve.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index of expression over table row or column
@ 2024-10-16 03:21 David G. Johnston <david.g.johnston@gmail.com>
parent: Steve Lau <stevelauc@outlook.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2024-10-16 03:21 UTC (permalink / raw)
To: Steve Lau <stevelauc@outlook.com>; +Cc: pgsql-docs
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index of expression over table row or column
@ 2024-10-16 03:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Steve Lau <stevelauc@outlook.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-10-16 03:23 UTC (permalink / raw)
To: Steve Lau <stevelauc@outlook.com>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index of expression over table row or column
@ 2024-10-16 04:00 Steve Lau <stevelauc@outlook.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Steve Lau @ 2024-10-16 04:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; david.g.johnston@gmail.com <david.g.johnston@gmail.com>; +Cc: pgsql-docs
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index of expression over table row or column
@ 2024-10-16 04:17 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Steve Lau <stevelauc@outlook.com>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2024-10-16 04:17 UTC (permalink / raw)
To: Steve Lau <stevelauc@outlook.com>; Tom Lane <tgl@sss.pgh.pa.us>; david.g.johnston@gmail.com <david.g.johnston@gmail.com>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Index of expression over table row or column
@ 2024-10-16 06:14 Steve Lau <stevelauc@outlook.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Steve Lau @ 2024-10-16 06:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; david.g.johnston@gmail.com <david.g.johnston@gmail.com>; pgsql-docs
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-10-16 06:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-16 03:07 Index of expression over table row or column Steve Lau <stevelauc@outlook.com>
2024-10-16 03:21 ` David G. Johnston <david.g.johnston@gmail.com>
2024-10-16 03:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-10-16 04:00 ` Steve Lau <stevelauc@outlook.com>
2024-10-16 04:17 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-10-16 06:14 ` Steve Lau <stevelauc@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox