Re: Order of rows in simple "select r from table_fn()"
> david.g.johns...@gmail.com wrote:
>
>> b...@yugabyte.com wrote:
>>
>> I've found that a table function with "returns table(r text)" provides a
>> convenient way to write a nicely formatted report using psql that can be
>> easily directed to a file with the "\o" metacommand. In general, for cases
>> like this, I can't write a useful "order by r" because the values of "r"
>> interleave, for example, rule-offs between sections of the report, various
>> sub-headings, and actual query results. The required order is exactly the
>> order in which my code produces the rows.
>
> Seems safe enough to rely upon if the query is indeed: SELECT * FROM fn();
> The system has to consume the output of the function call in its serial order
> and has no reason to then reorder things prior to producing the final result
> set. Though I'd probably still consider adding a "report line number" column
> to the output for end-user usability or if they want to sort the report and
> then return to the physical order.
>
> I am curious whether a user-defined set-returning function is allowed to
> specify "WITH ORDINALITY" like the built-in UNNEST function does to produce
> the output row numbers external to the function body and signature.
Thanks, David. Thanks, too, to pavel.steh...@gmail.com for your separate reply
that also says that I can rely on seeing the order in which I produce the rows
in the function's implementation. And yes, I realize that Postgres table
functions are not pipelined in the way that they can be, if you choose this, in
Oracle Database.
Given that the order is pre-calculated, it seems that "with ordinality" can add
line numbering "after the fact" reliably and with minimum clutter when it's
needed. I tried these two variants:
create function f1()
returns setof text
language sql
as $body$
values ('skiing'), ('cycling'), ('running');
$body$;
and:
create function f2()
returns table(r text)
language plpgsql
as $body$
begin
r := 'skiing'; return next;
r := 'cycling'; return next;
r := 'running'; return next;
end;
$body$;
select t.line_no, t.report_text
from f1() with ordinality as t(report_text, line_no);
Each supports this same query
select t.line_no, t.report_text
from fN() with ordinality as t(report_text, line_no);
and gets this same result:
line_no | report_text
-+-
1 | skiing
2 | cycling
3 | running
Re: Order of rows in simple "select r from table_fn()"
Hi > It adds an uncomfortable amount of clutter. > > ** Is it essential for correctness? ** > > It's annoying that the use of "return next" prevents the pattern that each > "print line" follows from being encapsulated into a procedure. But it is > what it is, yes? > RETURN NEXT, RETURN QUERY (RETURN QUERY is +/- RETURN NEXT) materializes result in tuple store. This can be a source of performance problems sometimes (SELECT * FROM fc() LIMIT n) , but you can believe in persistent order. Order of reading from the tuple store is exactly like reading from a file, and there are not any other processes that can modify the order of reading (tuple store is not visible from other processes). I can imagine different mechanisms in the future - without materialization based on pipes - but it is a very far future, and still there will not be a reason for change of order. Regards Pavel
Re: Query plan for "id IS NULL" on PK
David Rowley writes:
> On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer wrote:
>> OTOH it could also be argued that the optimizer should be able to
>> perform the same simplifications as I did above and produce the same
>> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
>> as for WHERE (("id" > ?)) AND (("id" <= ?)).
> You're right, and it has been brought up quite a few times in the
> past. To make it work, it's a fairly trivial change. We'd just need
> to record all the attnotnull columns during something like
> get_relation_info() then when adding baserestrictinfos to the base
> relations, we could look to see if the qual is a NullTest and skip
> that if we deem the qual as constantly true.
There's an order-of-operations issue that makes this more painful
than you might think at first. In the above example, the NullTest
node *isn't* going to be a top-level restrictinfo: it's buried inside
an OR. Really, the only reasonable place to suppress such a NullTest
is during eval_const_expressions, which already has the logic that would
get rid of the now-unnecessary OR above it. And that's problematic
because it's done way ahead of where we know any relation-specific
information. (Since eval_const_expressions happens ahead of join
removal, for $good_reasons, moving the plancat.c fetching to someplace
earlier than that wouldn't be cost-free either.)
> The problem with that is that doing that has an above zero cost and
> since it likely only applies to nearly zero real-world cases, it just
> does not seem like useful cycles to add to the planner.
Yeah, this. In the end there is a low threshold on expensive stuff
that we're willing to do to clean up after brain-dead ORMs, because
the costs of that will also be paid by not-so-brain-dead applications.
In the example at hand, it's hard to argue that the query generator
sending this query shouldn't know better, since as Peter points out
the IS NULL check is redundant on its face, primary key or not.
regards, tom lane
Re: Quoting issue from ODBC
On 2/14/23 14:58, Brad White wrote: > Are these UPDATE's actually necessary? > In other words has nobody noticed a problem with the data over that time frame? I don't know what to make of it. I had the same question you did. I now have proof that these hundreds of errors are not an issue. The postgres logs in pg_log clearly show an error, but the errors never show up in the Access log. Then either: 1) They are not coming from Access. 2) Or indirectly from Access, which kicks off some other code that is not logged. In either case they seem to be surplus to requirements. In other words cruft that is not actually relevant to the application. That means that Access didn't get an error back. Further, I have evidence that the values are actually getting updated. The financial balances are all correct. Issues that were closed are all closed, etc. Even though the Access log says the statement was only run once and Postgres says ERROR, I see no other evidence of it. I can't fathom how that can happen. ¯\_(ツ)_/¯ On the other hand, there are some real errors in the log. I'll put that in another post. Brad -- Adrian Klaver adrian.kla...@aklaver.com
Re: Query plan for "id IS NULL" on PK
On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer wrote:
> OTOH it could also be argued that the optimizer should be able to
> perform the same simplifications as I did above and produce the same
> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
> as for WHERE (("id" > ?)) AND (("id" <= ?)).
You're right, and it has been brought up quite a few times in the
past. To make it work, it's a fairly trivial change. We'd just need
to record all the attnotnull columns during something like
get_relation_info() then when adding baserestrictinfos to the base
relations, we could look to see if the qual is a NullTest and skip
that if we deem the qual as constantly true.
The problem with that is that doing that has an above zero cost and
since it likely only applies to nearly zero real-world cases, it just
does not seem like useful cycles to add to the planner. That might be
different if this was some optimisation that there was no other way to
make work, but that's not the case. All you need to do is remove the
redundant null check. In the planner, if we had some other reason to
record which columns are NOT NULL then the additional overhead of just
looking at the NullTest quals would likely be cheap enough to be
worthwhile. I imagine we'd need to find some other reason to record
attnotnull columns before we'd consider doing this.
David
Re: Order of rows in simple "select r from table_fn()"
On Tue, Feb 14, 2023 at 4:49 PM Bryn Llewellyn wrote: > I've found that a table function with "returns table(r text)" provides a > convenient way to write a nicely formatted report using psql that can be > easily directed to a file with the "\o" metacommand. In general, for cases > like this, I can't write a useful "order by r" because the values of "r" > interleave, for example, rule-offs between sections of the report, various > sub-headings, and actual query results. The required order is exactly the > order in which my code produces the rows. > Seems safe enough to rely upon if the query is indeed: SELECT * FROM fn(); The system has to consume the output of the function call in its serial order and has no reason to then reorder things prior to producing the final result set. Though I'd probably still consider adding a "report line number" column to the output for end-user usability or if they want to sort the report and then return to the physical order. I am curious whether a user-defined set-returning function is allowed to specify "WITH ORDINALITY" like the built-in UNNEST function does to produce the output row numbers external to the function body and signature. David J.
Order of rows in simple "select r from table_fn()"
In general, the order of the rows in a result set is stated to be unpredictable
without an "order by" at the outermost level. Famously, beginners observe what
seems to be reliably reproducible ordering in some queries that don't have an
"order by"—and it can take some effort to persuade them that they cannot rely
on what seems to be a reliable order unless they clutter the SQL (and add the
cost of sorting) by adding an "order by" clause.
I've found that a table function with "returns table(r text)" provides a
convenient way to write a nicely formatted report using psql that can be easily
directed to a file with the "\o" metacommand. In general, for cases like this,
I can't write a useful "order by r" because the values of "r" interleave, for
example, rule-offs between sections of the report, various sub-headings, and
actual query results. The required order is exactly the order in which my code
produces the rows.
Here's a trivial, artificial, example:
create function report_1()
returns table(r text)
language plpgsql
as $body$
declare
rule constant text not null := lpad('—', 40, '—');
begin
r := rule; return next;
r := 'MY REPORT'; return next;
r := rule; return next;
r := ''; return next;
r := 'Section 1'; return next;
r := '-'; return next;
for r in (
select v::text
from t1
order by v
) loop
/**/ return next;
end loop;
r := '';return next;
r := 'Section 2'; return next;
r := '-'; return next;
for r in (
select v::text
from t2
order by v desc
) loop
/**/ return next;
end loop;
r := rule; return next;
end;
$body$;
And this is the output, given some suitable content in t1 and t2, from "select
r from report_1()" with no "order by":
MY REPORT
Section 1
-
10
12
14
16
Section 2
-
27
24
21
I've written no end of reports this way. And I've never, ever, seen the rows
come out in an order that differs from the order in which they're written. (Of
course, I know that this proves nothing.) Here's a variant that lets me say
"select r from report_1() order by k":
create function report_2()
returns table(k int, r text)
language plpgsql
as $body$
declare
rule constant text not null := lpad('—', 40, '—');
begin
k = 1; r := rule; return next;
k = k + 1; r := 'MY REPORT';return next;
k = k + 1; r := rule; return next;
k = k + 1; r := ''; return next;
k = k + 1; r := 'Section 1';return next;
k = k + 1; r := '-';return next;
for r in (
select v::text
from t1
order by v
) loop
k = k + 1;return next;
end loop;
k = k + 1; r := ''; return next;
k = k + 1; r := 'Section 2';return next;
k = k + 1; r := '-';return next;
for r in (
select v::text
from t2
order by v desc
) loop
k = k + 1;return next;
end loop;
k = k + 1; r := rule; return next;
end;
$body$;
It adds an uncomfortable amount of clutter.
* Is it essential for correctness? *
It's annoying that the use of "return next" prevents the pattern that each
"print line" follows from being encapsulated into a procedure. But it is what
it is, yes?
Re: Quoting issue from ODBC
> Are these UPDATE's actually necessary? > In other words has nobody noticed a problem with the data over that time frame? I don't know what to make of it. I had the same question you did. I now have proof that these hundreds of errors are not an issue. The postgres logs in pg_log clearly show an error, but the errors never show up in the Access log. That means that Access didn't get an error back. Further, I have evidence that the values are actually getting updated. The financial balances are all correct. Issues that were closed are all closed, etc. Even though the Access log says the statement was only run once and Postgres says ERROR, I see no other evidence of it. I can't fathom how that can happen. ¯\_(ツ)_/¯ On the other hand, there are some real errors in the log. I'll put that in another post. Brad
Re: Query plan for "id IS NULL" on PK
On 2/14/23 15:43, Peter J. Holzer wrote:
On 2023-02-14 15:36:32 -0700, Rob Sargent wrote:
But if the query is supposed to be generic and re-used in a situation where id
could be null, wouldn't the null id records be fetched every time?
No, they will never be fetched because of the AND (("id" <= ?)).
hp
Yeah, wanted that back as I hit send. Sorry.
Re: Query plan for "id IS NULL" on PK
On 2023-02-14 15:36:32 -0700, Rob Sargent wrote:
> But if the query is supposed to be generic and re-used in a situation where id
> could be null, wouldn't the null id records be fetched every time?
No, they will never be fetched because of the AND (("id" <= ?)).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Query plan for "id IS NULL" on PK
On 2023-02-14 17:04:51 -0500, Ben Chrobot wrote:
> We have a large table (~470 million rows) with integer primary key id (not
> null) on a Postgres 14.5 cluster. A third-party tool is attempting to perform
> a
> SELECT-based full table copy in preparation for log-based sync with a query
> like the following:
>
> SELECT "id", "other_column_a", "other_column_b", "created_at", "updated_at"
> FROM "public"."my_large_table"
> WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
> ORDER BY "id" LIMIT 5;
That doesn't make sense. ("id" <= ?) implies that ("id" IS NULL) is
FALSE. So the where clause can be simplified to
WHERE (("id" > ? OR FALSE)) AND (("id" <= ?))
and then
WHERE (("id" > ?)) AND (("id" <= ?))
even without the knowledge that "id" is a primary key (and therefore can
never be null).
Even if the column could contain NULL values, those would never be
selected.
It could therefore be argued that the query as written is broken and
should be fixed.
OTOH it could also be argued that the optimizer should be able to
perform the same simplifications as I did above and produce the same
code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
as for WHERE (("id" > ?)) AND (("id" <= ?)).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Query plan for "id IS NULL" on PK
On 2/14/23 15:30, David G. Johnston wrote: On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote: When will id be null in a primary key? The OP seems to be aware of this... "We cannot change the query being executed. Is there any way we can make the query planner ignore `OR (id IS NULL)` (as that will never be the case for the PK) and use both `id` clauses in the index condition?" David J. Yes, agreed. But if the query is supposed to be generic and re-used in a situation where id could be null, wouldn't the null id records be fetched every time?
Re: Query plan for "id IS NULL" on PK
On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote: > > When will id be null in a primary key? > > The OP seems to be aware of this... "We cannot change the query being executed. Is there any way we can make the query planner ignore `OR (id IS NULL)` (as that will never be the case for the PK) and use both `id` clauses in the index condition?" David J.
Re: Query plan for "id IS NULL" on PK
On 2/14/23 15:04, Ben Chrobot wrote:
Hello,
Long time listener, first time caller.
We have a large table (~470 million rows) with integer primary key id
(not null) on a Postgres 14.5 cluster. A third-party tool is
attempting to perform a SELECT-based full table copy in preparation
for log-based sync with a query like the following:
SELECT "id", "other_column_a", "other_column_b", "created_at",
"updated_at"
FROM "public"."my_large_table"
WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
ORDER BY "id" LIMIT 5;
The lower bound increments by batch size (50k) while the upper bound
is always the `max(id)`, in our case around 575,000,000.
The query plan produced is very slow as the index condition does
basically nothing:
QUERY PLAN
Limit (cost=0.57..21901.46 rows=5 width=417) (actual
time=1708920.675..1709198.995 rows=5 loops=1)
-> Index Scan using my_large_table_pkey on
my_large_table (cost=0.57..135230792.97 rows=308733624 width=417)
(actual time=1708920.673..1709195.926 rows=5 loops=1)
Index Cond: (id <= 575187488)
Filter: ((id > 193208795) OR (id IS NULL))
Rows Removed by Filter: 157784540
Planning Time: 0.186 ms
Execution Time: 1709200.618 ms
(7 rows)
Time: 1709231.721 ms (28:29.232)
We cannot change the query being executed. Is there any way we can
make the query planner ignore `OR (id IS NULL)` (as that will never be
the case for the PK) and use both `id` clauses in the index condition?
We have provided the vendor with the same query without the `id is
null` showing that it's significantly faster (see below). They have
informed us that addressing the null check on a not null PK is on
their roadmap to address but no timeline.
explain analyze
SELECT "id", "other_column_a", "other_column_b", "created_at",
"updated_at"
FROM "public"."my_large_table"
WHERE (("id" > ?)) AND (("id" <= ?))
ORDER BY "id" LIMIT 5;
QUERY PLAN
-
Limit (cost=0.57..13930.19 rows=5 width=416) (actual
time=2.118..400.937 rows=5 loops=1)
-> Index Scan using my_large_table_pkey on my_large_table
(cost=0.57..85429173.84 rows=306645829 width=416) (actual
time=2.117..398.325 rows=5 loops=1)
Index Cond: ((id > 193208795) AND (id <= 575187488))
Planning Time: 0.166 ms
Execution Time: 402.376 ms
We have tried leading the planner to water with this view but it did
not change the slow query plan:
create view my_fast_large_table as
select *
from my_large_table
where id is not null;
Any other tricks to try here?
Thank you,
Ben Chrobot
When will id be null in a primary key?
Query plan for "id IS NULL" on PK
Hello,
Long time listener, first time caller.
We have a large table (~470 million rows) with integer primary key id (not
null) on a Postgres 14.5 cluster. A third-party tool is attempting to
perform a SELECT-based full table copy in preparation for log-based sync
with a query like the following:
SELECT "id", "other_column_a", "other_column_b", "created_at", "updated_at"
FROM "public"."my_large_table"
WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
ORDER BY "id" LIMIT 5;
The lower bound increments by batch size (50k) while the upper bound is
always the `max(id)`, in our case around 575,000,000.
The query plan produced is very slow as the index condition does basically
nothing:
QUERY PLAN
Limit (cost=0.57..21901.46 rows=5 width=417) (actual
time=1708920.675..1709198.995 rows=5 loops=1)
-> Index Scan using my_large_table_pkey on
my_large_table (cost=0.57..135230792.97 rows=308733624 width=417) (actual
time=1708920.673..1709195.926 rows=5 loops=1)
Index Cond: (id <= 575187488)
Filter: ((id > 193208795) OR (id IS NULL))
Rows Removed by Filter: 157784540
Planning Time: 0.186 ms
Execution Time: 1709200.618 ms
(7 rows)
Time: 1709231.721 ms (28:29.232)
We cannot change the query being executed. Is there any way we can make the
query planner ignore `OR (id IS NULL)` (as that will never be the case for
the PK) and use both `id` clauses in the index condition?
We have provided the vendor with the same query without the `id is null`
showing that it's significantly faster (see below). They have informed us
that addressing the null check on a not null PK is on their roadmap to
address but no timeline.
explain analyze
SELECT "id", "other_column_a", "other_column_b", "created_at", "updated_at"
FROM "public"."my_large_table"
WHERE (("id" > ?)) AND (("id" <= ?))
ORDER BY "id" LIMIT 5;
QUERY PLAN
-
Limit (cost=0.57..13930.19 rows=5 width=416) (actual
time=2.118..400.937 rows=5 loops=1)
-> Index Scan using my_large_table_pkey on my_large_table
(cost=0.57..85429173.84 rows=306645829 width=416) (actual
time=2.117..398.325 rows=5 loops=1)
Index Cond: ((id > 193208795) AND (id <= 575187488))
Planning Time: 0.166 ms
Execution Time: 402.376 ms
We have tried leading the planner to water with this view but it did not
change the slow query plan:
create view my_fast_large_table as
select *
from my_large_table
where id is not null;
Any other tricks to try here?
Thank you,
Ben Chrobot
pro services list
Is this the place for suggestions for postgres.org? I had occasion yesterday to visit the page of available support companies. I see it's sorted alphabetically. Does that tend to favour the "A"s? Thinking of Yellow Page (tangible, phone company version) listings like " knife sharpening". Hard to randomize that per get?
Re: Multi-column index: Which column order
> On 14/02/2023 18:53 CET Sebastien Flaesch wrote: > > Hello! > > When creating an index on multiple columns, does the order of the columns > matter? (I guess so) Maybe, depending on the queries. > It's mostly for SELECT statements using a condition that include ALL > columns of the index (pkey): > > SELECT * FROM art WHERE etb='L1' and code='ART345' > > I would naturally put the columns with the most various values first, and > > For example, if the "code" column contains thousands of various item ids > like 'SXZ874', 'ERF345', ... while the "etb" column contains a dozen of > values like "L1", "LT" and "BX". > > Which one is best? > > CREATE UNIQUE INDEX ix1 ON art (code, etb) > or > CREATE UNIQUE INDEX ix1 ON art (etb, code) > > (or its PRIMARY KEY equivalent) It should not make any difference for the query above. It can make a difference for queries that only filter by the second index column or use inequality constraints on those columns. > Does it depend on the type of index (Btree, GiST, etc) ? > > I could not find that information in the doc. Yes, see the documentation on multicolumn indexes with details on how they are used: https://www.postgresql.org/docs/current/indexes-multicolumn.html But you're limited to btree anyway if you're only interested in unique indexes. -- Erik
[Outcome] Queries running forever, because of wrong rowcount estimate
On Mon, Feb 13, 2023 at 12:38:12PM -0500, Tom Lane wrote:
! Peter writes:
! > "rows=1" in the "Hash Anti Join" line is WRONG. It should be
! > 300. Or at least some thousands.
!
! FWIW, this behaves better in v14 and up. In older versions there's
! an ambiguity about what relpages=reltuples=0 means; it's the state
! when ANALYZE has never yet examined the table contents, but it's
! also the state when we have found the table to be empty. Because
! of the former case, older planner versions don't like to believe
! that a table is empty even if reltuples says so.
!
! regards, tom lane
Okay, I got someway through with it.
Given, the destination table is empty, and VACUUMed. Then there is
relpages = reltuples = 0.
We do some
"source LEFT OUTER JOIN destination ON (matchfield) WHERE matchfield IS NULL"
The source is big, but carries only a few distinct matchfield values.
The so-called "hack" in heapam_handler.c:heapam_estimate_rel_size()
does then makes the planner believe that there are actually 10
curpages. This is not wrong, because the same might happen if one does
an INSERT amounting for 10 pages and not yet ANALYZE.
We then get a calculated rowcount of whatever, lets assume 2330 rows.
Then we go into eqjoinsel():
n_distinct for the left source table is known, it is (lets assume) 4.
n_distinct for the right destination table is not known because it is
not analyzed, but it is UNIQUE, so it becomes 2330.
We get into eqjoinsel_inner():
MCV (right) for the destination is not known because it is not analyzed.
selec = selec / nd2 => 0.000429
We get into eqjoinsel_semi():
if (nd1 <= nd2 || nd2 < 0)
selec = 1.0 - nullfrac1;
Voila, selec = 1.0
And so the ANTI JOIN will estimate to 0 (aka 1) result rows, instead
of whatever rowcount the source brings along (usually big).
- cut ---
Okay, so this should be fixed in R.14.
But, as mentioned above, the same can also happen in normal operations
Example:
I have a logfile from my webserver and a table to collect the (unique)
IP-addresses. I start with an empty table and feed in the log.
First round, the destination addresses. Lats assume there are 2330
different addresses.
Second round, the source addresses. There are only 4 different ones.
I don't do an ANALYZE between the two steps.
And voila, I get the same effect as above. (Been there, done
it: duration: 30216273.583 ms)
Agreed, this is an inefficient way of doing that. It would be better
to do a UNION on the source data, at that point filter it accordingly,
then DISTINCT, and only then the ANTI JOIN.
But I'm a lazy guy and I may not care if a query that could run in
0.5 seconds takes 60 seconds instead.
However, it's not so nice when it takes 10 hours.
Anyway, I don't get a real clue on what this stance should actually
achieve (from selfuncs.c:eqjoinsel_semi()#2773):
if (nd1 <= nd2 || nd2 < 0)
selec = 1.0 - nullfrac1;
else
selec = (nd2 / nd1) * (1.0 - nullfrac1);
This seems to assume a 100% match whenever the left (source) table
brings fewer distinct(!) values than the right (destination) table
already carries.
For now, I have just disabled this behaviour, in the rough&dirty way:
--- src/backend/optimizer/path/costsize.c.orig 2022-11-07 22:47:13.0
+0100
+++ src/backend/optimizer/path/costsize.c 2023-02-13 00:04:54.156489000
+0100
@@ -4685,8 +4685,11 @@
/* pselec not used */
break;
case JOIN_ANTI:
+ if(fkselec * jselec > 0.9975) {
+ jselec = 0.9975;
+ }
nrows = outer_rows * (1.0 - fkselec * jselec);
nrows *= pselec;
break;
default:
/* other values not expected here */
cheerio,
PMc
Multi-column index: Which column order
Hello! When creating an index on multiple columns, does the order of the columns matter? (I guess so) It's mostly for SELECT statements using a condition that include ALL columns of the index (pkey): SELECT * FROM art WHERE etb='L1' and code='ART345' I would naturally put the columns with the most various values first, and For example, if the "code" column contains thousands of various item ids like 'SXZ874', 'ERF345', ... while the "etb" column contains a dozen of values like "L1", "LT" and "BX". Which one is best? CREATE UNIQUE INDEX ix1 ON art (code, etb) or CREATE UNIQUE INDEX ix1 ON art (etb, code) (or its PRIMARY KEY equivalent) Does it depend on the type of index (Btree, GiST, etc) ? I could not find that information in the doc. Seb
Re: Losing my latin on Ordering...
On Tue, 2023-02-14 at 13:06 +0100, Dominique Devienne wrote: > > Sure, just make sure to use the definition of C that uses UTF-8 encoding > > (I think it's typically called C.UTF-8). > > OK, so for new DBs, sounds like we need to > > CREATE DATABASE ... WITH LOCALE 'C.UTF-8' ENCODING UTF8 > > Correct? Collations are identifiers, so it has to be double quotes. The name depends on the operating system; if that is Unix-like, you can run "locale -a" to get all available locales. On my system it would be CREATE DATABASE x TEMPLATE template0 LOCALE "C.utf8" ENCODING UTF8; > But what about existing DBs? Can the collation be changed a posteriori? > ALTER DATABASE does not seem to support the same options. > > We don't want to have to sprinkle COLLATE "C" all over the place in the code. > And there are quite a few DBs out there already. What to do about them? The only option is dump/restore. A changed collation means changed indexes, so there is no better option. Yours, Laurenz Albe
Re: Losing my latin on Ordering...
On Tue, Feb 14, 2023 at 12:35 PM Alvaro Herrera wrote: > On 2023-Feb-14, Dominique Devienne wrote: > > Honestly, who expects the same prefix to sort differently based on what > > comes after, in left-to-right languages? > Look, we don't define the collation rules. > Ok, ok, sorry. To you, Laurenz, and everyone. I obviously disagree with these rules, but I'm a nobody, so who cares :) > > So the "C" collation is fine with general UTF-8 encoding? > > I.e. it will be codepoint ordered OK? > > Sure, just make sure to use the definition of C that uses UTF-8 encoding > (I think it's typically called C.UTF-8). > OK, so for new DBs, sounds like we need to CREATE DATABASE ... WITH LOCALE 'C.UTF-8' ENCODING UTF8 Correct? But what about existing DBs? Can the collation be changed a posteriori? ALTER DATABASE does not seem to support the same options. We don't want to have to sprinkle COLLATE "C" all over the place in the code. And there are quite a few DBs out there already. What to do about them?
Re: Losing my latin on Ordering...
On Tue, 2023-02-14 at 12:17 +0100, Dominique Devienne wrote: > On Tue, Feb 14, 2023 at 11:23 AM Laurenz Albe > wrote: > > On Tue, 2023-02-14 at 10:31 +0100, Dominique Devienne wrote: > > > Surely sorting should be "constant left-to-right", no? What are we > > > missing? > > > > No, it isn't. That's not how natural language collations work. > > Honestly, who expects the same prefix to sort differently based on what comes > after, in left-to-right languages? > How does one even find out what the (capricious?) rules for sorting in a given > collation are? Look at the documentation / implementation. As far as ICU is concerned, here: https://unicode.org/reports/tr10/ > > > I'm already surprised (star) comes before (space), when the latter "comes > > > before" the former in both ASCII and UTF-8, but that the two "Foo*" and > > > "Foo " > > > prefixed pairs are not clustered after sorting is just mistifying to me. > > > So how come? > > > > Because they compare identical on the first three levels. Any difference in > > letters, accents or case weighs stronger, even if it occurs to the right > > of these substrings. > > That's completely unintuitive... Well, you can complain to GNU and the Unicode consortium, but that's pretty much the way it is. > > Yes, it soulds like the "C" collation may be best for you. That is, if you > > don't > > mind that "Z" < "a". > > I would mind if I asked for case-insensitive comparisons. > > So the "C" collation is fine with general UTF-8 encoding? > I.e. it will be codepoint ordered OK? Yes, exactly. Yours, Laurenz Albe
Re: Losing my latin on Ordering...
On 2023-Feb-14, Dominique Devienne wrote: > Honestly, who expects the same prefix to sort differently based on what > comes after, in left-to-right languages? Look, we don't define the collation rules. We just grab the collation rules defined by experts in collations. In this case the experts have advised the glibc developers to write collations this way; but even if you went further and looked at the ICU libraries, you would find that they have pretty much the same definition. > How does one even find out what the (capricious?) rules for sorting in a > given collation are? I suggest to look at a telephone book carefully sometime (provided you can find one ... apparently nobody wants them anymore.) > So the "C" collation is fine with general UTF-8 encoding? > I.e. it will be codepoint ordered OK? Sure, just make sure to use the definition of C that uses UTF-8 encoding (I think it's typically called C.UTF-8). -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Re: Losing my latin on Ordering...
On Tue, Feb 14, 2023 at 11:23 AM Laurenz Albe wrote: > On Tue, 2023-02-14 at 10:31 +0100, Dominique Devienne wrote: > > Hi. Porting a unit test to PostgreSQL, we got a failure related to > ordering. > > > > We've distilled it to the below. The DB is en_US.UTF-8, and the sorting > we get > > does not make sense to me. The same prefix can be sorted differently > based on > > the suffix apprently, which doesn't make any sense to me. > > > > Surely sorting should be "constant left-to-right", no? What are we > missing? > > No, it isn't. That's not how natural language collations work. > Honestly, who expects the same prefix to sort differently based on what comes after, in left-to-right languages? How does one even find out what the (capricious?) rules for sorting in a given collation are? I'm aware of sorting taking numerical numbers in text influencing sort, so "Foo10" comes after "Foo9", but that's not what we are discussing here. "Foo*" and "Foo " have no logical relatioship, like 9 and 10 do. > > I'm already surprised (star) comes before (space), when the latter "comes > > before" the former in both ASCII and UTF-8, but that the two "Foo*" and > "Foo " > > prefixed pairs are not clustered after sorting is just mistifying to me. > So how come? > > Because they compare identical on the first three levels. Any difference > in > letters, accents or case weighs stronger, even if it occurs to the right > of these substrings. > That's completely unintuitive... > > For now we can work-around this by explicitly adding the `collate "C"` on > > the queries underlying that particular test, but that would be wrong in > the > > general case of international strings to sort, so I'd really like to > understand > > what's going on. > > Yes, it soulds like the "C" collation may be best for you. That is, if > you don't > mind that "Z" < "a". > I would mind if I asked for case-insensitive comparisons. So the "C" collation is fine with general UTF-8 encoding? I.e. it will be codepoint ordered OK?
Re: Losing my latin on Ordering...
On Tue, 2023-02-14 at 10:31 +0100, Dominique Devienne wrote: > Hi. Porting a unit test to PostgreSQL, we got a failure related to ordering. > > We've distilled it to the below. The DB is en_US.UTF-8, and the sorting we get > does not make sense to me. The same prefix can be sorted differently based on > the suffix apprently, which doesn't make any sense to me. > > Surely sorting should be "constant left-to-right", no? What are we missing? No, it isn't. That's not how natural language collations work. They typically use different levels of comparison: first, strings are sorted according to base character, ignoring accents, case and punctuation. Wherever that comparison is equal, the next level is used (typically accents), then the next (case), and so on. > I'm already surprised (star) comes before (space), when the latter "comes > before" the former in both ASCII and UTF-8, but that the two "Foo*" and "Foo " > prefixed pairs are not clustered after sorting is just mistifying to me. So > how come? Because they compare identical on the first three levels. Any difference in letters, accents or case weighs stronger, even if it occurs to the right of these substrings. > For now we can work-around this by explicitly adding the `collate "C"` on > the queries underlying that particular test, but that would be wrong in the > general case of international strings to sort, so I'd really like to > understand > what's going on. Yes, it soulds like the "C" collation may be best for you. That is, if you don't mind that "Z" < "a". Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Losing my latin on Ordering...
Hi. Porting a unit test to PostgreSQL, we got a failure related to ordering.
We've distilled it to the below. The DB is en_US.UTF-8, and the sorting we
get
does not make sense to me. The same prefix can be sorted differently based
on the suffix apprently, which doesn't make any sense to me.
Surely sorting should be "constant left-to-right", no? What are we missing?
I'm already surprised (star) comes before (space), when the latter "comes
before" the former in both ASCII and UTF-8, but that the two "Foo*" and
"Foo " prefixed pairs are not clustered after sorting is just mistifying to
me. So how come?
For now we can work-around this by explicitly adding the `collate "C"` on
the queries underlying that particular test, but that would be wrong in the
general case of international strings to sort, so I'd really like to
understand what's going on.
Thanks, --DD
PS: if I try "en_US.UTF-8" or "en_US"."UTF-8" for the collate, it fails.
How come what pg_database.datcollate displays is not a valid value for
collate?
PPS: We tried on v12 and v13 I believe. Somehow my v14.2 on Windows doesn't
have en_US as a collation...
ddevienne=> with t(v) as (values ('Foo All'), ('Foo*'), ('Foo Brief'),
('Foo*All')) select '<'||v||'>', v::bytea from t order by v collate "en_US";
?column? | v
-+--
| \x466f6f2a
| \x466f6f20416c6c
| \x466f6f2a416c6c
| \x466f6f204272696566
(4 rows)
ddevienne=> with t(v) as (values ('Foo All'), ('Foo*'), ('Foo Brief'),
('Foo*All')) select '<'||v||'>', v::bytea from t order by v collate "C";
?column? | v
-+--
| \x466f6f20416c6c
| \x466f6f204272696566
| \x466f6f2a
| \x466f6f2a416c6c
(4 rows)
