Re: is JIT available
> On Jul 25, 2020, at 8:21 AM, Pavel Stehule wrote: > > > > so 25. 7. 2020 v 14:04 odesílatel Scott Ribe > napsal: > > On Jul 24, 2020, at 9:55 PM, Pavel Stehule wrote: > > > > SELECT * FROM pg_config; > > That doesn't tell me whether or not it can actually be used. > > It shows if Postgres was compiled with JIT support. > > When you run EXPLAIN ANALYZE SELECT ... then you can see info about JIT > overhead. If you don't see notices about JIT in EXPLAIN, then JIT was not > used. I like Pavel’s 'EXPLAIN ANALYZE SELECT’ suggestion a lot. I think setting jit=on and jit_above_cost=1 and then running 'EXPLAIN ANALYZE SELECT’ is a very effective way to see whether jit is available in practice. On installations where jit isn’t available (like on my Mac or on AWS Aurora), you can still set jit=on in a session and Postgres doesn’t complain, but that doesn’t mean it’s actually enabled. Cheers Philip
Re: shp2pgsql is missing
Thanks, Christoph! Looks like we'll be able to fix this now that we know what to do. Sue --- Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hu...@brookhurstdata.com Mobile: 314-486-3261 On 2020-07-26 14:58, Christoph Moench-Tegeder wrote: ## Susan Hurst (susan.hu...@brookhurstdata.com): OS: FreeBSD 12.1-RELEASE-p7 FreeBSD 12.1-RELEASE-p7 GENERIC amd64 There's your answer: the FreeBSD port of PostGIS 3.0 (databases/postgis30) installs shp2pgsql only if option LOADERGUI has been enabled on the port's build (the port defaults to LOADERGUI=off). You need to rebuild PostGIS or convince the maintainer of that port to switch the default (the usual way for that is the FreeBSD bugzilla system). Perhaps one could even argue that LOADERGUI should only toggle shp2pgsql-gui, but I haven't really looked into that. Regards, Christoph
Re: is JIT available
> On Jul 25, 2020, at 8:02 AM, Christoph Moench-Tegeder > wrote: > > ## Scott Ribe (scott_r...@elevated-dev.com): > >> So JIT is enabled in your conf, how can you tell from within a client >> session whether it's actually available (PG compiled with it and >> compiler available)? > > pg_jit_available() boolean is JIT compilation available in this session > > https://www.postgresql.org/docs/12/functions-info.html Thanks, that seems to be exactly what I was looking for. Even though the documentation is not clear, it does return false when jit = on but PG was not compiled with JIT.
Re: How does vacuum works in postgresql
Your question is a bit vague, and your referenced article is quite old. It doesn't reference pg_stat_progress_vacuum* even despite 9.6 being out for a while before the article. What version are you using and what questions or problems do you have that you think are related to vacuum? *See below https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PROGRESS-REPORTING
Re: Format generation_expression
Are you caching the definition some other place in the application stack and checking it later to compare? If so, I would likely alter the table and in the same transaction read the definition as recorded in generation_expression from information_schema.columns. Seems simple enough, but not sure of your overall process. >
Re: bad JIT decision
On 2020-Jul-24, Andres Freund wrote: > I think the issue is more that we need to take into accoutn that the > overhead of JITing scales ~linearly with the number of JITed > expressions. And that's not done right now. I've had a patch somewhere > that had a prototype implementation of changing the costing to be > #expressions * some_cost, and I think that's a lot more accurate. I don't quite understand why is it that a table with 1000 partitions means that JIT compiles the thing 1000 times. Sure, it is possible that some partitions have a different column layout, but it seems an easy bet that most cases are going to have identical column layout, and so tuple deforming can be shared. (I'm less sure about sharing a compile of an expression, since the varno would vary. But presumably there's a way to take the varno as an input value for the compiled expr too?) Now I don't actually know how this works so please correct if I misunderstand it. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: bad JIT decision
> On Jul 27, 2020, at 4:00 PM, Alvaro Herrera wrote: > > I don't quite understand why is it that a table with 1000 partitions > means that JIT compiles the thing 1000 times. Sure, it is possible that > some partitions have a different column layout, but it seems an easy bet > that most cases are going to have identical column layout, and so tuple > deforming can be shared. (I'm less sure about sharing a compile of an > expression, since the varno would vary. But presumably there's a way to > take the varno as an input value for the compiled expr too?) Now I > don't actually know how this works so please correct if I misunderstand > it. I'm guessing it's because of inlining. You could optimize a function that takes parameters, no problem. But what's happening is inlining, with parameters, then optimizing.
Re: bad JIT decision
Hi, On 2020-07-25 10:54:18 -0400, Tom Lane wrote: > David Rowley writes: > > ... nested at the bottom level join, about 6 joins deep. The lack of > > any row being found results in upper level joins not having to do > > anything, and the majority of the plan is (never executed). > > On re-reading this, that last point struck me forcibly. If most of > the plan never gets executed, could we avoid compiling it? That is, > maybe JIT isn't JIT enough, and we should make compilation happen > at first use of an expression not during executor startup. That unfortunately has its own downsides, in that there's significant overhead of emitting code multiple times. I suspect that taking the cost of all the JIT emissions together into account is the more promising approach. Greetings, Andres Freund
Re: bad JIT decision
On 2020-Jul-27, Scott Ribe wrote: > > On Jul 27, 2020, at 4:00 PM, Alvaro Herrera > > wrote: > > > > I don't quite understand why is it that a table with 1000 partitions > > means that JIT compiles the thing 1000 times. Sure, it is possible that > > some partitions have a different column layout, but it seems an easy bet > > that most cases are going to have identical column layout, and so tuple > > deforming can be shared. (I'm less sure about sharing a compile of an > > expression, since the varno would vary. But presumably there's a way to > > take the varno as an input value for the compiled expr too?) Now I > > don't actually know how this works so please correct if I misunderstand > > it. > > I'm guessing it's because of inlining. You could optimize a function > that takes parameters, no problem. But what's happening is inlining, > with parameters, then optimizing. Are you saying that if you crank jit_inline_above_cost beyond this query's total cost, the problem goes away? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: bad JIT decision
On Tue, 28 Jul 2020 at 11:00, Andres Freund wrote: > > On 2020-07-25 10:54:18 -0400, Tom Lane wrote: > > David Rowley writes: > > > ... nested at the bottom level join, about 6 joins deep. The lack of > > > any row being found results in upper level joins not having to do > > > anything, and the majority of the plan is (never executed). > > > > On re-reading this, that last point struck me forcibly. If most of > > the plan never gets executed, could we avoid compiling it? That is, > > maybe JIT isn't JIT enough, and we should make compilation happen > > at first use of an expression not during executor startup. > > That unfortunately has its own downsides, in that there's significant > overhead of emitting code multiple times. I suspect that taking the > cost of all the JIT emissions together into account is the more > promising approach. Is there some reason that we can't consider jitting on a more granular basis? To me, it seems wrong to have a jit cost per expression and demand that the plan cost > #nexprs * jit_expr_cost before we do jit on anything. It'll make it pretty hard to predict when jit will occur and doing things like adding new partitions could suddenly cause jit to not enable for some query any more. ISTM a more granular approach would be better. For example, for the expression we expect to evaluate once, there's likely little point in jitting it, but for the one on some other relation that has more rows, where we expect to evaluate it 1 billion times, there's likely good reason to jit that. Wouldn't it be better to consider it at the RangeTblEntry level? David
Re: is JIT available
On Tue, 28 Jul 2020 at 04:18, Scott Ribe wrote: > > > On Jul 25, 2020, at 8:02 AM, Christoph Moench-Tegeder > > wrote: > > pg_jit_available() boolean is JIT compilation available in this session > > > > https://www.postgresql.org/docs/12/functions-info.html > > Thanks, that seems to be exactly what I was looking for. > > Even though the documentation is not clear, it does return false when jit = > on but PG was not compiled with JIT. If it's not clear we can certainly change it. I looked at the manual page. It says: "is JIT compilation available in this session (see Chapter 31)? Returns false if jit is set to false." Maybe this would be better? "returns true if jit is enabled and JIT compilation is available in this session (see Chapter 31)." Open to other suggestions. David
postgres stats on the new primary
I have a RDS PostgreSQL v11.6 with primary and standby. On the primary, vacuum/analyze were running and pg_stat_all_tables's last_vacuum/analyze/autovacuum/autoanalyze were having values when vacuum/analyze were run. 1. Switchover to Standby happened, now when I see pg_stat_all_tables (on the active primary which was standby earlier), the last vacuum/analyze columns are all nulls. Would primary-standby replication not replicate system tables? Is this how it behaves? 2. On the new primary, would the table have stats on them? (I do see pg_stats entries for the tables on new standby), are they reliable or do I need to gather stats and run vacuum after failover/switchover to standby?
Re: postgres stats on the new primary
On 2020/07/28 9:08, Ayub M wrote: > I have a RDS PostgreSQL v11.6 with primary and standby. On the primary, vacuum/analyze > were running and pg_stat_all_tables's last_vacuum/analyze/autovacuum/autoanalyze > were having values when vacuum/analyze were run. > > 1. Switchover to Standby happened, now when I see pg_stat_all_tables (on the active > primary which was standby earlier), the last vacuum/analyze columns are all nulls. > Would primary-standby replication not replicate system tables? Is this how it behaves? The information shown by the various pg_stat_* views is specific to the local server, and is *not* replicated. In most cases that wouldn't even make sense. The information is collated by the statistics collector [1] and is stored in memory and/or files in the data directory, not as a system table. [1] https://www.postgresql.org/docs/current/monitoring-stats.html > 2. On the new primary, would the table have stats on them? (I do see pg_stats > entries for the tables on new standby), are they reliable or do I need to gather > stats and run vacuum after failover/switchover to standby? The statistics about the *contents* of the database, as shown by pg_stats, are of course replicated and will be reliable. More precisely they'll be as up-to-date as the last vacuum/analyze on the former primary, just you won't see the information about when that was. Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: is JIT available
David Rowley writes: > Maybe this would be better? > "returns true if jit is enabled and JIT compilation is available in > this session (see Chapter 31)." The general, non-hacker meaning of "jit is enabled" would seem to be pretty much what this function is already doing; and for that matter, the same can be said for "JIT compilation is available". We need something that's less tautological-looking. Maybe along the lines of "returns true if a JIT compiler extension is available and the jit parameter is set to on; when this is true, JIT compilation will be performed." ? regards, tom lane
Re: is JIT available
On Tue, 28 Jul 2020 at 15:33, Tom Lane wrote: > > David Rowley writes: > > Maybe this would be better? > > > "returns true if jit is enabled and JIT compilation is available in > > this session (see Chapter 31)." > > The general, non-hacker meaning of "jit is enabled" would seem to > be pretty much what this function is already doing; and for that > matter, the same can be said for "JIT compilation is available". > We need something that's less tautological-looking. Maybe along > the lines of > > "returns true if a JIT compiler extension is available and the > jit parameter is set to on; That's probably better. FWIW, the "jit" is already a link to the GUC docs, so I had in mind that users would have known we meant "jit" the GUC rather than "jit" the feature. Your wording will help for anyone who thinks we're talking about the feature. > when this is true, JIT compilation will be performed." I'd probably drop this part since it's not really true. The query has to exceed the cost thresholds before that'll happen. David
