Domain check taking place unnecessarily?
I'm used to adding an empty column being instant in most cases, so my
attention was drawn when it took a long lock.
The timings below imply that each row is running the CHECK?
I've come to expect addition of a NULL column to be fast, and what I'm
seeing seems to contradict the docs [1]:
> PostgreSQL assumes that CHECK constraints' conditions are immutable,
> that is, they will always give the same result for the same input value.
> This assumption is what justifies examining CHECK constraints only when
> a value is first converted to be of a domain type, and not at other
> times.
I've ruled out waiting on a lock; nothing is reported with
log_lock_waits=on. This is a test database with exclusive access (2.5
million rows):
I don't think this is another index or constraint, as removing them does
not affect performance. Also the "text" case below seems to prove this.
Results are fully reproducable by repeatedly dropping and adding these
columns.
Reporting in case something is not as expected. I can't even think of a
workaround here...
This is PostgreSQL 14.5 on Alpine Linux. Thanks.
[1] https://www.postgresql.org/docs/current/sql-createdomain.html
---
CREATE DOMAIN hash AS text
CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$');
devstats=> ALTER TABLE invite ADD COLUMN test text;
ALTER TABLE
Time: 8.988 ms
devstats=> ALTER TABLE invite ADD COLUMN test hash;
ALTER TABLE
Time: 30923.380 ms (00:30.923)
devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT NULL;
ALTER TABLE
Time: 30344.272 ms (00:30.344)
devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT '123abc123'::hash;
ALTER TABLE
Time: 67439.232 ms (01:07.439)
--
Mark
Re: Domain check taking place unnecessarily?
On Wed, Feb 8, 2023 at 11:01 AM Mark Hills wrote:
>
> CREATE DOMAIN hash AS text
> CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$');
>
> devstats=> ALTER TABLE invite ADD COLUMN test hash;
> ALTER TABLE
> Time: 30923.380 ms (00:30.923)
>
Necessarily, I presume because if you decided that the check on the domain
should be "value is not null" (don't do this though...) the column addition
would have to fail for existing rows (modulo defaults...).
David J.
Re: Domain check taking place unnecessarily?
On Wed, 2023-02-08 at 18:01 +, Mark Hills wrote:
> I've ruled out waiting on a lock; nothing is reported with
> log_lock_waits=on. This is a test database with exclusive access (2.5
> million rows):
>
> This is PostgreSQL 14.5 on Alpine Linux. Thanks.
>
> CREATE DOMAIN hash AS text
> CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$');
>
> devstats=> ALTER TABLE invite ADD COLUMN test text;
> ALTER TABLE
> Time: 8.988 ms
>
> devstats=> ALTER TABLE invite ADD COLUMN test hash;
> ALTER TABLE
> Time: 30923.380 ms (00:30.923)
>
> devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT NULL;
> ALTER TABLE
> Time: 30344.272 ms (00:30.344)
>
> devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT '123abc123'::hash;
> ALTER TABLE
> Time: 67439.232 ms (01:07.439)
It takes 30 seconds to schan the table and determine that all existing rows
satisky the constraint.
The last example is slower, because there is actually a non-NULL value to check.
If that were not a domain, but a normal check constraint, you could first add
the constraint as NOT VALID and later run ALTER TABLE ... VALIDATE CONSTRAINT
...,
which takes a while too, but does not lock the table quite that much.
But I don't think there is a way to do that with a domain.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Window Functions & Table Partitions
Hell postgres people!
This is not an issue report so much as a gripe. I'm on postgres 12.2, so it
is entirely possible that the issue I describe is fixed in a later version.
If so, it is not described in the docs or any posts I can find archived on
pgsql-performance. (I am not brave enough to delve into pgsql-developer,
where I'm sure this has been brought up at some point)
Basically- window partition functions don't take advantage of existing
table partitions. I use window functions as a more powerful GROUP BY clause
that preserves row-by-row information- super handy for a lot of things.
In particular, I want to use window functions on already partitioned
tables, like the below example:
create table abb (a int, b int, g int) partition by hash(b)
/* populate table etc... */
select a, b, min(a) over (partition by b) as g from abb
Ideally with a query plan like this:
Window:
Append:
Sort on table_p0
Sort on table_p1
Sort on table_p2
Instead, I get this:
Window:
Sort:
Append:
Parallel seq scan on table_p0
Parallel seq scan on table_p1
Parallel seq scan on table_p2
Which is a BIG no-no, as there could potentially be thousands of partitions
and BILLIONS of rows per table. This can be solved by manually implementing
the first query plan via scripting, e.g:
do $$
declare i int;
begin
for i in 0..get_npartitions() loop
execute('select a, b, min(a) over (partition by b) as g from
abb_p%', i);
end loop;
end $$ language plpgsql;
This is not ideal, but perfectly workable. I'm sure you guys are already
aware of this, it just seems like a really simple fix to me- if the window
function partition scheme exactly matches the partition scheme of the table
it queries, it should take advantage of those partitions.
Thanks,
Ben
Re: Window Functions & Table Partitions
On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle wrote:
> Basically- window partition functions don't take advantage of existing table
> partitions. I use window functions as a more powerful GROUP BY clause that
> preserves row-by-row information- super handy for a lot of things.
>
> In particular, I want to use window functions on already partitioned tables,
> like the below example:
>
> create table abb (a int, b int, g int) partition by hash(b)
> /* populate table etc... */
> select a, b, min(a) over (partition by b) as g from abb
>
> Ideally with a query plan like this:
>
> Window:
> Append:
> Sort on table_p0
> Sort on table_p1
> Sort on table_p2
There was some effort [1] in version 12 to take advantage of the order
defined by the partitioning scheme. The release notes [2] mention:
"Avoid sorting when partitions are already being scanned in the necessary order"
However, it's not 100% of what you need as there'd have to be a btree
index on abb(b) for the planner to notice.
Likely this could be made better so that add_paths_to_append_rel()
added the pathkeys defined by the partitioned table into
all_child_pathkeys if they didn't exist already. In fact, I've
attached a very quickly hacked together patch against master to do
this. I've given it very little thought and it comes complete with
failing regression tests.
If you're interested in pursuing this then feel free to take the patch
to the pgsql-hackers mailing list and propose it. It's unlikely I'll
get time to do that for a while, but I will keep a branch locally with
it to remind me in case I do at some point in the future.
David
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=959d00e9dbe4cfcf4a63bb655ac2c29a5e579246
[2] https://www.postgresql.org/docs/release/12.0/
diff --git a/src/backend/optimizer/path/allpaths.c
b/src/backend/optimizer/path/allpaths.c
index ae0f9bdc8a..c4271c9179 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1365,6 +1365,27 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo
*rel,
}
}
+ if (rel->part_scheme != NULL && IS_SIMPLE_REL(rel) &&
+ partitions_are_ordered(rel->boundinfo, rel->live_parts))
+ {
+ List *partition_pathkeys;
+ boolpartial;
+
+ partition_pathkeys = build_partition_pathkeys(root, rel,
+
ForwardScanDirection,
+
&partial);
+
+ if (!partial)
+ all_child_pathkeys =
lappend(all_child_pathkeys, partition_pathkeys);
+
+ partition_pathkeys = build_partition_pathkeys(root, rel,
+
BackwardScanDirection,
+
&partial);
+
+ if (!partial)
+ all_child_pathkeys =
lappend(all_child_pathkeys, partition_pathkeys);
+ }
+
/*
* Collect lists of all the available path orderings and
* parameterizations for all the children. We use these as a
max_wal_senders
I've been thinking about the max_wal_senders parameter lately and wondering if there is any harm in setting it too high. I'm wondering if I should try to shave a few senders off, perhaps to match my logical replicas + 1, instead of just leaving it at the default of 10. Or vice-versa, can clients use more than one sender if they are available? Would increasing it result in lower latency? The documentation is a little vague. The documentation mentions an orphaned connection slot that may take a while to time out. How can I tell if I have any of those? I was looking for a `pg_wal_slots` table similar to the `pg_replication_slots` table, but don't see anything obvious in the catalog.
Re: max_wal_senders
On Wed, 2023-02-08 at 18:07 -0500, Rick Otten wrote: > I've been thinking about the max_wal_senders parameter lately and wondering > if there > is any harm in setting it too high. No, there isn't, except that if you end up having too many *actual* WAL senders, it will cause load. A high limit is no problem as such. > The documentation mentions an orphaned connection slot that may take a while > to time out. > How can I tell if I have any of those? I was looking for a `pg_wal_slots` > table > similar to the `pg_replication_slots` table, but don't see anything obvious > in the catalog. The view is "pg_stat_replication", but you won't see there if an entry is abandoned before PostgreSQL does and terminates it. You can set "tcp_keepalived_idle" low enough so that the kernel will detect broken connections early on. Yours, Laurenz Albe
Re: max_wal_senders
Hi, On 2023-02-09 06:59:53 +0100, Laurenz Albe wrote: > On Wed, 2023-02-08 at 18:07 -0500, Rick Otten wrote: > > I've been thinking about the max_wal_senders parameter lately and wondering > > if there > > is any harm in setting it too high. > > No, there isn't, except that if you end up having too many *actual* WAL > senders, it > will cause load. A high limit is no problem as such. That's not *quite* true. The downsides are basically the same as for max_connections (It's basically treated the same, see InitializeMaxBackends()): You need more shared memory. There's a small degradation of performance due to the increased size of some shared datastructures, most prominently the lock table for heavyweight locks. Greetings, Andres Freund
