Re: Catching up with performance & PostgreSQL 15
On Tue, 2022-11-29 at 19:09 +0100, Alvaro Herrera wrote: > On 2022-Nov-29, Mladen Gogala wrote: > > > Hmmm, I think I will run pgbench with and without JIT on and see > > the > > difference. > > I doubt you'll notice anything, because the pgbench queries will be > far > below the JIT cost, so nothing will get JIT compiled at all. Or are > you > planning on using a custom set of queries? > Nope. I am planning to set jit_above_cost parameter to 5. That should take care of the pgbench problem. Other than that, you're right: JIT should not be used for OLTP. However, pure OLTP or DW databases are a rarity these days. Reporting is a crucial function and almost every OLTP database that I've seen also has reporting function, which means that there are complex queries to be executed. -- Mladen Gogala Database Consultant https://dbwhisperer.wordpress.com
Re: Catching up with performance & PostgreSQL 15
On 11/29/22 03:36, Alvaro Herrera wrote: On 2022-Nov-28, Mladen Gogala wrote: As for JIT, I've recently asked that question myself. I was told that PostgreSQL with LLVM enabled performs approximately 25% better than without it. Hmm, actually, normally you're better off turning JIT off, because it's very common to diagnose cases of queries that become much, much slower because of it. Some queries do become faster, but it's not a wide margin, and it's not a lot. There are rare cases where JIT is beneficial, but those tend to be queries that take upwards of several seconds already. IMO it was a mistake to turn JIT on in the default config, so that's one thing you'll likely want to change. Hmmm, I think I will run pgbench with and without JIT on and see the difference. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Catching up with performance & PostgreSQL 15
On 11/29/22 03:31, Alvaro Herrera wrote: On 2022-Nov-28, Mladen Gogala wrote: You'll probably be glad to learn that we have hints now. What hints are you talking about? As I understand, we still don't have Oracle-style query hints. https://github.com/ossc-db/pg_hint_plan -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Catching up with performance & PostgreSQL 15
On 11/28/22 21:59, Josh Berkus wrote: Hey, folks: I haven't configured a PostgreSQL server since version 11 (before that, I did quite a few). What's changed in terms of performance configuration since then? Have the fundamentals of shared_buffers/work_mem/max_connections changed at all? Which new settings are must-tunes? I've heard about new parallel stuff an JIT, but neither is that applicable to my use-case. Well, well! Long time no see! You'll probably be glad to learn that we have hints now. Thank you for the following page you created: https://laptrinhx.com/why-postgresql-doesn-t-have-query-hints-2912445911/ I've used it several times, with great success. It's priceless. Now, to answer your question: no, fundamentals of shared buffers, work memory and connections haven't changed. Parallelism works fine, it's reliable and easy to enable. All you need is to set max_parallel_workers_per_gather to an integer > 0 and PgSQL 15 will automatically use parallel plan if the planner decides that it's the best path. However, to warn you in advance, parallel query is not a panacea. On OLTP databases, I usually disable it on purpose. Parallel query will speed up sequential scans, but if your application is OLTP, sequential scan is a sign of trouble. Parallelism is a data warehouse only feature. And even then, you don't want it ti be run by multiple users at the same time. Namely, the number of your CPU resources is finite and having multiple users launch multiple processes is the best way to run out of the CPU power fast. Normally, you would package an output of the parallel query into a materialized view and let the users query the view. As for JIT, I've recently asked that question myself. I was told that PostgreSQL with LLVM enabled performs approximately 25% better than without it. I haven't measured it so I can't either confirm or deny the number. I can tell you that there is a noticeable throughput improvement with PL/PGSQL intensive applications. There was also an increase in CPU consumption. I wasn't doing benchmarks, I was looking for a generic settings to install via Ansible so I don't have the numbers, only the feeling. One way of quantifying the difference would be to run pgbench with and without JIT. PS: I am still an Oracle DBA, just as you wrote in the paper. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Postgresql TPS Bottleneck
On 3/31/22 07:50, wakandavis...@outlook.com wrote: Hi everyone, I am a bachelor's student and writing my thesis about the scaling and performance of an application. The application is using postgresql as a database but we can't scale any further currently as it seems postgres is hitting the limit. With the application, as well as with pgbench, we don't get more than (max) 70k TPS on postgres. But the servers' resources are not utilized completely (more below). I would try monitoring using "perf top" and "atop -d" to see what is going on on the system. Also, try using sar to figure out what's going on. Are you paging, waiting for I/O or having some other kind of bottleneck. Once you figure where is your system spending time, you can address the problem. In addition to that, analyze the log files with pgbadger to find out which queries are time consuming and try optimizing them. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: HIGH IO and Less CPU utilization
On 3/29/22 14:04, Rambabu g wrote:
Hi All,
We have an issue with high load and IO Wait's but less cpu on postgres
Database, The emp Table size is around 500GB, and the connections are
very less.
Please suggest to us do we need to change and config parameters at
system level or Postgres configuration.
The "emp" table is 500 GB? You're doing something wrong, The "emp" table
should have 14 rows and the "dept" table should have 4 rows The "bonus"
and "salgrade" tables should also be very small. The guy named Bruce
Scott could probably help you with that schema. Other than that, do you
have a SQL causing all this ruckus and a detailed explain plan ("explain
(analyze,costs,buffers)") for the SQL using most of the time? You can
analyze the log file with PgBadger to get the queries consuming the most
time.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
XA transactions much slower on 14.2 than on 13.5
My boss asked me to upgrade one of the development databases from 13.5 --> 14.2. One thing that we've noticed right away is that XA transactions (2-phase commit) are much slower on 14.2 than on 13.5. Were there any significant changes to the XA protocol in the version 14? Did anybody else encountered this problem? When I say "XA transactions are much slower", I mean that commit and/or rollback take much longer. The SQL execution takes the same and the plans are identical to the 13.5 version. The application code is the same, using IBM WebSphere 9.0.4. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Simple task with partitioning which I can't realize
On 3/1/22 10:54, David G. Johnston wrote: On Tue, Mar 1, 2022 at 8:37 AM Andrew Zakharov wrote: create table region_hierarchy( gid uuid not null default uuid_generate_v1mc(), parent_gid uuid null, region_code int2, I’ve carefully looked thru docs/faqs/google/communities and found out that I must include “gid” field into partition key because a primary key field. Yes, you are coming up against the following limitation: "Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions." https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE That limitation is independent of partitioning; i.e., the legacy inheritance option doesn't bypass it. Thus, your true "key" is composite: (region, identifier). Thus you need to add a "parent_region_code" column as well, redefine the PK as (region_code, gid), and the REFERENCES clause to link the two paired fields. You can decide whether that is sufficient or if you want some added comfort in ensuring that a gid cannot appear in multiple regions by creating a single non-partitioned table containing all gid values and add a unique constraint there. Or maybe allow for duplicates across region codes and save space by using a smaller data type (int or bigint - while renaming the column to "rid" or some such) - combined with having the non-partitioned reference table being defined as (region_code, rid, gid). David J. Hi David, Are there any concrete plans to address that particular limitation? That limitation can be re-stated as "PostgreSQL doesn't support global indexes on the partitioned tables" and I've have also run into it. My way around it was not to use partitioning but to use much larger machine with the NVME disks, which can handle the necesary I/O. Are there any plans to allow global indexes? I am aware that this is not a small change but is the only real advantage that Oracle holds over PostgreSQL. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Never Ending query in PostgreSQL
On 2/27/22 12:20, Jeff Janes wrote: Several hours is not all that long. Well, the pyramids in the Valley of the Kings last for around 4500 years. Dinosaurs have ruled the Earth for approximately 120 million years. Solar system is 5 billion years old. Cosmos is around 13 billion years old. Compared to those numbers, indeed, several hours isn't that long. Furthermore, you have to account for the time dilatation. One hour on the planet that's evolving and revolving at 900 miles an hour is not the same as one hour of standing still. To make things even more interesting, it's orbiting at 19 miles a second, so it's reckoned,The sun that is the source of all our power. So, several hours is relative. Each object has its relative time so it's not possible to conclude whether several hours is a long time or not. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Never Ending query in PostgreSQL
On 2/26/22 23:40, Kumar, Mukesh wrote: Hi Team, Can you please help in tunning the attached query as , i am trying to run this query and it runs for several hours and it did not give any output. I am not able to generate the explain analyze plan as well and it keeps on running for several hours and did not give output. I have attached the query and explain plan without analyze. Please help if nayone has any idea how to tune that query. Regards, Mukesh Kumar Hi Team Member, Your attachments are not SQL, they are plans. Judging by the size of the plans, your best course of action is to completely rewrite the queries, probably using CTE and temporary tables. May the Force be with you. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Unique constraint blues
Postgres version is 13.5, platform is Oracle Linux 8.5, x86_64. Here is the problem: mgogala=# create table test1(col1 integer,col2 varchar(10)); CREATE TABLE mgogala=# alter table test1 add constraint test1_uq unique(col1,col2); ALTER TABLE mgogala=# insert into test1 values(1,null); INSERT 0 1 mgogala=# insert into test1 values(1,null); INSERT 0 1 mgogala=# select * from test1; col1 | col2 --+-- 1 | 1 | (2 rows) So, my unique constraint doesn't work if one of the columns is null. Bruce Momjian to the rescue: https://blog.toadworld.com/2017/07/12/allowing-only-one-null Let's see what happens: mgogala=# truncate table test1; TRUNCATE TABLE mgogala=# alter table test1 drop constraint test1_uq; ALTER TABLE mgogala=# create unique index test1_uq on test1(col1,(col2 is null)) where col2 is null; CREATE INDEX mgogala=# insert into test1 values(1,null); INSERT 0 1 mgogala=# insert into test1 values(1,null); ERROR: duplicate key value violates unique constraint "test1_uq" DETAIL: Key (col1, (col2 IS NULL))=(1, t) already exists. So, this allows only a single NULL value, just what I wanted. However, there is a minor issue: this doesn't work for the general case: mgogala=# insert into test1 values(1,'test1'); INSERT 0 1 mgogala=# insert into test1 values(1,'test1'); INSERT 0 1 mgogala=# select * from test1; col1 | col2 --+--- 1 | 1 | test1 1 | test1 (3 rows) I can insert the same row twice, which defeats the purpose. So, let's make the 3d modification: mgogala=# truncate table test1; TRUNCATE TABLE mgogala=# drop index test1_uq; DROP INDEX mgogala=# create unique index test1_uq on test1(col1,coalesce(col2,'*** EMPTY ***')); Using "coalesce" enforces the constraint just the way I need: mgogala=# insert into test1 values(1,null); INSERT 0 1 mgogala=# insert into test1 values(1,null); ERROR: duplicate key value violates unique constraint "test1_uq" DETAIL: Key (col1, COALESCE(col2, '*** EMPTY ***'::character varying))=(1, *** EMPTY ***) already exists. mgogala=# insert into test1 values(1,'test1'); INSERT 0 1 mgogala=# insert into test1 values(1,'test1'); ERROR: duplicate key value violates unique constraint "test1_uq" DETAIL: Key (col1, COALESCE(col2, '*** EMPTY ***'::character varying))=(1, test1) already exists. mgogala=# Now comes the greatest mystery of them all: explain (analyze,verbose) select * from test1 where col1=1 and col2='test1'; QUERY PLAN - Bitmap Heap Scan on mgogala.test1 (cost=1.70..7.52 rows=1 width=42) (actual ti me=0.023..0.024 rows=1 loops=1) Output: col1, col2 Recheck Cond: (test1.col1 = 1) Filter: ((test1.col2)::text = 'test1'::text) Rows Removed by Filter: 1 Heap Blocks: exact=1 -> Bitmap Index Scan on test1_uq (cost=0.00..1.70 rows=6 width=0) (actual t ime=0.015..0.016 rows=2 loops=1) Index Cond: (test1.col1 = 1) Planning Time: 1.184 ms Execution Time: 0.407 ms (10 rows) How come that the index is used for search without the "coalesce" function? The unique index is a function based index and, in theory, it shouldn't be usable for searches without the function. I don't understand why is this working. I am porting application from Oracle to Postgres and Oracle behaves like this: SQLcl: Release 21.3 Production on Tue Jan 18 11:39:43 2022 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.0.0 Elapsed: 00:00:00.001 SQL> create table test1(col1 integer,col2 varchar2(10)); Table TEST1 created. Elapsed: 00:00:00.050 SQL> alter table test1 add constraint test1_uq unique(col1,col2); Table TEST1 altered. Elapsed: 00:00:00.139 SQL> insert into test1 values(1,null); 1 row inserted. Elapsed: 00:00:00.026 SQL> insert into test1 values(1,null); Error starting at line : 1 in command - insert into test1 values(1,null) Error report - ORA-1: unique constraint (SCOTT.TEST1_UQ) violated Elapsed: 00:00:00.033 Oracle is rejecting the same row twice, regardless of whether it contains NULL values or not. As in Postgres, the resulting index can be used for searches. However, Oracle index is not a function-based index because it doesn't contain the coalesce function. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: postgresql13-llvm jit-13.5-1PGDG.rhel8.x86_64
On 11/15/21 00:04, Pavel Stehule wrote: Hi There is a CentOS8-stream version which solves the problem but I cannot use that in the office. I will probably have to wait for another month before OL8 has everything that I need in its repositories. Now, the question is what kind of an impact will running without llvm-jit have? According to the links below, llvm-jit effects are quite spectacular: https://llvm.org/devmtg/2016-09/slides/Melnik-PostgreSQLLLVM.pdf https://www.pgcon.org/2017/schedule/events/1092.en.html When JIT was used on very large query with a lot of CASE expr, then JIT has a positive effect about 50%. On usual large queries, the effect of JIT was about 20%. Unfortunately, JIT is sensitive to estimation, and the JIT sometimes increases seconds to queries, although without JIT this query is executed in ms. When you use a query that can be well calculated in parallel, then positive effect of JIT is less. Regards Pavel Thanks Pavel, you answered my question. I'll wait with the upgrade. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
postgresql13-llvm jit-13.5-1PGDG.rhel8.x86_64
I was trying to upgrade my test 13.4 instance on Oracle Linux 8.4 (x86_64) to 13.5. I can't upgrade postgresql13-llvm jit because Oracle's and Red Hat repositories still don't have the required version of llvm (12.1.0.2): root@postgres mgogala]# rpm -qa|grep llvm llvm-libs-11.0.1-2.0.1.module+el8.4.0+20397+f876858a.x86_64 llvm7.0-libs-7.0.1-7.el8.x86_64 llvm-test-11.0.1-2.0.1.module+el8.4.0+20397+f876858a.x86_64 llvm-11.0.1-2.0.1.module+el8.4.0+20397+f876858a.x86_64 llvm-static-11.0.1-2.0.1.module+el8.4.0+20397+f876858a.x86_64 llvm-devel-11.0.1-2.0.1.module+el8.4.0+20397+f876858a.x86_64 [root@postgres mgogala]# I am getting the following error: postgresql13-llvm jit-13.5-1PGDG.rhel8.x86_64 requires libLLVM-12.so()(64bit), but none of the providers can be installed. There is a CentOS8-stream version which solves the problem but I cannot use that in the office. I will probably have to wait for another month before OL8 has everything that I need in its repositories. Now, the question is what kind of an impact will running without llvm-jit have? According to the links below, llvm-jit effects are quite spectacular: https://llvm.org/devmtg/2016-09/slides/Melnik-PostgreSQLLLVM.pdf https://www.pgcon.org/2017/schedule/events/1092.en.html Now, the question is whether anyone on this list can quantify the difference? What would be a better option? To wait for the repos to receive the necessary packages or to run without llvm-jit? In the office I have some rather large databases and CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY fixes in 13.5 are highly desired but not at the cost of the overall application performance. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Lock contention high
On 10/12/21 03:35, Ashkil Dighin wrote: 1.Is there a way to tune the lock contention ? Lock contention is usually an application issue. Application processes are stepping on each other's toes. I have never seen a situation where the database would be slow with managing locks. Postgres uses an in-memory queue manager which is, generally speaking, very fast. Applications usually do stupid things. I've seen GUI doing "SELECT FOR UPDATE". And then the operator decided to have lunch. I'll leave the rest to your imagination. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On 10/8/21 11:21, Bruce Momjian wrote: Ah, this is exactly what I wanted to know --- what people are using the event waits for. Can you tell if these are done all externally, or if they need internal database changes? Well, the methodology goes like this: we get the slow queries from pgBadger report and then run explain (analyze, timing, buffers) on the query. If we still cannot figure out how to improve things, we check the events and see what the query is waiting for. After that we may add an index, partition the table, change index structure or do something like that. Unrelated to this discussion, I discovered Bloom extension. Bloom indexes are phenomenally useful. I apologize for the digression. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
adrci utility has parameters for the automatic cleanup of the traceand core dump files. Sometimes they did fill the file system. As for the "tracing vs. sampling" debate, Oracle has both. V$ACTIVE_SESSION_HISTORY is a sampling view. Sampling views are more practical, especially when there are pooled connections. Personally, I would prefer sampling. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: [EXT] Re: Troubleshooting a long running delete statement
On 10/6/21 16:32, Dirschel, Steve wrote: postgres=# explain (analyze) delete from t; Delete on t I would try explain (analyze, timing, buffers). That would also give you the timing of each step so you can figure which one takes the longes. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: [EXT] Re: Troubleshooting a long running delete statement
On 10/6/21 16:26, Dirschel, Steve wrote: Thanks for the reply and I hope I’m replying to this e-mail correctly at the bottom of the chain. Hey, it's not me, it's rules and regulations. And that's incredibly important on this group, or so I was lead to believe :) We are running on AWS aurora postgres. I assume strace -e isn’t an option given we don’t have access to the server or are you aware of a method I could still do that without server access? Regards Steve No, access to the OS is not an option with RDS. However, RDS comes with support. You will have to contact support. They may use strace for you. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Troubleshooting a long running delete statement
On 10/6/21 14:00, Dirschel, Steve wrote: Question: How would one troubleshoot this issue in Postgres as to why the delete was running so long? My background is Oracle and there are various statistics I may look at: * One could estimate the number of logical reads the delete should do based on expected number of rows to delete, expected logical reads against the table per row, expected logical reads against each index per row. * One could look in V$SQL and see how many logical reads the query was actually doing. * One could look at V$SESS_IO and see how many logical reads the session was doing. In this case you would see the query was doing way more logical reads that expected and then try and think of scenarios that would cause that. Here is what I could see in Postgres: * When I did an explain on the delete I could see it was full scanning the table. I did a full scan of the table interactively in less than 1 second so the long runtime was not due to the full tablescan. * I could not find the query in pg_stat_statements to see how many shared block reads/hits the query was doing to see if the numbers were extremely high. Based on documentation queries do not show up in pg_stat_statements until after they complete. * pg_stat_activity showed wait_event_type and wait_event were null for the session every time I looked. So the session was continually using CPU. I started looking at table definitions (indexes, FK's, etc.) and comparing to Oracle and noticed some indexes missing. I then could see the table being deleted from was a child table with a FK pointing to a parent table. Finally I was able to see that the parent table was missing an index on the FK column so for every row being deleted from the child it was full scanning the parent. All makes sense after the fact but I'm looking for a more methodical way to come to that conclusion by looking at database statistics. Are there other statistics in Postgres I may have looked at to methodically come to the conclusion that the problem was the missing index on the parent FK column? Thanks This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html Hi Steve, First, check whether you have any triggers on the table. The best way of doing it is to use information_schema.triggers. I have seen triggers introduce some "mysterious" functionality in Oracle as well. Second, check constraints. Is the table you're deleting from the parent table of a foreign key constraint(s)? If the constraints are defined with "ON DELETE CASCADE", you maybe deleting more than you think. If it is not defined with "ON DELETE CASCADE" or "ON DELETE SET NULL", you would get an error. If that passes the muster, then check the processes doing the most of IO using "iotop" or "atop". I like the latter. You can then check what the busy processes are doing using strace -e trace=file and, for good measure, 'perf top". Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On 10/5/21 20:02, Tim wrote: Jeff Holt is probably pretty embarrassed there's some blowhard making a scene using his name in a casual mailing list thread. Wow! What a contribution to the discussion! Calling me a blowhard, all while top-posting at the same time. Your post will be remembered for generations to come. Or not. Laurenz will probably tell you that we don't top-post in Postgres community. He's good with rules, regulations and the way things are done in Postgres community. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
Comments in-line On 10/5/21 16:24, Peter Geoghegan wrote: On Fri, Oct 1, 2021 at 1:06 PM Jeff Holt wrote: Now looking closely at postgreSQL, I see an opportunity to more quickly implement Oracle's current feature list. I've come to this point because I see many roadblocks for users who want to see a detailed "receipt" for their response time. I have heard of method R. Offhand it seems roughly comparable to something like the Top-down Microarchitecture Analysis Method that low level systems programmers sometimes use, along with Intel's pmu-tools -- at least at a very high level. The point seems to be to provide a workflow that can plausibly zero in on low-level bottlenecks, by providing high level context. Many tricky real world problems are in some sense a high level problem that is disguised as a low level problem. And so all of the pieces need to be present on the board, so to speak. Does that sound accurate? Yes, that is pretty accurate. It is essentially the same method described in the "High Performance Computing" books. The trick is to figure what the process is waiting for and then reduce the wait times. All computers wait at the same speed. One obvious issue with much of the Postgres instrumentation is that it makes it hard to see how things change over time. I think that that is often *way* more informative than static snapshots. I can see why you'd emphasize the need for PostgreSQL to more or less own the end to end experience for something like this. It doesn't necessarily follow that the underlying implementation cannot make use of infrastructure like eBPF, though. Fast user space probes provably have no overhead, and can be compiled-in by distros that can support it. There hasn't been a consistent effort to make that stuff available, but I doubt that that tells us much about what is possible. The probes that we have today are somewhat of a grab-bag, that aren't particularly useful -- so it's a chicken-and-egg thing. Not exactly. There already is a very good extension for Postgres called pg_wait_sampling: https://github.com/postgrespro/pg_wait_sampling What is missing here is mostly the documentation. This extension should become a part of Postgres proper and the events should be documented as they are (mostly) documented for Oracle. Oracle uses trace files instead. However, with Postgres equivalence of files and tables, this is not a big difference. It would probably be helpful if you could describe what you feel is missing in more general terms -- while perhaps giving specific practical examples of specific scenarios that give us some sense of what the strengths of the model are. ISTM that it's not so much a lack of automation in PostgreSQL. It's more like a lack of a generalized model, which includes automation, but also some high level top-down theory. I am not Jeff and my opinion is not as valuable and doesn't carry the same weight, by far. However, I do believe that we may not see Jeff Holt again on this group so I am providing my opinion instead. At least I would, in Jeff's place, be reluctant to return to this group. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
Comments in-line: On 10/5/21 04:26, Laurenz Albe wrote: have never heard of Jeff Holt, but then there are a lot of wonderful and smart people I have never heard of. I tend to be respectful in my conversation, regardless if I know the other person or not. That much is apparent. However, that's no excuse. Haughty lectures about "Oracle has it" not being good enough could hardly be more out of place here. I have no idea how you arrive at the conclusion that I was delivering a haughty lecture. Somebody asked if PostgreSQL would consider applying patches he is ready to write, somebody who seems not to be familiar with the way PostgreSQL development works, so I tried to give helpful pointers. Your tone of voice did. Plus, you took it on yourself to explain "how things are done in the Postgres community". I always use hints and Josh Berkus as an example "how things are done in the Postgres community" and why is the Postgres progress so slow. You have just provided me another perfect example of the "community spirit". To put it as politely as is possible in this case, shut your pie hole. I think you have just disqualified yourself from taking part in this conversation. I recommend that you don't embarrass Jeff Holt by trying to champion him. If you are under impression that I want to take part in a conversation, then you're sorely mistaken. And I have to adjust my style of writing to make things even more clear. As for Jeff, I don't need to 'champion him'. He did that all by himself. In his place, I would simply ignore both this topic and you, Mr. Postgres Community. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On 10/4/21 23:08, Peter Geoghegan wrote: n all sincerity: Chill out. I don't think that this is worth getting into an argument over. I think that there is a good chance that you'd have had a much better experience if the conversation had been in person. Text has a way of losing a lot of important nuance. I have personally met and enjoyed talking to quite a few people that personally worked on Oracle, in various capacities -- the world of database internals experts is not huge. I find Tanel Poder very interesting, too -- never met the man, but we follow each other on Twitter. Oracle is a system that has some interesting properties in general (not just as a counterpoint to PostgreSQL), and I definitely respect it. Same with SQL Server. There are lots of smart and accomplished people in the world, including Jeff. I think that it's worth understanding these differences in perspective. There is likely to be merit in all of the specific points made by both Laurenze and Jeff. They may not be irreconcilable, or anything like it. What angered me was the presumptuous tone of voice directed to an Oracle legend. I have probably talked to many more Oracle people than you, including Tanel, whom I have met personally. I am not on Twitter, unfortunately I am older than 20. Before you ask, I am not on Instagram, Facebook or Tiktok. I am not on OnlyFans either. I have never understood the need to share one's every thought in real time. Being rather private person has probably stymied my career of an internet influencer. I'll never rival Kim Kardashian. As for Jeff Holt, I believe that a person of his stature needs to be taken seriously and not lectured "how are things done in Postgres community". I am rather confused by the thinly veiled hostility toward Oracle. In my opinion, Postgres community should be rather welcoming to Oracle people like Frits Hoogland, Frank Pachot or Jeff Holt. After all, we are using Postgres and telling us "you can't have what you used to get from Oracle" is not either encouraging or smart. If you want consulting jobs in big banks and for a decent money, you might just take Oracle people seriously. Have you ever wondered why Oracle has so many customers despite the fact that it's so freakishly expensive? -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
On 10/4/21 22:25, michael...@sqlexec.com wrote: Mladen, Shame on u lecturing a top notch guy in the PostgreSQL world, Laurenz Albe. I think Laurenz knows “a little bit” about Oracle having written the popular extension, fdw_oracle, among his many other contributions to the PG world. So ironic that Laurenz was just named “PostgReSQL person of the week”, and then has to be subjected to this “tirade” of yours! Follow the PG protocol in submitting your change requests to core PG and stop your Bitchin! Michael Vitale First, a matter of format: please don't top-post. Replies go under the original posts. That's an unwritten rule, but a very time honored one. Second, I know very well who Laurenz Albe is. We have met on the oracle-l few decades ago. Third, I think that my reproach to Laurenz's tone is very justified. You don't say "the argument that Python has it is not good enough" to Dennis Ritchie. Hopefully, you get my analogy, but one cannot ever be sure. Last, I didn't request any new features from the Postgres community. That's a mistake that I'll never commit again. Last time I tried, this has happened: https://www.toolbox.com/tech/data-management/blogs/why-postgresql-doesnt-have-query-hints-020411/ I still keep it in my bookmark folder, under "Humor". I used that article several times on the oracle-l as an illustration some properties of Postgres community. That article was a gift and I am sincerely grateful. Of course, PostgreSQL now has query hints. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle
tions all the time. Arrogantly talking at someone and giving unsolicited lectures in what is appropriate and what is not is another thing altogether. Finally, about the tone of this message: you really pissed me off. I had to restrain myself from using even stronger language, that was surprisingly hard to do. I wouldn't be surprised to see you giving haughty lectures about programming to Brian Kernighan or Dennis Ritchie. And yes, those two have allegedly also written a book. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
I know that 14 is a beta version but the performance is significantly worse than v13 (I assume it's 13.4). Head revision is better than v14 but still worse than v13. Can you expand a bit on the difference? Where does the difference come from? Are there any differences in the execution plan? I am looking at the first query, taking slightly more than 12s. Regards On 8/21/21 11:04 AM, Tom Lane wrote: HEAD: Time: 12234.297 ms (00:12.234) Time: 3029.643 ms (00:03.030) v14: Time: 12519.038 ms (00:12.519) Time: 3211.315 ms (00:03.211) v13: Time: 12132.026 ms (00:12.132) Time: 3114.582 ms (00:03.115) -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Postgres using the wrong index index
You know that you can use pg_hint_plan extension? That way you don't have to disable indexes or set session parameters. Regards On 8/11/21 3:56 PM, Matt Dupree wrote: Thanks for your response, Justin! Here's <https://explain.depesz.com/s/kCvN> the plan if we disable the custom_2 index. It uses the index I expect and it's much faster. Here's <https://explain.depesz.com/s/KBgG> a plan if we disable index scans. It uses both indexes and is much faster. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
