Re: Database creation performance drop going from pg 14 to pg 15+
Here's what the OP of the PostgresNIO issue has mentioned about what performance impact usage of `file_copy` has in his setup (https://github.com/SwiftPackageIndex/SwiftPackageIndex-Server/pull/3812): `Series: Default Strategy Suite AllTests passed after 5.081 seconds Suite AllTests passed after 5.274 seconds Suite AllTests passed after 5.306 seconds Suite AllTests passed after 5.224 seconds Suite AllTests passed after 6.343 seconds Suite AllTests passed after 5.450 seconds Series: file_copy Strategy Suite AllTests passed after 4.729 seconds Suite AllTests passed after 4.755 seconds Suite AllTests passed after 4.739 seconds Suite AllTests passed after 4.772 seconds Suite AllTests passed after 4.866 seconds Suite AllTests passed after 4.855 seconds` This is them running the tests in parallel multiple times with and without file_copy, just to benchmark. Overall ~0.5-6s / ~10% speedup, which isn't too significant at this level, but is still a noticeable relative improvement.
Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized
On 6/4/25 16:12, Dimitrios Apostolou wrote: In general I have noticed most operations are slower after a succesful pg_restore until VACUUM is complete, which is unfortunate as the database is huge and it takes days to run. Something I have on my list to try, is whether a COPY FREEZE would alleviate all this trouble, since all tuples are immediately visible then. Maybe a patch for a new pg_restore option --freeze is a better solution. Are my assumptions right? It seems that the idea has already been discussed: https://www.postgresql.org/message-id/flat/CA%2BU5nM%2BXvkUu9ran%2B5cY%3DTWQquLTpvzte4KVMK%3DaDfbr-xfNXA%40mail.gmail.com#b61a7fee06e10e61afa68712bc0b3c5b I've CCed Bruce Mojman, in the hope that he can tell us more about it.
Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized
On 6/5/25 16:13, Frédéric Yhuel wrote: On 6/4/25 16:12, Dimitrios Apostolou wrote: In general I have noticed most operations are slower after a succesful pg_restore until VACUUM is complete, which is unfortunate as the database is huge and it takes days to run. Something I have on my list to try, is whether a COPY FREEZE would alleviate all this trouble, since all tuples are immediately visible then. Maybe a patch for a new pg_restore option --freeze is a better solution. Are my assumptions right? It seems that the idea has already been discussed: https:// www.postgresql.org/message-id/flat/ CA%2BU5nM%2BXvkUu9ran%2B5cY%3DTWQquLTpvzte4KVMK%3DaDfbr- xfNXA%40mail.gmail.com#b61a7fee06e10e61afa68712bc0b3c5b I've CCed Bruce Mojman, in the hope that he can tell us more about it. (It might be more interesting now than 12 years ago thanks to this patch: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7db0cd2145f2bce84cac92402e205e4d2b045bf2)
Re: Poor row estimates from planner, stat `most_common_elems` sometimes missing for a text[] column
Mark Frost writes: > We're seeing intermittently very poor performance of a query, when > occasionally a poor query plan is chosen. We're using Postgres 16.9. > One suspicious factor when looking at the EXPLAIN ANALYZE output, is a very > wrong estimated number of rows to be returned from a text[] column queried > with '&&'. > After playing around with a simple recreate (details below), it seems ANALYZE > of the table is affected by the number of rows in the table. Statistic > `most_common_elems` is [null] when there's over 15,873 rows in the table when > analyzed. With fewer rows it's analyzed correctly. Thanks for the report. Poking through the code, it seems like there are two distinct problems here: 1. ANALYZE uses a "lossy counting" algorithm (dating to commit 0e5e167aa) to estimate the frequencies of array element values. The part of that that seems to be going off the rails is this selection of a cutoff frequency below which element values will be dropped: /* * Construct an array of the interesting hashtable items, that is, * those meeting the cutoff frequency (s - epsilon)*N. Also identify * the minimum and maximum frequencies among these items. * * Since epsilon = s/10 and bucket_width = 1/epsilon, the cutoff * frequency is 9*N / bucket_width. */ cutoff_freq = 9 * element_no / bucket_width; The first thing I find suspicious here is that the calculation is based on element_no (the total number of array elements processed) and not nonnull_cnt (the maximum possible frequency). Is that really right? It wouldn't change the results in your reproducer with just one element per array, but it seems bogus. More relevant to your immediate problem, this creates a behavioral cliff at the point where cutoff_freq rises from 0 to 1, which with the default attstattarget turns out to be, you guessed it, 15873 elements. In your example, all the element values have frequency 1, so that switches us from being willing to record all the values to being willing to record none of them. That doesn't feel right either. By analogy to our treatment of regular MCVs, it seems like we should never be willing to store values that we didn't see at least twice. Of course, doing that would make this example worse, because then we'd not store any "most common" elements at smaller rowcounts either. Which brings us to the other major problem this reveals: 2. In array_selfuncs.c, we fall back to default selectivity estimates if there's no MCELEM statistics field. What we should be doing, if there are other stats for the column but not MCELEM, is realizing that compute_array_stats did not find any elements that are common enough to be worth recording. Then we'd use some much lower-than-default estimate for the selectivity. I don't have any immediate patch to offer, but clearly this area could use another look. compute_array_stats seems to have borrowed the lossy-counting algorithm from ts_typanalyze, so we'd better take a look at that too. regards, tom lane
Re: Poor row estimates from planner, stat `most_common_elems` sometimes missing for a text[] column
I wrote: > The part of that that seems to be going off the rails is > this selection of a cutoff frequency below which element values > will be dropped: > cutoff_freq = 9 * element_no / bucket_width; > The first thing I find suspicious here is that the calculation is > based on element_no (the total number of array elements processed) > and not nonnull_cnt (the maximum possible frequency). Is that > really right? I did some more digging and found that that calculation was introduced (in the older tsvector code) in bc0f08092, which traces to this discussion: https://www.postgresql.org/message-id/flat/4BF4357E.6000505%40krogh.cc So the use of element_no is correct, because what we need to consider here is the total number of values fed to the LC algorithm. Also, my thought that maybe we should reject entries with f < 2 is bogus, because at the end of the algorithm f is not necessarily the true count of occurrences of the value: some early occurrences could have been forgotten via pruning. The "behavioral cliff" is annoying but I'm not sure there is much to be done about it: having a single (still-remembered) occurrence gets less and less significant as the total input size increases, so sooner or later you are going to hit a point where such values should be thrown away. So at this point I'm thinking that there is nothing wrong with ANALYZE's algorithm, although I now see that there are some relevant comments in ts_typanalyze.c that probably ought to be transposed into array_typanalyze.c. The idea of treating lack of MCELEM differently from complete lack of stats still seems to have merit, though. regards, tom lane
Re: Poor row estimates from planner, stat `most_common_elems` sometimes missing for a text[] column
On 6/5/25 17:42, Mark Frost wrote: Is there any good explanation for this behaviour? Preferably we’d like some way for proper `most_common_elems` statistics to be collected in our production database, in the hope that influences a good query plan to always be selected. most_common_elems has a limited size, and if all the elements have the same freq, there's nothing we can do. You could do: alter table test alter column tags set statistics X; However, X is capped at 1, which means that the size of most_common_elems will be less than 100k, and it would probably be stupid to go beyond that anyway. It seems that postgres lacks some kind of "n_distinct_elems" for that kind of case, but let's wait and see what the statistics gurus think.
Poor row estimates from planner, stat `most_common_elems` sometimes missing for a text[] column
Hello all,
We’re seeing intermittently very poor performance of a query, when occasionally
a poor query plan is chosen. We’re using Postgres 16.9.
One suspicious factor when looking at the EXPLAIN ANALYZE output, is a very
wrong estimated number of rows to be returned from a text[] column queried with
‘&&’.
After playing around with a simple recreate (details below), it seems ANALYZE
of the table is affected by the number of rows in the table. Statistic
`most_common_elems` is [null] when there’s over 15,873 rows in the table when
analyzed. With fewer rows it’s analyzed correctly.
Is there any good explanation for this behaviour? Preferably we’d like some way
for proper `most_common_elems` statistics to be collected in our production
database, in the hope that influences a good query plan to always be selected.
In our production system there’s ~150,000 rows in a table including a `text[]`
column, where each row has an array containing a single 19ish char string,
unique within the table. The full query joins against a couple more tables, and
has a GIN index on the text[] column. If necessary, I can get into details of
the real system, but hope the simple recreate will be sufficient to understand
the problem:
CREATE TABLE IF NOT EXISTS public.test(
id SERIAL PRIMARY KEY,
tags text[]
)
INSERT INTO public.test (tags)
SELECT ARRAY[TO_CHAR(n,'fm')] FROM ( SELECT
generate_series(1,15_873) AS n );
ANALYZE public.test;
SELECT * FROM pg_stat_user_tables WHERE relname = 'test';
EXPLAIN (ANALYZE,BUFFERS,VERBOSE)
SELECT * FROM test WHERE tags && ARRAY['0002']
Results
---
table with 15_000 rows has most_common_elems after ANALYZE
(most_common_elem_freqs : 6.67e-05)
table with 15_872 rows has most_common_elems after ANALYZE
(most_common_elem_freqs : 6.300403e-05)
table with 15_873 rows has [null] most_common_elems after ANALYZE
table with 100_000 rows has [null] most_common_elems after ANALYZE
Query plans show an estimated 1 row is predicted when statistics has
`most_common_elems` available, or the hardcoded default 1/200 of the estimated
table size when most_common_elems is null.
Here 79 rows are estimated, when the table contained 15,873 rows and stats
weren’t available.
Query plan
---
Seq Scan on public.test (cost=0.00..463.41 rows=79 width=37) (actual
time=9.934..17.190 rows=1 loops=1)
Output: id, tags
Filter: (test.tags && '{0002}'::text[])
Rows Removed by Filter: 15872
Buffers: shared hit=268
Planning:
Buffers: shared hit=75
Planning Time: 2.060 ms
Execution Time: 17.205 ms
Full version
"PostgreSQL 16.9 (Debian 16.9-1.pgdg120+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit"
Regards,
Mark Frost
IBM
Unless otherwise stated above:
IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road,
Winchester, Hampshire SO21 2JN
