Re: Hash Right join and seq scan

Is the query fast with some bind parameters but slow with others? If so, it'd be better to show an explain with 'fast' and 'slow' bind params, rather than the same bind params with enable_*=off. Or is the change because autoanalyze runs on some table and changes the statistics enough to change th

Re: speeding up grafana sensor-data query on raspberry pi 3

On Sun, Apr 16, 2023 at 07:00:33PM +0200, Clemens Eisserer wrote: > Hi, > > I am currently trying to migrate an influxdb 1.7 smarthome database to > postgresql (13.9) running on my raspberry 3. > It works quite well, but for the queries executed by grafana I get a > bit highter execution times tha

Re: multicolumn partitioning help

On Sun, Mar 12, 2023 at 01:59:32PM -0400, James Robertson wrote: > Hey folks, > I am having issues with multicolumn partitioning. For reference I am using > the following link as my guide: > https://www.postgresql.org/docs/devel/sql-createtable.html > > Reading the documentation: "When creating

Re: BRIN index worse than sequential scan for large search set

On Fri, Feb 24, 2023 at 06:51:00PM +0100, Mickael van der Beek wrote: > Hello Justin, > > Thanks for the quick response! > > > The table may be dense, but the tuples aren't. You're asking to return > > 1/1000th of the tuples, across the entire table. Suppose there are ~100 > > tuples per page,

Re: BRIN index worse than sequential scan for large search set

On Fri, Feb 24, 2023 at 05:40:55PM +0100, Mickael van der Beek wrote: > Hello everyone, > > I'm playing around with BRIN indexes so as to get a feel for the feature. > During my tests, I was unable to make BRIN indexes perform better than a > sequential scan for queries searching for large value s

Re: Getting an index scan to be a parallel index scan

On Wed, Feb 01, 2023 at 11:22:47AM -0800, Alex Kaiser wrote: > I've never messed around with extended statistics, but I'm not sure how > they would help here. From what I've read they seem to help when your query > is restricting over multiple columns. Since this query is only on one > column I'm n

Re: Database Stalls

On Mon, Jan 30, 2023 at 05:47:49PM +, Mok wrote: > Hi, > > We've started to observe instances of one of our databases stalling for a > few seconds. > > We see a spike in wal write locks then nothing for a few seconds. After > which we have spike latency as processes waiting to get to the db c

Re: Advice on best way to store a large amount of data in postgresql

On Sun, Jan 08, 2023 at 07:02:01AM -0500, spiral wrote: > This table is used essentially as a key-value store; rows are accessed > only with `mid` primary key. Additionally, inserted rows may only be > deleted, but never updated. > > We only run the following queries: > - INSERT INTO messages VALU

Re: How to analyze of short but heavy intermittent slowdown on BIND on production database (or BIND vs log_lock_waits)

On Sat, Dec 31, 2022 at 02:26:08PM +0200, Maxim Boguk wrote: > Hi, > > When performing post-mortem analysis of some short latency spikes on a > heavily loaded database, I found that the reason for (less than 10 second > latency spike) wasn't on the EXECUTE stage but on the BIND stage. > At the sam

Re: When you really want to force a certain join type?

On Wed, Dec 28, 2022 at 10:39:14AM -0500, Gunther Schadow wrote: > I have a complex query which essentially runs a finite state automaton > through a with recursive union, adding the next state based on the > previous.  This is run at 100,000 or a million start states at the same > time, picking a

Re: Fwd: temp_file_limit?

On Mon, Dec 19, 2022 at 09:10:27PM +0100, Frits Jalvingh wrote: > @justin > > I tried the create statistics variant and that definitely improves the > estimate, and with that one of the "bad" cases (the one with the 82 minute > plan) now creates a good plan using only a few seconds. > That is a wo

Re: Fwd: temp_file_limit?

On Sun, Dec 18, 2022 at 06:29:41PM +0100, Frits Jalvingh wrote: > By itself I'm used to bad query performance in Postgresql; our application > only does bulk queries and Postgres quite often makes terrible plans for > those, but with set enable_nestloop=false set always most of them at least > exec

Re: Fwd: temp_file_limit?

> to). In rare cases where more generations are needed it could unlink > the older ones -- that hasn't been implemented yet. If you set > log_temp_files = 0 to log temporary file names, it should be clear if > it's going through multiple rounds of repartitioning, from the names

Re: Fwd: temp_file_limit?

On Mon, Dec 19, 2022 at 05:57:42PM +0100, Frits Jalvingh wrote: > @justin: > > Ran the query again. Top shows the following processes: >PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ Thanks > root@chatelet:/d2/var/lib/postgresql/15/main/base# du --max=2 -mx > ./pgsql_tmp

Re: Fwd: temp_file_limit?

On Sun, Dec 18, 2022 at 06:29:41PM +0100, Frits Jalvingh wrote: > Just to be clear: my real question is: why is temp_file_limit not > working at the specified size? Because this is my real problem: when a > query is dying like this it will also kill other queries because these > are also running ou

Re: temp_file_limit?

On Sun, Dec 18, 2022 at 12:48:03PM +0100, Frits Jalvingh wrote: > Hi list, > > I have a misbehaving query which uses all available disk space and then > terminates with a "cannot write block" error. To prevent other processes > from running into trouble I've set the following: > > temp_file_limit

Re: Postgres12 looking for possible HashAggregate issue workarounds?

On Fri, Dec 16, 2022 at 03:24:17PM +, João Paulo Luís wrote: > Hi! Sorry to post to this mailing list, but I could not find many tips > working around HashAggregate issues. > > In a research project involving text repetition analysis (on top of public > documents) > I have a VirtualMachine (

Re: Odd Choice of seq scan

On Fri, Dec 02, 2022 at 11:52:19AM +1100, Paul McGarry wrote: > Hi there, > > I'm wondering if anyone has any insight into what might make the database > choose a sequential scan for a query (table defs and plan below) like : > Plan - seq scan of table: > = > > explain select orders.orderid F

Re: Catching up with performance & PostgreSQL 15

On Mon, Nov 28, 2022 at 06:59:41PM -0800, 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

Re: Query is sometimes fast and sometimes slow: what could be the reason?

On Wed, Sep 14, 2022 at 05:02:07PM +0200, tias...@gmx.de wrote: > What could be the reason of a query, which is sometimes fast > and sometimes slow (factor >10x)? > (running on a large table). >   >   Lots of possible issues. Is it using a different query plan ? Collect a good plan and a bad one

Re: Postgresql JDBC process consumes more memory than psql client

On Tue, Sep 06, 2022 at 04:15:03AM +, James Pang (chaolpan) wrote: > We make 2 comparisions between partitioned(256 HASH) and no-partitioned(same > data volume,same table attributes) , do same "UPDATE,DELETE " . > 1. with partitioned tables , the "RES" from top command memory increased > qu

Re: Postgresql JDBC process consumes more memory than psql client

On Mon, Sep 05, 2022 at 12:52:14PM +, James Pang (chaolpan) wrote: > Any idea how to print SQL plan from JDBC driver ? You could use "explain execute" on the client, or autoexplain on the server-side. -- Justin

Re: Postgresql JDBC process consumes more memory than psql client

On Mon, Sep 05, 2022 at 12:40:46PM +, James Pang (chaolpan) wrote: > We run same update or delete SQL statement " DELETE FROM ... WHERE ... " > the table is a hash partition table (256 hash partitions). When run the sql > from Postgresql JDBC driver, it soon increased to 150MB memory (RES

Re: to_jsonb performance on array aggregated correlated subqueries

On Fri, Aug 12, 2022 at 07:02:36PM +, Nico Heller wrote: > I knew I forgot something: We are currently on 13.6. When was this issue > fixed? There's a WIP/proposed fix, but the fix is not released. I asked about your version because jit was disabled by default in v11. But it's enabled by defau

Re: to_jsonb performance on array aggregated correlated subqueries

What version of postgres ? I wonder if you're hitting the known memory leak involving jit. Try with jit=off or jit_inline_above_cost=-1. -- Justin

Re: Postgresql 14 partitioning advice

On Mon, Aug 08, 2022 at 03:45:11PM -0700, Slava Mudry wrote: > Postgres 14 improved partitioning quite a bit. I used it in Postgres 9 and > there was a lot of locking on partition hierarchy when you add/drop > partition tables. Note that postgres 9 didn't have native/declarative partitioning, and

Re: pg_wal filling up while running huge updates

On Fri, Aug 05, 2022 at 06:00:02PM +0530, aditya desai wrote: > Hi, > We are doing an oracle to postgres migration(5 TB+ data). We are encoding > and decoding BLOB data after migration and for that we are running updates > on tables having BLOB/CLOB data. When we execute this pg_wal is filling up.

Re: Postgresql 14 partitioning advice

On Wed, Jul 27, 2022 at 08:55:14AM -0400, Rick Otten wrote: > I'm spinning up a new Postgresql 14 database where I'll have to store a > couple years worth of time series data at the rate of single-digit millions > of rows per day. Since this has to run in AWS Aurora, I can't use > TimescaleDB. >

Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries

On Thu, Jul 21, 2022 at 03:59:30PM -0400, bruno da silva wrote: > OS/version: CentOS release 6.9 (Final) How are these set ? tail /sys/kernel/mm/ksm/run /sys/kernel/mm/transparent_hugepage/{defrag,enabled,khugepaged/defrag} /proc/sys/vm/zone_reclaim_mode I suspect you may be suffering from i

Re: PostgresSQL 9.5.21 very slow to connect and perform basic queries

On Thu, Jul 21, 2022 at 02:37:35PM -0400, bruno da silva wrote: > I'm investigating an issue on a PostgresSql 9.5.21 installation that > becomes unusable in an intermittent way. Simple queries like "select > now();" could take 20s. commits take 2s. and all gets fixed after an engine > restart. > >

Re: Occasional performance issue after changing table partitions

On Wed, Jul 13, 2022 at 03:13:46AM +1200, Nathan Ward wrote: > I have been stepping through the various statements which are different > between the two functions, and note that when I do math on a timestamp in a > SELECT statement (i.e. _event_timestamp - INTERVAL ‘1 hour’), > the planner takes

Re: Oracle_FDW table performance issue

On Mon, Jul 11, 2022 at 05:38:34PM +0530, aditya desai wrote: > Hi, > I have one Oracle fdw table which is giving performance issue when joined > local temp table gives performance issue. > > select * from oracle_fdw_table where transaction_id in ( select > transaction_id from temp_table) 54

Re: Occasional performance issue after changing table partitions

On Mon, Jul 11, 2022 at 03:21:38PM +1200, Nathan Ward wrote: > > Note that postgres doesn't automatically analyze parent tables, so you > > should > > maybe do that whenever the data changes enough for it to matter. > > Hmm. This raises some stuff I’m not familiar with - does analysing a parent

Re: Occasional performance issue after changing table partitions

On Sun, Jul 10, 2022 at 04:55:34PM +1200, Nathan Ward wrote: > I am running Postgres 13 on CentOS 7, installed from the yum.postgresql.org > repo. It doesn't sound relevant, but what kind of storage systems is postgres using ? Filesystem, raid, device. Is the high CP

Re: partition pruning only works for select but update

On Fri, Jul 01, 2022 at 08:30:40AM +, James Pang (chaolpan) wrote: > We have other application depend on V13, possible to backport code changes to > V13 as > https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=86dc90056dfdbd9d1b891718d2e5614e3e432f35 Do you mean that the other appl

Re: Fluctuating performance of updates on small table with trigger

On Wed, Jun 29, 2022 at 09:31:58PM +0200, Mikkel Lauritsen wrote: > In short I'm running PostgreSQL 14.1 on Linux on a small test machine with should try to upgrade to 14.4, for $reasons > Is there any feasible way to find out what it is that causes Postgres to > start doing slow updates? My gues

Re: Strange behavior of limit clause in complex query

On Wed, Jun 08, 2022 at 09:44:08AM +0100, Paulo Silva wrote: > But if I add an ORDER BY and a LIMIT something goes very wrong (Q2): A somewhat common problem. A common workaround is to change "ORDER BY a" to something like "ORDER BY a+0" (if your framework will allow it). > An EXPLAIN (ANALYZE,

Re: Query is taking too long i intermittent

On Mon, Jun 06, 2022 at 03:28:43PM +0530, Mayank Kandari wrote: > Thanks for including the link*. (*FYI, I find it to be kind of unfriendly to ask the same question in multiple forums, simultaneously - it's like cross-posting. The goal seems to

Re: postgres backend process hang on " D " state

On Mon, May 30, 2022 at 01:19:56AM +, James Pang (chaolpan) wrote: > 1. extensions > shared_preload_libraries = > 'orafce,pgaudit,pg_cron,pg_stat_statements,set_user' > 2. psql can not login now ,it hang there too, so can not check anything from > pg_stats_* views > 3. one main app u

Re: postgres backend process hang on " D " state

On Sun, May 29, 2022 at 01:20:12PM +, James Pang (chaolpan) wrote: >We have a performance test on Postgresql 13.4 on RHEL8.4 , just after > connection storm in ( 952 new connections coming in 1 minute), a lot of > backends start on " D " state, and when more sessions got disconnected, t

Re: How to monitor Postgres real memory usage

On Sat, May 28, 2022 at 01:40:14AM +0800, 徐志宇徐 wrote: > vm.swappiness=0 I think this is related to the problem. swappiness=0 means to *never* use swap, even if that means that processes are killed. If you really wanted that, you should remove the swap space. Swap is extremely slow and worth avo

Re: How to monitor Postgres real memory usage

On Fri, May 27, 2022 at 01:39:15AM +0800, 徐志宇徐 wrote: > Hi Justin > > Thanks for you explaination. > > > > What postgres version ? > > > How was it installed ? From souce? From a package ? > I am using Postgres 11.1 .It's installed by package. This is quite old, and missing ~4 years of

Re: How to monitor Postgres real memory usage

utovacuum_analyze_scale_factor = '0.1' This means you're going to use up to 20 processes simultaneously running vacuum (each of which may use 64MB memory). What kind of storage does the server have? Can it support 20 background processes reading from disk, in addition to

Re: How to monitor Postgres real memory usage

On Wed, May 25, 2022 at 12:25:28AM +0800, 徐志宇徐 wrote: > Hi All > > I am a Database DBA. I focus on PostgreSQL and DB2. > Recently. I experience some memory issue. The postgres unable allocate > memory. I don't know how to monitor Postgres memory usage. Postgres is just an OS Process, so shoul

Re: DB connection issue suggestions

If the problem occurs gradually (like leaking 20 connections per hour during ETL), you can check pg_stat_activity every hour or so to try to observe the problem before all the connection slots are used up, to collect diagnostic information. Alternately, leave a connection opened to the DB and wait

Re: DB connection issue suggestions

On Wed, May 11, 2022 at 09:52:10AM +0800, Sudhir Guna wrote: > Hi Justin, > > Thank you for reviewing. > > I have tried to run the below query and could see only less than 5 > connections active when I get this error. The total rows I see is only 10 > including idle and active sessions for this o

Re: DB connection issue suggestions

On Wed, May 11, 2022 at 12:59:01AM +0800, Sudhir Guna wrote: > Dear All, > > We have recently upgraded Postgresql 9.4 standalone server to Postgresql > 11.2 with High Availability (2 servers : Master and Standby). > > While trying to test using ETL applications and reports, we observe that > the

Re: Query Planner not taking advantage of HASH PARTITION

On Wed, Apr 20, 2022 at 07:11:37PM -0700, Benjamin Tingle wrote: > @ the first point about write locks > I think I had/have a misconception about how inserts work in postgres. It's > my understanding that postgres will never draft a parallel insert plan for > any query (except maybe CREATE TABLE AS

Re: significant jump in sql statement timing for on server vs a remote connection

On Tue, Apr 19, 2022 at 03:00:09PM -0600, Sbob wrote: > We are debugging a sql performance issue. We have a sql file with 50,000 > simple select statements in it. If I run the file locally it completes in > less than 15sec.  If I force the local connection to be a tcp/ip connection > via psql -h an

Re: Query Tunning related to function

On Thu, Apr 14, 2022 at 06:03:33AM +, Kumar, Mukesh wrote: > We are running the below query in PostgreSQL and its taking approx. 8 to 9 > sec to run the query. > > Query - 1 ... > > The explain plan and other details are placed at below link for more > information. We have checked the index

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Wed, Apr 13, 2022 at 03:36:19PM +, l...@laurent-hasson.com wrote: > After a lot of back and forth, someone in IT informed us that the database VM > is under a backup schedule using Veeam. Apparently, during the backup window, > Veeam creates a snapshot and that takes the VM offline for a c

Re: HIGH IO and Less CPU utilization

On Wed, Mar 30, 2022 at 10:17:38AM +0530, Rambabu g wrote: > Hi Justin, > > Only one query is causing the issue, sharing the def of indexes. Please > have a look. > > > > There are three indexes defined on the table, each one is around 20 to > > > 25GB > > tp| character varying(2000) | y

Re: HIGH IO and Less CPU utilization

On Wed, Mar 30, 2022 at 12:52:05AM +0530, Rambabu g wrote: > > What indexes are defined on this table ? > > How large are they ? > > There are three indexes defined on the table, each one is around 20 to 25GB > and the indexes is create on Did you mean to say something else after "on" ? Show the

Re: HIGH IO and Less CPU utilization

Hi, Thanks for providing all this info. On Tue, Mar 29, 2022 at 11:34:18PM +0530, 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. What indexes are defined o

Re: Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

On Tue, Mar 22, 2022 at 12:57:10PM +, Prajna Shetty wrote: > 1. We have performed Vacuum/Analyze/Reindex post Upgrade. > 2. Tweaked work_mem so it does not spill to Disk. We can Disk Usage But > it is still using Hash Aggregate and came down from 5 minutes to 20 seconds. > (Expected

Re: High process memory consumption when running sort

On Wed, Mar 23, 2022 at 02:42:06PM +, Shai Shapira wrote: > Hi, > > When running our application, we noticed that some processes are taking a lot > of memory ( 10, 15, 20GB or so, of RSS ). > It is also reproduced when running in psql. Note that RSS can include shared_buffers read by that ba

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

On Wed, Mar 23, 2022 at 09:44:09AM +, Lars Aksel Opsahl wrote: > Why is temp tables with no indexes much faster system tables with indexes ? I think the "temp table" way is accidentally faster due to having no statistics, not because it has no indexes. If you run ANALYZE, you may hit the same

Re: Optimal configuration for server

On Mon, Mar 07, 2022 at 08:51:24AM -0300, Luiz Felipph wrote: > My current problem: > > under heavyload, i'm getting "connection closed" on the application > level(java-jdbc, jboss ds) Could you check whether the server is crashing ? If you run "ps -fu postgres", you can compare the start time (

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Thu, Mar 03, 2022 at 01:33:08PM -0300, Ranier Vilela wrote: > Sorry, but this is much more on the client side. The client is reporting the problem, as is the server. > Following the logs, it is understood that the client is dropping the > connection. The logs show that the client's connection

Re: OOM killer while pg_restore

On Thu, Mar 03, 2022 at 09:59:03AM +0100, Marc Rechté wrote: > Hello, > > We have a pg_restore which fails due to RAM over-consumption of the > corresponding PG backend, which ends-up with OOM killer. > > The table has one PK, one index, and 3 FK constraints, active while restoring. Send the sch

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Tue, Mar 01, 2022 at 04:28:31PM +, l...@laurent-hasson.com wrote: > Now, there is an additional component I think... Storage is on an array and I > am not getting a clear answer as to where it is 😊 Is it possible that > something is happening at the storage layer? Could that be reported as

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Mon, Feb 28, 2022 at 09:43:09PM +, l...@laurent-hasson.com wrote: >On Wed, Feb 23, 2022 at 07:04:15PM -0600, Justin Pryzby wrote: >> > And the aforementioned network trace. You could set a capture filter > on TCP >> > SYN|RST so it's not absur

Re: slow query to improve performace

Please provide some more information, like your postgres version and settings. Some relevant things are included here. https://wiki.postgresql.org/wiki/Slow_Query_Questions -- Justin

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Wed, Feb 23, 2022 at 07:04:15PM -0600, Justin Pryzby wrote: > And the aforementioned network trace. You could set a capture filter on TCP > SYN|RST so it's not absurdly large. From my notes, it might look like this: > (tcp[tcpflags]&(tcp-rst|tcp-syn|tcp-fin)!=0) I'd

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Thu, Feb 24, 2022 at 08:50:45AM -0300, Ranier Vilela wrote: > I can't understand why you are still using 13.4? > [1] There is a long discussion about the issue with 13.4, the project was > made to fix a DLL bottleneck. > > Why you not use 13.6? That other problem (and its fix) were in the wind

Re: An I/O error occurred while sending to the backend (PG 13.4)

You originally mailed about an error on the client, and now you found corresponding server logs, which suggests a veritable network issue. Are the postgres clients and server on the same subnet ? If not, what are the intermediate routers ? Is there any NAT happening ? Do those devices have any

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Thu, Feb 24, 2022 at 12:47:42AM +, l...@laurent-hasson.com wrote: > On Sat, Dec 04, 2021 at 05:32:10PM +, l...@laurent-hasson.com wrote: > > I have a data warehouse with a fairly complex ETL process that has > > been running for years now across PG 9.6, 11.2 and now 13.4 fo

Re: Slow Running Queries in Azure PostgreSQL

On Tue, Feb 22, 2022 at 02:11:58PM +, Kumar, Mukesh wrote: > -> Hash Join (cost=6484.69..43117.63 rows=1 width=198) (actual > time=155.508..820.705 rows=52841 loops=1)" >Hash Cond: (((lms_doc_property_rights_assoc.doc_sid_c)::text = > (lms_doc_propright_status_assoc.doc_sid_c)::te

Re: slow "select count(*) from information_schema.tables;" in some cases

On Mon, Feb 07, 2022 at 04:56:35PM +, Lars Aksel Opsahl wrote: > Sometimes simple sql's like this takes a very long time "select count(*) > from information_schema.tables;" > > Other sql's not including system tables may work ok but login also takes a > very long time. > > The CPU load on

Re: Query is slower with a large proportion of NULLs in several columns

On Tue, Dec 21, 2021 at 12:33:06AM -0500, Tom Lane wrote: > So now we have a real mystery about what is happening on Lars' > system. Those numbers can't be right. I realized Lars said it was x86_64/Linux, but I'm hoping to hear back with more details: What OS version? Is it a VM of some type ? H

Re: Query is slower with a large proportion of NULLs in several columns

On Mon, Dec 20, 2021 at 08:11:42PM -0800, Lars Bergeson wrote: > ok, here are results after I did: > set max_parallel_workers_per_gather = 0; > > HashAggregate (cost=1676432.13..1676432.16 rows=3 width=15) (actual > time=19908.343..19908.345 rows=5 loops=1) > I/O Timings: read=532369.898 > Exe

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Sat, Dec 04, 2021 at 07:18:06PM +, l...@laurent-hasson.com wrote: > It's a remote server, but all on a local network. Network performance is I am > sure not the issue. Also, the system is on Windows Server. What are you > expecting to see out of a tcpdump? I'll try to get PG logs on the fa

Re: An I/O error occurred while sending to the backend (PG 13.4)

On Sat, Dec 04, 2021 at 05:32:10PM +, l...@laurent-hasson.com wrote: > I have a data warehouse with a fairly complex ETL process that has been > running for years now across PG 9.6, 11.2 and now 13.4 for the past couple of > months. I have been getting the error "An I/O error occurred while s

Re: pg_dump backup verification

On Thu, Nov 25, 2021 at 02:41:34PM +0530, Daulat wrote: > Please suggest how I can ensure pg_dump backup has completed successfully ? > I don't think there is any view like Oracle which helps with > dba_datampump_jobs etc. 1) Check its exit status. If it's nonzero, then surely there's a problem (

Re: Need help identifying a periodic performance issue.

On Wed, Nov 24, 2021 at 10:44:12PM +, Robert Creager wrote: > I forgot, I had reloaded postgres, but had not re-started our app, so the > connections wouldn’t have that plan setting on them. Re-doing now. Are you sure? GUC changes should be applied for existing sessions, right ? Would you s

Re: performance of analytical query

On Fri, Nov 12, 2021 at 09:12:38PM +0100, Jiří Fejfar wrote: > * I know that PG is focused on OLTP rather then analytics, but we are happy > with it at all and do not wish to use another engine for analytical > queries... isn't somewhere some "PG analytical best practice" available? It's a good qu

Re: Need help identifying a periodic performance issue.

On Thu, Nov 18, 2021 at 04:39:42PM +1300, Thomas Munro wrote: > On Thu, Nov 18, 2021 at 1:18 PM Robert Creager > wrote: > > So, how do I go about capturing more information for the big brains (you > > guys) to help figure this out? I have all our resources at mine (and hence > > your) disposal

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 09:54:14PM +, Robert Creager wrote: > We are able to move up to Postgres 13.5, in our ports tree, if that would > help. We used pg_upgrade to get from 9.6 to 13.3, so that should work fine > going instead to 13.5. We’re almost branching/releasing our code, so it’s >

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 05:51:05PM +, Robert Creager wrote: > postgres`HeapTupleSatisfiesVisibility+0x42 > postgres`heapgetpage+0x237 > postgres`heapgettup_pagemode+0x5ad > postgres`heap_getnextslot+0x52 > postgres`SeqNext+0x

Re: Need help identifying a periodic performance issue.

On Tue, Nov 16, 2021 at 04:43:25AM +, Robert Creager wrote: > We’re executing the following copy to fill a table with approximately 5k > records, then repeating for a total of 250k records. Normally, this copy > executes < 1 second, with the entire set taking a couple of minutes. The > prob

Re: performance of analytical query

On Fri, Nov 12, 2021 at 10:55:53AM -0700, Michael Lewis wrote: > On Thu, Nov 11, 2021 at 7:42 PM Justin Pryzby wrote: > > > BTW, we disable nested loops for the our analytic report queries. I have > > never > > been able to avoid pathological plans any other way. >

Re: performance of analytical query

On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote: > Hi folks, > > we have found that (probably after VACUUM ANALYZE) one analytical query > starts to be slow on our production DB. Moreover, more or less the same > plan is used on our testing data (how to restore our testing data is > de

Re: Fwd: Query out of memory

On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote: > I am running the below query. Table has 21 million records. I get an Out Of > Memory error after a while.(from both pgadmin and psql). Can someone review Is the out of memory error on the client side ? Then you've simply returned more

Re: Lock contention high

On Tue, Oct 12, 2021 at 01:05:12PM +0530, Ashkil Dighin wrote: > Hi, > Lock contention observed high in PostgreSQLv13.3 > The source code compiled with GNC(GCCv11.x) > PostgreSQL version: 13.3 > Operating system: RHEL8.3 > Kernel name:4.18.0-305.10.2.el8_4.x86_64 > RAM Size:512GB > SSD: 1TB > The

Re: Troubleshooting a long running delete statement

On Wed, Oct 06, 2021 at 06:00:07PM +, Dirschel, Steve wrote: > • 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 started loo

Re: Problem with indices from 10 to 13

On Wed, Sep 29, 2021 at 02:11:15AM +, Daniel Diniz wrote: > How do i increase the statistics target for h.nome_des? > And why uploading the dump at 10 and at 13 is there this difference? It's like ALTER TABLE h ALTER nome_des SET STATISTICS 2000; ANALYZE h; https://www.postgresql.org/docs/cur

Re: Performance for initial copy when using pg_logical to upgrade Postgres

On Fri, Sep 24, 2021 at 03:28:50PM +0100, Westwood, Giles wrote: > At Orcid we're trying to upgrade our Postgres database (10 to 13) using > pg_logical for no downtime. The problem we have is how long the initial > copy is taking for the ~500GB database. If it takes say 20days to complete, > will w

Re: Postgres chooses slow query plan from time to time

On Mon, Sep 13, 2021 at 08:19:40AM -0600, Michael Lewis wrote: > Autovacuum will only run for freezing, right? Insert only tables don't get > autovacuumed/analyzed until PG13 if I remember right. Tomas is talking about autovacuum running *analyze*, not vacuum. It runs for analyze, except on parti

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 (workarounds)

On Mon, Aug 30, 2021 at 04:43:23AM +0200, Pavel Stehule wrote: > po 30. 8. 2021 v 2:44 odesílatel l...@laurent-hasson.com napsal: > > At this point, I am not sure how to proceed except to rethink that > > toFloat() function and many other places where we use exceptions. We get > > such dirty data t

Re: Using regexp from table has unpredictable poor performance

On Wed, Aug 25, 2021 at 11:47:43AM -0500, Jack Christensen wrote: > I have items that need to be categorized by user defined matching rules. > Trusted users can create rules that include regular expressions. I've > reduced the problem to this example. > I use the following query to find matches: >

Re: Postgres using the wrong index index

On Mon, Aug 23, 2021 at 08:53:15PM -0400, Matt Dupree wrote: > Is it possible that the row estimate is off because of a column other than > time? I would test this by writing the simplest query that reproduces the mis-estimate. > I looked at the # of events in that time period and 1.8 million is

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sun, Aug 22, 2021 at 08:44:34PM -0300, Ranier Vilela wrote: > > If there is any way I can help further... I am definitely not able to do a > > dev environment and local build, but if we have a windows developer > > reproducing the issue between 11 and 12, then that should help. If someone > > ma

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sun, Aug 22, 2021 at 10:50:47AM -0300, Ranier Vilela wrote: > > Tried to check this with Very Sleepy at Windows 10 (bare metal). > > Not sure it can help if someone can guide how to test this better? > explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as "a" > from sampletest

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

Could you send SELECT * FROM pg_config() and try to find the CPU model ? I think it's possible the hypervisor is trapping and emulating unhandled CPU instructions. Actually, it would be interesting to see if the performance differs between 11.2 and 11.13. It's possible that EDB compiled 11.13 on

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sat, Aug 21, 2021 at 02:19:50PM -0500, Justin Pryzby wrote: > As I recall, you're running postgres under a windows VM - I'm not sure if > that's relevant. I tried under a couple hyperv VMs but could not reproduce the issue (only an ~8x difference "with exceptions&qu

Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

On Sat, Aug 21, 2021 at 02:17:26PM -0400, Tom Lane wrote: > "l...@laurent-hasson.com" writes: > > So you mean that on average, the 4x overhead of exceptions is around what > > you'd expect? > > Doesn't surprise me any, no. Exception recovery has to clean up after > a wide variety of possible er

Re: Postgres using the wrong index index

On Mon, Aug 16, 2021 at 11:22:44AM -0400, Matt Dupree wrote: > > Is either half of the AND estimated correctly? If you do a query > > with only ">=", and a query with only "<=", do either of them give an > > accurate rowcount estimate ? > > Dropping >= results in the correct index being used. Dro

Re: Postgres using the wrong index index

On Thu, Aug 12, 2021 at 09:38:45AM -0400, Matt Dupree wrote: > > The rowcount estimate for the time column is bad for all these plans - do > > you > > know why ? You're using inheritence - have you analyzed the parent tables > > recently ? > > Yes. I used ANALYZE before posting, as it's one of

Re: Postgres using the wrong index index

The rowcount estimate for the time column is bad for all these plans - do you know why ? You're using inheritence - have you analyzed the parent tables recently ? | Index Scan using other_events_1004175222_pim_evdef_67951aef14bc_idx on public.other_events_1004175222 (cost=0.28..1,648,877.92 rows

Re: Postgres using the wrong index index

On Tue, Aug 10, 2021 at 12:47:20PM -0400, Matt Dupree wrote: > Here's the plan: https://explain.depesz.com/s/uNGg > > Note that the index being used is Could you show the plan if you force use of the intended index ? For example by doing begin; DROP INDEX indexbeingused; explain thequery; rollba

  1   2   3   4   5   >