n one side to completion because the
other side's largest join key value is less than the first side's
largest value. The merge can stop as soon as either input is
exhausted.
regards, tom lane
the
> conversion pain.
A year ago I would have seriously considered doing it that way.
But now that we have code to dump-n-restore stats, that code would
have to be adjusted to convert the old representation. It's not
worth it for this case.
Hence, v1 attached, now with a commit messag
ization?
[ shrug... ] Insufficient information. There could be some plan
caching going on that contributes to this effect, though.
regards, tom lane
that GEQO usually finds one of the better plans, but
when its randomized search is particularly unlucky it fails to.
Try bumping geqo_threshold to more than 12, and note whether that
results in unacceptable planning time for these queries. If not,
leave it at the higher value.
regards, tom lane
nvasive
is to get rid of the convention of storing the min/max/nulls
frequencies as extra entries in the MCELEM numbers entry ---
which surely is a hack too --- and put them into some new slot
type. I'm not sure if that's enough nicer to be worth the
conversion pain.
Thoughts?
omments 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
h 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
the lock.
More specifically: the inserts are only trying to get a shared lock.
If they are blocked, it's because some other operation is already
holding an exclusive lock on the table and is not letting go.
Look for uncommitted DDL changes.
More details about that at [1].
e first place, but leave partition pruning to the
executor, at least in cases where it can determine that that will be
possible.
regards, tom lane
that
there is no consideration at all. See around line 1370 in
src/backend/utils/cache/plancache.c.
regards, tom lane
re of that code well, but
there wasn't guesswork involved, other than where to set breakpoints
to narrow down the source more quickly.
I forgot to mention on this thread that I posted a possible fix at
[1].
regards, tom lane
[1] https://www.postgresql.org/mes
ion times are so close. But it could
be something to try to improve. We could trivially discount the
meta page for index types that have one. Discounting intermediate
upper pages would take more calculation (since we don't know a-priori
how many there are) and might not be worth the trouble.
regards, tom lane
is also the time needed to fetch them from out-of-line TOAST
storage, which is another thing that doesn't happen in EXPLAIN.
Since v17, EXPLAIN has grown a SERIALIZE option that causes it to
do everything except the network transmission, which might be
of interest in analyzing this further.
regards, tom lane
Dimitrios Apostolou writes:
> On Thu, 20 Mar 2025, Tom Lane wrote:
>> I am betting that the problem is that the dump's TOC (table of
>> contents) lacks offsets to the actual data of the database objects,
>> and thus the readers have to reconstruct that information by
Try labeling that CTE as NOT MATERIALIZED.
regards, tom lane
emote_storage
Yup, writing the output to a pipe would cause that ...
> What do you think causes this? Is it something that can be improved?
I don't see an easy way, and certainly no way that wouldn't involve
redefining the archive format. Can you write the dump to a local
file rather than piping it immediately?
regards, tom lane
0db500a79g#
> 171 031 ms !!
Raising cursor_tuple_fraction would probably help this case.
regards, tom lane
row's first index column, for the purposes of the logic
* about required keys.
That is, for the purposes of deciding when the index scan can stop,
the "<= ROW" condition acts like "data_model_id <= 123". So it will
run through all of the data_model_id = 123 entries before stopping.
regards, tom lane
SELECT ... WHERE per-row-condition
where the two subplans would be a seqscan and an indexscan.
But this hasn't come up often enough to motivate anyone
to build such a thing.
In the meantime, you might think about doing the if-then-else
manually on the application side.
regards, tom lane
on
and the WHEN NOT MATCHED BY SOURCE condition means that only t rows
meeting that condition are of interest, so that in principle we could
optimize by pushing that down to the scan of t. But as you can see,
we don't. Not sure if this pattern is common enough to be worth
trying to implement such an optimization.
regards, tom lane
gest
building it as an extension rather than trying to persuade people
it belongs in core Postgres.
regards, tom lane
g a better way would take
a great deal of creative effort and testing.
regards, tom lane
[ please don't top-quote, it makes the conversation hard to follow ]
David Mullineux writes:
> On Wed, 20 Nov 2024, 15:46 Tom Lane, wrote:
>> Yeah. Also, are you building with openssl, or not?
> No, not at all!
If you're not using openssl, then gen_random_uuid basically
uilding with openssl, or not?
regards, tom lane
consult statistics for those columns. That can lead
to a different, less optimal plan being used. Maybe something like
that is happening here?
regards, tom lane
value, but so far not backed by any great deal of hard evidence.)
regards, tom lane
whose
estimated cost depends enormously on random_page_cost. You've given
us exactly zero detail about your test conditions, so it's hard to say
more than that.
regards, tom lane
riosity, was this AI-generated?
regards, tom lane
can do about it.
regards, tom lane
try=0x7fabcde7eed0,
key=key@entry=0x7ffddd3b10c0, buf=) at nbtsearch.c:394
It's not the fault of the index machinery, because a single comparison
takes the same amount of time:
u8=# select '<' <= repeat('<', 65536);
?column?
--
t
(1 row)
Time: 1391.550 ms (00:01.392)
I didn't try it with ICU.
regards, tom lane
of the b_idx result is based on considering the
selectivity of "t2.b = ?" where the comparison value is not known.
Because there are so many b values that are unique, we end up
estimating the average number of matching rows as 1 even though
it will be far higher for a few values.
regards, tom lane
Filter: (c = 10)
> (6 rows)
I tried to reproduce this and could not. What PG version are you
running exactly? Can you provide a self-contained example?
regards, tom lane
significantly
cheaper than scanning the whole table.
regards, tom lane
he particular tuples you were fetching were
in not-all-visible pages. That seems plausible to me.
regards, tom lane
Peter Geoghegan writes:
> On Sun, Aug 18, 2024 at 10:50 PM Tom Lane wrote:
>> Yeah, that part is a weakness I've wanted to fix for a long
>> time: it could do the filter condition by fetching b from the
>> index, but it doesn't notice that and has to go to the he
by fetching b from the
index, but it doesn't notice that and has to go to the heap
to get b. (If the other plan does win, it'd likely be because
of that problem and not because the index scanning strategy
per se is better.)
regards, tom lane
idate
a conclusion that an index with idx_scan = 0 isn't being used, but
it's something to keep in mind when running small tests that are
only expected to record a few events.
regards, tom lane
o be run to
completion. See initial_cost_mergejoin in costsize.c.
regards, tom lane
he alternatives won't be better
in terms of performance impact. Really, if this is a problem
for you, you need a beefier server. Or split the work across
more than one server.
regards, tom lane
nd so with a generic plan the planner will
not risk using a plan that depends on the parameter value
being infrequent, as the one you're showing does.
regards, tom lane
Michal Charemza writes:
> Tom Lane writes:
>> I'm fairly sure that if that exists it's always noticed first,
>> bypassing the need for any role membership tests. So please
>> confirm whether your production database has revoked PUBLIC
>> connect privilege.
Michal Charemza writes:
> Tom Lane writes:
>> It's not very clear what you mean by "sometimes". Is the slowness
> reproducible for a particular user and role configuration, or does
> it seem to come and go by itself?
> Ah it's more come and go by itself -
. If so, we could consider back-patching 14e991db8
further than v16 ... but I don't plan to take any risk there without
concrete evidence that it'd improve things.
regards, tom lane
ht not find that the query gets any faster.
regards, tom lane
#x27;s a bad idea.
regards, tom lane
en as an equality condition, no. It's pretty much
of a hack that makes it an indexable condition at all, and we don't
really do any advanced optimization with it.
regards, tom lane
but
* subquery_is_pushdown_safe handles that.)
To conclude that it'd be safe with this particular window function
requires deep knowledge of that function's semantics, which the
planner has not got.
regards, tom lane
that are needed.
It depends. The planner may choose to tell a non-top-level scan node
to return all columns, in hopes of saving a tuple projection step at
runtime. That's heuristic and depends on a number of factors, so you
shouldn't count on it happening or not happening.
regards, tom lane
mably $1 and $2 were replaced as well; we don't
do half-custom plans.)
regards, tom lane
to see if that's
meaningfully slower -- if not, replanning each time is deemed to be
wasteful.
regards, tom lane
Michael Paquier writes:
> On Mon, Dec 04, 2023 at 09:57:24AM -0500, Tom Lane wrote:
>> Jerry Brenner writes:
>>> Both Oracle and SQL Server have
>>> consistent hash values for query plans and that makes it easy to identify
>>> when there are multiple plans fo
plain
> plan)?
No, there's no support currently for obtaining a hash value that's
associated with a plan rather than an input query tree.
regards, tom lane
ore) it is a real problem.
PG 11 is out of support as of earlier this month, so your users really
need to be prioritizing getting onto more modern versions.
regards, tom lane
SELECT * FROM that_table WHERE ctid = '(0,1)';
to see the previous state of the problematic tuple. Might
help to decipher the problem.
regards, tom lane
has a bloated index, and that's driving
up the estimated cost enough to steer the planner away from it.
regards, tom lane
7;ve given so far, little is possible beyond speculation.
regards, tom lane
is is normal.
Sure. The output of the WITH is visibly unique on c1.
regards, tom lane
;tenant_id" column in all our indexes to make them work
> under RLS?
Adding tenant_id is going to bloat your indexes quite a bit,
so I wouldn't do that except in cases where you've demonstrated
it's important.
regards, tom lane
t you're using some
extension that isn't happy.
regards, tom lane
icated multi-pass sort rules.
AFAICT from the FreeBSD sort(1) man page, FreeBSD defines en_US
as "same as C except case-insensitive", whereas I'm pretty sure
that underscores and other punctuation are nearly ignored in
glibc's interpretation; they'll only be taken into account if the
alphanumeric parts of the strings sort equal.
regards, tom lane
. I speculate that you are
using different collations on the two systems, and FreeBSD's collation
happens to place the first matching row earlier in the index.
regards, tom lane
le
I doubt fixing that would move the needle greatly, it still
seems sloppy.
regards, tom lane
ll take days to do this. Maybe I can try to dump the whole database
> and restore it on another machine.
Pretty hard to believe that dump-and-restore would be faster than
VACUUM.
> (Is there a way to check the number of dead rows?)
I think contrib/pgstattuple might help.
regards, tom lane
ndex, because reading a single
> (random?) entry from an index should not run for >10 minutes.
You should believe what EXPLAIN tells you about the plan shape.
(Its rowcount estimates are only estimates, though.)
regards, tom lane
speed of
deletes from the PK table ...
regards, tom lane
a LOT on the merge side.
Hmm. The planner should avoid using a merge join if it knows that
to be true. Maybe analyze'ing that table would prompt it to use
some other join method?
regards, tom lane
ved?
If the sort is the inner input to a merge join, this could reflect
mark-and-restore rescanning of the sort's output. Are there a
whole lot of duplicate keys on the merge's other side?
regards, tom lane
| v
> 17097 | regexp_replace | oracle | f | f | v
> 17098 | regexp_replace | oracle | f | f | v
Why in the world are the oracle ones marked volatile? That's what's
preventing them from being used in index quals.
regards, tom lane
th such a fragmentary
description of the problem. Please send a complete, self-contained
test case if you want anybody to look at it carefully.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
trigger. Perhaps
auto_explain with auto_explain.log_nested_statements enabled
would give some insight.
I suspect there might be a permissions problem causing schema1_u
to not be allowed to "see" the statistics for table_b, resulting
in a bad plan choice for the FK enforcement query; but that's
nteresting to compare exactly that
test case on your ARM board.
regards, tom lane
lly if performance is a
problem you should think about ditching the star schema design.
regards, tom lane
obody's gotten
around to implementing that in Postgres AFAIK.
regards, tom lane
at unlabeled string constants will tend to
get resolved to that.)
regards, tom lane
re told got committed.)
If you do need strict ACID compliance, get a better disk subsystem.
Or, perhaps, just a better OS ... Windows is generally not thought of
as the best-performing platform for Postgres.
regards, tom lane
ssive TCP keepalive
parameters might help.
regards, tom lane
guaranteed to satisfy the domain check, because the
> domain check is guaranteed to be immutable (per [1] in my original mail)
immutable != "will accept null".
There could be some more optimizations here, perhaps, but there aren't.
regards, tom lane
ter the ALTER begins
waiting.
regards, tom lane
Note that changing planner parameters on the basis of a single
query getting slower is a classic beginner error. You need
to think about the totality of the installation's workload.
regards, tom lane
s per spec.
regards, tom lane
as a valid bug though. That would
require a vastly more complicated implementation.
regards, tom lane
actually had to
check even more than 155K rows.
You need a better index. It might be that switching to a jsonb_path_ops
index would be enough to fix it, or you might need to build an expression
index matched specifically to this type of query. See
https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
Also, if any of the terminology there doesn't make sense, read
https://www.postgresql.org/docs/current/indexes.html
regards, tom lane
r. It is
a rough rule of thumb that was invented for far smaller machines than
what you're talking about here.
regards, tom lane
in WHERE. You have to help it along with
UNION or some similar locution.
regards, tom lane
d be likely to have motivation to improve things.
regards, tom lane
a lot of work to make these estimators
better, have at it.
regards, tom lane
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/backend/utils/adt/geo_selfuncs.c;hb=HEAD
users who will
not be happy with that. We really need to prioritize fixing the
cost-estimation problems, and/or tweaking the default thresholds.
regards, tom lane
here are serious bugs in the cost-estimation algorithms
for deciding when to use it. A nearby example[1] of a sub-1-sec
partitioned query that took 30sec after JIT was enabled makes me
wonder if we're accounting correctly for per-partition JIT costs.
reg
the startup costs,
which is where the hash index wins. I'm not sure if it's quite
fair to give hash a zero startup cost; but it doesn't have to
descend a search tree, so it is fair that its startup cost is
less than btree's.
regards, tom lane
which joins
> could be avoided.
I believe only left joins to single tables can be elided ATM.
It's too hard to prove uniqueness of the join key in more-
complicated cases.
regards, tom lane
ilently does nothing on platforms lacking
ADDR_NO_RANDOMIZE and PROC_ASLR_FORCE_DISABLE. Are you asserting
there are no such platforms?
(I'm happy to lose the comment if it's really useless now, but
I think we have little evidence of that.)
regards, tom lane
Thomas Munro writes:
> On Tue, Aug 23, 2022 at 4:57 AM Tom Lane wrote:
> +service the requests, with those clients receiving unhelpful
> +connection failure errors such as Resource temporarily
> +unavailable.
> LGTM but I guess I would add "... or Connection re
that without somebody making a well-reasoned case for
some other number.
regards, tom lane
n MaxBackends.
I think the most appropriate definition for the listen queue
length is now MaxConnections * 2, not MaxBackends * 2, because
the other processes counted in MaxBackends don't correspond to
incoming connections.
I propose 0003 for HEAD only, but the docs changes could be
back-pa
document at least three different
sysctl names for this setting :-(
regards, tom lane
Thomas Munro writes:
> On Mon, Aug 22, 2022 at 12:20 PM Tom Lane wrote:
>> Hmm. It'll be awhile till the 128 default disappears entirely
>> though, especially if assorted BSDen use that too. Probably
>> worth the trouble to document.
> I could try to write a doc p
tone-age kernels.
It's hard to believe any modern kernel can't defend itself against
silly listen-queue requests.
regards, tom lane
q retry after
EAGAIN. It would make sense for this particular undocumented use
of EAGAIN, but I'm worried about others, especially the documented
reason. On the whole I'm inclined to leave the code alone;
but is there sufficient reason to add something about adjusting
somaxconn to our documentation?
regards, tom lane
Andrew Dunstan writes:
> On 2022-08-21 Su 17:15, Tom Lane wrote:
>> On the whole this is smelling more like a Linux kernel bug than
>> anything else.
> *nod*
Conceivably we could work around this in libpq: on EAGAIN, just
retry the failed connect(), or maybe better to close t
Andrew Dunstan writes:
> On 2022-08-20 Sa 23:20, Tom Lane wrote:
>> Kevin McKibbin writes:
>>> What's limiting my DB from allowing more connections?
> The first question in my mind from the above is where this postgres
> instance is actually listening. Is it re
1 - 100 of 426 matches
Mail list logo
