and then allow the optimization for clauses that
reference only such columns. Or maybe I'm misreading the comment
(but then it needs clarification).
regards, tom lane
uration snapshots, which might not be representative of a 10-hour
run. XCode's Instruments feature would probably be better about giving
a full picture, but it has a steep learning curve.
regards, tom lane
Robert Haas writes:
> On Tue, Nov 21, 2017 at 12:01 PM, Tom Lane wrote:
>> Can you get a profile of where the machine is spending its time during the
>> dump run? On Linux I'd recommend "perf", but on macOS, hmm ...
>> You could use Activity Monitor, but as
ly surprising in itself, but it offers
no explanation for why you'd see a slowdown --- zlib isn't even our
code, nor has it been under active development for a long time, so
presumably 8.4 and 9.5 would have used the same version. Perhaps you
were doing the 8.4 dump without compression enabl
would create
stats that would allow decent estimates for "WHERE boolval".
regards, tom lane
10, but that's just a guess at this point.
Trying simple test queries involving WHERE x IN (SELECT DISTINCT
unnest(foo) FROM ...), I do not see a behavior like this, so there is some
not-very-obvious contributing factor in your situation. Can you put
together a self-contained test case that produces a bogus one-row
estimate? Extra points if it produces duplicate HashAgg steps.
regards, tom lane
he
individual SRF's behavior.
In short, I propose the attached fixes. I've checked this and it seems
to fix Dmitry's original problem according to the test case he sent
off-list.
regards, tom lane
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src
t -91 lines).
Hunk #5 succeeded at 3570 (offset -91 lines).
regards, tom lane
when can I expect 10.2, approximately of course?
I haven't pushed it to the git repo yet, but I will shortly, and then
it will be in the next minor release. That will probably be in
early February, per our release policy:
https://www.postgresql.org/developer/roadmap/
regards, tom lane
valent concept if you're not on Red Hat) is a
good skill to have.
regards, tom lane
triction clause on the "cim" relation,
not a join clause. So it will get pushed down into the subquery
without creating any join order constraints on the outer query.
regards, tom lane
ng if that
doesn't improve matters.
(BTW, what tipped me off to this was that the "buffers hit" count for
the seqscan node was so high, several times more than the actual size
of the table. I couldn't account for that until I realized that the
function itself would be adding a few buffer hits per execution.)
regards, tom lane
dependency statistics are supposed to fix exactly this type of problem.
I suspect there may be something in the extended-stats code that causes it
not to work right for boolean columns --- this wouldn't be excessively
surprising because of the way the planner messes around with converting
"flag = true" to just "flag" and sometimes back again. But I've not
looked closer yet.
regards, tom lane
you propose doing instead? We'd have to do something with
ties, and it's not so obvious this way is wrong.
regards, tom lane
s
deficiency.
In addition to the bugfix proper, I improved some comments, got rid of
a NumRelids() test that's redundant with the preceding bms_membership()
test, and fixed dependencies_clauselist_selectivity so that
estimatedclauses actually is a pure output argument as stated by its
API contract.
er as you can see here:
The usual suspect for this is not having an index on some FK referencing
column, thus forcing the FK check trigger to seq-scan the entire
referencing table for each referenced row that is to be deleted.
regards, tom lane
the referencing columns, so
> the query that scans the table to find the referencing rows is a
> seqscan.
Actually though ... the weird thing about this is that I'd expect to
see a separate line in the EXPLAIN output for time spent in the FK
trigger. Where'd that go?
regards, tom lane
Jeff Janes writes:
> On Dec 3, 2017 15:31, "Tom Lane" wrote:
>> Jeff Janes writes:
>>> But I do see that ties within the logical order of the column values are
>>> broken to agree with the physical order. That is wrong, right? Is there
>>> any ar
" role is a member of a whole lot
of roles?
regards, tom lane
=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= writes:
> 2017-12-07 17:01 GMT+01:00 Tom Lane :
>> It looks like the first time such a question is asked within a session,
>> we build and cache a list of all the roles the session user is a member
>> of (directly or indirectly). That's w
est individual files in the
database directory ...
regards, tom lane
y mentioned pg_largeobject upthread --- that would definitely
be a candidate to be big, if you're using large objects at all.
regards, tom lane
e_limit to force the planner to follow the syntactic
join order. In this way you'd get rid of most of the run-time
join order search effort. Don't know how cooperative your ORM
would be with such an approach though.
regards, tom lane
ess of geqo_threshold.)
regards, tom lane
llocate could not find %zu free pages", npages);
Now maybe that comment is being unreasonably optimistic, but it sure
appears that this is supposed to be a can't-happen case, in which case
you've found a bug.
cc'ing the DSA authors for comment.
regards, tom lane
possibly fix that by installing
extended statistics on that pair of columns. See
https://www.postgresql.org/docs/current/static/planner-stats.html#PLANNER-STATS-EXTENDED
regards, tom lane
ls in the column, why are you insisting on specifying a nondefault
value of NULLS FIRST/LAST in the query?
regards, tom lane
ours remaining
before the planned release wrap, it's unlikely that anything but the most
trivial fixes could get done in time.
regards, tom lane
#x27;s a limited amount that the planner
can do with it.
regards, tom lane
concept of different temp spaces having different performance
characteristics, and anyway we don't really have enough info to make
accurate predictions of temp space consumption. So it's hard to see the
planner doing this for you automagically.
regards, tom lane
ws=4625123 loops=1)
I don't think we have parallel IOS yet (I might be wrong). If so,
it probably thinks this is cheaper than the best available parallel plan.
> If I just get the count it will use a parallel query
Likely a parallelized aggregation.
regards, tom lane
I wonder if the OP's restart process involves calling
pg_resetxlog or something like that (which would be risky as heck).
regards, tom lane
id you re-ANALYZE the new
database? pg_dump doesn't take care of that for you, and auto-analyze
might not think it needs to process the smaller tables.
regards, tom lane
he statistics target
for stock_trans.product_id. I'm not sure why you weren't getting
bitten by the same issue in 9.1; but the cost estimates aren't
that far apart for the two plans, so maybe you were just lucky ...
regards, tom lane
ables) and that restricting
the cost of the join plan search is really what he needs to do.
Lacking any further information about the problem, we can't say.
We can, however, point to
https://wiki.postgresql.org/wiki/Slow_Query_Questions
concerning how to ask this type of question effectively.
regards, tom lane
en plan on updating to some newer PG
release; 8.4.x has been out of support for years, and there are lots
of known-and-unfixed bugs in it.
regards, tom lane
ostgresql.org/wiki/Slow_Query_Questions
regards, tom lane
ts or reformulating
the filter conditions in a way the optimizer understands better.
regards, tom lane
ndary search condition, which would
help even more.
There's relevant advice about index design in the manual,
https://www.postgresql.org/docs/current/static/indexes.html
(see 11.3 and 11.5 particularly)
regards, tom lane
a in
the table, so maybe you could make a small self-contained example
using a script to generate dummy data.
regards, tom lane
=?UTF-8?Q?Marc_Recht=c3=a9?= writes:
> Le 03/03/2022 à 16:31, Tom Lane a écrit :
>> Does memory consumption hold steady if you drop the FK constraints?
> Actually the number of rows is 232735712.
> Accordingly the RAM consumption would be x12 x3 = 7.8 GiB.
> This is close to t
e a big chunk of your
problem, in which case physically grouping multiple rows into each INSERT
command (... or COPY ...) is the only way to fix it. But I'd start with
trying to reduce the transaction commit overhead.
regards, tom lane
Can you put together a self-contained test case that
demonstrates what you're seeing?
regards, tom lane
schemas for the tables,
as well as EXPLAIN's output for this query. I'm wondering
exactly which PG version this is, too.
regards, tom lane
wise, but they're wrong for exactly the
reason that it's impossible to prune hash partitions.)
regards, tom lane
n distribute these rows to parallel workers)
Your plan-shape complaint had nothing to do with insertions; it had
to do with joining the partitioned table to another table. That
join can't be optimized.
regards, tom lane
riends [1]. Depending on what you mean by
"final transformed query", you might instead want debug_print_rewritten,
or maybe you want the plan, in which case EXPLAIN is a much friendlier
way to look at it than debug_print_plan.
regards, tom lane
[1]
https://www.
column;
or at least, it wouldn't tempt the planner to try this unstably-
performing plan. It's trying to use the index ordering to satisfy
the ORDER BY, which works great as long as it finds a dataview
match in some reasonably recent index entry. Otherwise, it's
going to crawl the whole
f the estimated size of the hash
table exceeds work_mem. In this case, boosting work_mem would be
a mighty good idea.
regards, tom lane
's pretty old. We've made a number of changes to the LISTEN/NOTIFY
code since then; although in reading the commit log entries about them,
nothing is said about long-delayed notifications.
regards, tom lane
fy the filter
before it's gone very far in this index. If the shipping date and
pkey are correlated in the wrong direction, that could be a very
overoptimistic guess. I don't think we have adequate stats yet
to detect this sort of problem.
regards, tom lane
in the column.
but I'm not sure whether that's relevant here.
One thought is that if there is a pg_statistic row but it contains
no MCE list, we could assume that the column elements are all distinct
and see what sort of estimate that leads us to.
regards, tom lane
"James Pang (chaolpan)" writes:
> 1. extensions
> shared_preload_libraries =
> 'orafce,pgaudit,pg_cron,pg_stat_statements,set_user'
Can you still reproduce this if you remove all of those?
regards, tom lane
a
bad rowcount estimate it should help with that. It's easier to read too.
regards, tom lane
fault jsonb_path_ops opclass [1]. I'm not sure if that'd be
faster for this scenario, but it seems worth trying.
regards, tom lane
[1] https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
than it is for SELECT.
regards, tom lane
n updating your app, instead.
regards, tom lane
Alter
> table set xxx logged 6. Create index …
The easy answer is to skip steps 3 and 5.
regards, tom lane
g cost directly, but it would save overhead later.
regards, tom lane
w that can come
to be. Do you have a lot of especially long statements being tracked
in the pg_stat_statements view? Are there any other signs of distress
in the postmaster log, like complaints about being unable to write
pgss_query_texts.stat?
regards, tom lane
de though. Are you quite
sure this is a 9.5.21 version of the pg_stat_statements extension?
Is it possible that the pg_stat_tmp directory has been made non-writable?
regards, tom lane
> Are there any other signs of distress
> in the postmaster log, like co
uld build back up to 2.2GB of text
after five thousand or so of those.
I'm also curious whether this installation is in the habit of doing
pg_stat_statements_reset() a lot. It looks like that fails to
reset mean_query_len, which might be intentional but perhaps it
could play into getting a silly result here later on.
regards, tom lane
_stat_statements.max from 10k to 3k
Whether or not we've fully identified the problem, I think cutting
pg_stat_statements.max is a good idea. Especially as long as you're
stuck on an unsupported PG version.
regards, tom lane
pport the number of connections you want. Since you haven't
mentioned what platform this is on, it's impossible to say more
than that --- but it doesn't look like Postgres configuration
settings are at issue at all.
regards, tom lane
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
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
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
tone-age kernels.
It's hard to believe any modern kernel can't defend itself against
silly listen-queue requests.
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
document at least three different
sysctl names for this setting :-(
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
that without somebody making a well-reasoned case for
some other number.
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
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
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
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
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
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
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
d be likely to have motivation to improve things.
regards, tom lane
in WHERE. You have to help it along with
UNION or some similar locution.
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
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
as a valid bug though. That would
require a vastly more complicated implementation.
regards, tom lane
s per spec.
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
ter the ALTER begins
waiting.
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
ssive TCP keepalive
parameters might help.
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
at unlabeled string constants will tend to
get resolved to that.)
regards, tom lane
obody's gotten
around to implementing that in Postgres AFAIK.
regards, tom lane
lly if performance is a
problem you should think about ditching the star schema design.
regards, tom lane
nteresting to compare exactly that
test case on your ARM board.
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
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
| 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
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
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
speed of
deletes from the PK table ...
regards, tom lane
1 - 100 of 411 matches
Mail list logo
