Re: Query takes around 15 to 20 min over 20Lakh rows

What is T and how many rows are in there? How many rows in task_history? What indexes exist? Are you confident you want 2 million rows in that result set? What version is this on? What pg_settings have been changed from defaults?

How to observe plan_cache_mode transition from custom to generic plan?

TL;DR -- 1/ My basic prepared statement doesn't seem to start out with a custom plan (i.e., no parameter symbols in query text). 2/ EXPLAIN EXECUTE of prepared statement doesn't show query text, as the documentation seems to imply. Should it? 3/ How can I observe the effect of plan_cache_mode?

Re: Query takes around 15 to 20 min over 20Lakh rows

. *Michael Lewis | Database Engineer* *Entrata*

Re: How to observe plan_cache_mode transition from custom to generic plan?

Hi Laurenz, Thank you for the quick response and clear demonstration. I have a few comments, inline below. On Mon, Sep 6, 2021 at 8:26 AM Laurenz Albe wrote: > On Sun, 2021-09-05 at 14:20 -0400, Mladen Gogala wrote: > > On 9/5/21 9:35 AM, Richard Michael wrote: > > > TL;D

Re: How to observe plan_cache_mode transition from custom to generic plan?

Hi David, On Sun, Sep 5, 2021 at 8:32 PM David G. Johnston wrote: > On Sunday, September 5, 2021, Richard Michael > wrote: > >> >> Based on the documentation, I expected the first planned query text to >> be: `SELECT 10 AS data`, since it should be a custom plan

Re: How to observe plan_cache_mode transition from custom to generic plan?

Hi Tom, On Thu, Sep 9, 2021 at 6:02 PM Tom Lane wrote: > Richard Michael writes: > > After reading Laurenz's reply, I experimented again with `PREPARE > > basic(int) AS SELECT $1 AS number;" and the query plan logged in the log > > file (by auto_explain) does

Re: How to observe plan_cache_mode transition from custom to generic plan?

On Thu, Sep 9, 2021 at 7:39 PM Tom Lane wrote: > Richard Michael writes: > > Would a tiny patch to the PREPARE docs be accepted? I'd like to help > > clarify investigating `plan_cache_mode` for future readers. > > What did you have in mind? > > (I'm kin

Re: How to observe plan_cache_mode transition from custom to generic plan?

On Fri, Sep 10, 2021 at 10:38 AM Laurenz Albe wrote: > On Thu, 2021-09-09 at 16:51 +0200, Richard Michael wrote: > > > > 2/ EXPLAIN EXECUTE of prepared statement doesn't show query text, as > the > > > > documentation seems to imply. Should it? > > &g

Re: Fastest option to transfer db?

What version of Postgres is the source? Can you make use of logical replication?

Re: The tragedy of SQL

I started programming in 1967, and over the last 50+ years I've programmed in more languages than I would want to list. I spent a decade writing in FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited memory space, so you had to write EFFICIENT code, something that is a bit of

Re: The tragedy of SQL

Of all the languages I wrote in, I think SNOBOL was the most fun to write in, and LISP the least fun. Control Data assembler language programming was probably the most precise, because you could crash the OS with a single mis-placed character, something I did more than once. In a graduate-level c

Re: Alter and move corresponding: was The tragedy of SQL

When I was working at the help desk at the computer center as an undergrad, the professor in charge of that group used to give us interesting little language tests for things we needed to watch out for, especially with beginning programmers. One of his favorite ploys was to use the EQUIVALENCE fun

Re: The tragedy of SQL

On Wed, Sep 15, 2021 at 7:31 PM FWS Neil wrote: > > Programmers create a dozens of new languages every 10 years or so. Only a > few have stood the test of time. SQL is one of those. For all its faults, > it still is amazingly powerful. > > Neil > www.fairwindsoft.com > > Dennis Ritchie was giv

Re: The tragedy of SQL

In the same 1971 seminar where we studied Algol-68, we had to read and write a short paper on the 1970 Codd paper on relational theory, which had only been out for about a year. The professor running the seminar noted that Codd proved that the relational model worked, but didn't guarantee that it

Re: Alter and move corresponding: was The tragedy of SQL

One of the grad students in the computer center had a sign on his wall: God is real, but Man is only an integer. -- Mike Nolan

Re: Azure Postgresql High connection establishment time

This is not a Postgres issue. Please reach out to the Azure team. >

Re: Question about behavior of conditional indexes

You likely need to tune your autovacuum settings such that the index bloat reaches a steady-state and does not continue to increase. When vacuum runs, it will remove dead (and no longer visible to any running transactions) tuples aka row versions from each page (8KB block by default) in the file fo

Re: Timestamp with vs without time zone.

Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as the rows are localized to a timezone for where/group by purposes. That is, if I have a table which has events, but those events belong to a tenant or some entity that has a loca

Re: Question about behavior of conditional indexes

Just to clarify, I suggested fillfactor likely could *not* help because you are indexing a column that has the value change. The idea with reducing fillfactor is that the rows can be updated with the new versions remaining in the same 8KB block in the file for table/row storage. If the new version

Re: Timestamp with vs without time zone.

On Wed, Sep 22, 2021 at 12:44 AM cen wrote: > On 21. 09. 21 23:34, Michael Lewis wrote: > > Related to this current discussion and exchange of ideas... is there a > best practice for retrieving data in such a way as the rows are localized > to a timezone for where/group by purpos

Re: Faster distinct query?

In the future, please share the plan returned by explain analyze, and some data about how many rows in the involved tables, what type of system you are running it on, any changes from default configs, etc. How does the below work? It should do a very simple index scan only, then aggregate the rela

Re: Faster distinct query?

On Wed, Sep 22, 2021 at 2:48 PM Tom Lane wrote: > "David G. Johnston" writes: > > There is no where clause so I'm doubtful there is much to be gained going > > down this path. The Index-Only scan seems like an optimal way to obtain > > this data and the existing query already does that. > > The

Re: Faster distinct query?

> > It’s only when I add in the AND data.channels=channels.channel that the > query time blows up to 5+ minutes. I personally don’t understand why there > would be such a large difference between the two queries - something wrong > with my indexes? > Did you do the manual vacuum as suggested by To

Re: Using a single sequence for all tables

If your processes somehow allow updates on the wrong table, then fix that. If you run out of space in whatever value range you choose initially, the pain to upgrade to a type that allows larger values would seem to be very large.

Re: DELETE ... USING LATERAL

On Mon, Oct 4, 2021, 10:30 AM Nikhil Benesch wrote: > you can't reuse the FROM table name in the USING clause: > > # delete from int_arrays using int_arrays; > ERROR: table name "int_arrays" specified more than once > Don't you need to use an alias for the table in the using clause?

Re: Query time related to limit clause

What's the definition for abc_serv_nch_q1_2021_expr_idx3? That is a jsonb field I assume? Statistics aren't great on jsonb data, so you may benefit greatly from pulling keys out to be stored as a standard column. I would be curious for more "pure" estimates on each quarterly partition directly for

Re: Avg/max size of these JSON docs in Postgres

On Tue, Oct 12, 2021 at 1:43 AM Simon Riggs wrote: > On Tue, 12 Oct 2021 at 08:14, Ram Pratap Maurya > wrote: > > > Confirm what is Avg/max size of these JSON docs in Postgres. > > JSON and JSONB datatypes can both be max 1GB in size. > That is per row. Just to confirm- The compression associa

Re: Avg/max size of these JSON docs in Postgres

On Tue, Oct 12, 2021 at 12:51 PM Simon Riggs wrote: > On Tue, 12 Oct 2021 at 18:53, Michael Lewis wrote: > > > > On Tue, Oct 12, 2021 at 1:43 AM Simon Riggs < > simon.ri...@enterprisedb.com> wrote: > >> > >> On Tue, 12 Oct 2021 at 08:14, Ram Pratap Ma

Re: Force re-compression with lz4

th its default options. The compression ratio is comparable between both, still LZ4 compresses in average less than PGLZ. -- Michael signature.asc Description: PGP signature

Re: Force re-compression with lz4

=# VACUUM FULL cmdata; VACUUM =# SELECT pg_column_compression(f1) FROM cmdata; pg_column_compression --- pglz (1 row) -- Michael signature.asc Description: PGP signature

Re: Force re-compression with lz4

ost likely part of an upgrade, so I don't think that this would be adapted to have anyway. -- Michael signature.asc Description: PGP signature

Re: Force re-compression with lz4

has not been published yet), pg_receivewal (published) and base backups through the replication protocol (published). I have seen rather good numbers when it came to WAL, FWIW. Even if the compression ratio was a bit less than pglz, it was much faster. -- Michael signature.asc Description: PGP signature

Re: Determining if a table really changed in a trigger

Does this perform differently from suppress_redundant_updates_trigger? https://www.postgresql.org/docs/current/functions-trigger.html

Re: How to copy rows into same table efficiently

On Tue, Oct 26, 2021 at 1:07 AM Arun Suresh wrote: > Current approach taken is to build a query like below: > INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3, > col4 FROM mytable WHERE col2 = 'ABCD' > > Is there a better way to do this? > There could be other tables with for

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

> > Planning Time: 10.351 ms > > Execution Time: 0.283 ms > Nearly all of the time is in planning... What is your default_statistics_target?

Re: Determining if a table really changed in a trigger

If you end up with no rows changing from an insert or delete, something seems awry. Unless you mean 0 rows affected. Do after statement triggers still execute? I suppose they very well might. Would the statement even execute if no rows get updated and that is prevented with before update? I would

Re: Model clause and

On Thu, Oct 28, 2021 at 1:57 PM SQL Padawan wrote: > I presume that VIRTUAL GENERATED columns are on the to-do list? > https://www.postgresql.org/docs/current/ddl-generated-columns.html Is this not what you want?

Re: Model clause and

Mea culpa. "PostgreSQL currently implements only stored generated columns." I should re-read what I share references to rather than trust my memory, particularly while sick. Thanks for kindly correcting.

Re: Debugging features needed

For my purposes, app name isn't long enough so we put a comment at the start of every SQL that has a unique ID generated in the application. This ensures that we can tell one connection apart from another even when both are coming from the same feature/action (applicationName is set to this) even u

Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value

Is there any advantage to not defining the default on the replica? If it is not a static value and the publishing database will trigger row updates, I could see waiting to set the default until after the table re-write is done, but otherwise there doesn't seem to be any benefit to skipping column d

Re: Pause streaming replication

nder of the primary, with some pg_terminate_backend() calls, just to throw one idea in the bucket. -- Michael signature.asc Description: PGP signature

Re: Are all unlogged tables in any case truncated after a server-using

Why keep them as unlogged tables? If data is static, can you spare the disk space to gradually copy data from existing unlogged table to new copy that is logged, and then have brief exclusive lock to drop unlogged and rename new one?

Re: Are all unlogged tables in any case truncated after a server-crash?

Curious... why keep the table as unlogged if it is static? If you can spare the disk space, perhaps just create a regular table with same definition, gradually copy the data to spread the impact on WAL, and when complete, just drop the old table and rename the new one.

Row estimate on empty tables

ref: https://www.postgresql.org/message-id/9772.1510348781%40sss.pgh.pa.us This functionality hasn't changed in the years since this post, right? I'm dealing with temp tables specifically, but seem to be getting bit by this behavior occasionally so I would like to confirm. *Mic

Re: Execute command in PL/pgSQL function not executing

You could also just return the name of the temp table, or return nothing and directly use that name manually outside the function to do select * from pg_temp.my_data

Re: [EXTERNAL] Re: Inserts and bad performance

example and run it in a transaction with explain (analyze, buffers, verbose) and then rollback? *Michael Lewis | Database Engineer* *Entrata*

Re: split postgresql logfile

this support, but that won't apply to already-released versions.) -- Michael signature.asc Description: PGP signature

Re: Database Scalability

Store a connection string for each tenant or the metadata to build one on demand. Then each tenant is in its own schema on whatever database instance the connection string points at. Then it doesn’t really matter how you spread your tenants across one database or many; just do whatever works b

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent wrote: > Also be on the look-out for begin trans; begin trans; etc I read Kumar's > report as nested transactions. If your gang is doing a transaction per > row, they need a kick in the rear. Anyone not closing those needs a pink > slip. > I have see

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 8:54 PM Rob Sargent wrote: > On 12/1/21 7:08 PM, Michael Lewis wrote: > > On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent wrote: > >> Also be on the look-out for begin trans; begin trans; etc I read >> Kumar's report as nested transacti

Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 9:06 PM Dilip Kumar wrote: > IMHO, it is good to LOG such information if we are not already logging > this anywhere. > +1 I expect my 'vote' counts for naught, but I fully expect seeing these show up in the logs would have helped me much more quickly have insight into wha

Re: INSERT ... ON CONFLICT doesn't work

Perhaps I missed something, but why all this effort to reference the column and not just reference IX_Lockers_Uuid for the on conflict clause?

Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

What is your default_statistics_target and how accurate is that estimate of 5668 rows? What is random_page_cost set to by the way? More importantly, what is the better plan that you'd like the planner to use with your existing indexes? It would seem logical to me to scan for the matching shipment_

Re: Postgresql + containerization possible use case

On Fri, Dec 10, 2021, 2:24 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > Hi > we are running some 140 remote servers (in the 7 seas via satellite > connections) How are they used? What is in Postgres? Should that all have the exact same read only data at all times? >

Re: CTE Materialization

On Thu, 9 Dec 2021 at 10:29, Paul van der Linden wrote: > This one quite nicely explains it: > https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subquery > Given indexes applicable to multiple expressions in a WHERE condition, how does postgres decide which

Re: Postgresql + containerization possible use case

Interesting. I'm not sure that moving an image for a VM with Postgres is compatible with the goal of minimizing time/data but if upgrades are yearly or something, perhaps that is reasonable. It has been 9+ years since that post as well. But the full data will need to be included in the image if the

Re: log shipping with pg_receivewal

ay for nothing. Streaming replication would be likely your best, and cheapest, option here. -- Michael signature.asc Description: PGP signature

Re: Reindex "locked" standby database

d be > locking the WAL process. Any insight would be more than welcome! That's not going to be easy without more information, I am afraid. -- Michael signature.asc Description: PGP signature

md5 issues Postgres14 on OL7

hough disabling SELinux didn't solve the issue. Thanks in advance, Michael

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule wrote: > I wrote about it. Did you read this article? > > https://okbob.blogspot.com/2018/02/schema-variables.html > > The goals of this project: > >- fast non transactional non persistent (session based) storage, > > Would there be statistics th

Re: [External] Re: md5 issues Postgres14 on OL7

thanks Christoph, best, Michael -Original Message- From: Christoph Moench-Tegeder Date: Monday, 20. December 2021 at 15:22 To: Michael Mühlbeyer Cc: "pgsql-general@lists.postgresql.org" Subject: [External] Re: md5 issues Postgres14 on OL7 Hi! ## Michael Mühlbeyer (micha

Default values in functions

ATE + interval '2 hour', false ), ( null::int[] , CURRENT_DATE + interval '3 hour', true ), ( null , CURRENT_DATE + interval '4 hour', true ) )AS sub ( pArrayToCheck, pTimeToDisplay, expected_result ); *Michael Lewis | Database Engineer* *Entrata*

Re: Default values in functions

On Wed, Dec 29, 2021, 4:34 PM David G. Johnston wrote: > That isn’t how it works. Absence is what is important. Null is not > absence. As you showed, if you want nulls to be converted to defaults you > can use coalesce. > Thanks sir. It seems unfortunate that there is not a way to indicate ab

Re: Default values in functions

On Wed, Dec 29, 2021, 4:31 PM Tom Lane wrote: > You didn't say what icount() is, but if it's the one from > contrib/intarray, it's STRICT meaning it'll return NULL, > not zero, for a null array input. Thanks for that. Very good to know. (Or IOW, null::int[] is not at all the same thing as arra

Re: Default values in functions

On Wed, Dec 29, 2021, 5:31 PM Tom Lane wrote: > The way to do that is to use named parameters and the associated > call syntax, ie something like > > select myfunc(param1 => 42, param3 => 99); > Thanks very much. I have not seen that before. >

Re: md5 issues Postgres14 on OL7

c, with a complain coming from OpenSSL's EVP_MD_CTX_create(), but there are other palloc() calls in this area as well. -- Michael signature.asc Description: PGP signature

Re: md5 issues Postgres14 on OL7

sh". Any code paths calling the routines of md5_common.c just do that as well for ages when the computation fails, and that's what we care about here. -- Michael signature.asc Description: PGP signature

Re: md5 issues Postgres14 on OL7

ntation context a location to a static string, and add a new routine to extract it if there is an error, defaulting to OOM. -- Michael signature.asc Description: PGP signature

Re: md5 issues Postgres14 on OL7

On Wed, Jan 05, 2022 at 04:09:12PM +0900, Michael Paquier wrote: > In order to make things portable with 14 in cryptohash.c, we don't > have any need to change the existing cryptohash APIs. We could just > store in each implementation context a location to a static string, > and

Re: md5 issues Postgres14 on OL7

etail in md5_crypt_verify() and plain_crypt_verify() to feed back a LOG entry to the postmaster on those failures, and saw that it is safe to assign directly the error returned by the cryptohash APIs, avoiding the extra psprintf call that could become an issue under memory pressure. What do you thin

Re: md5 issues Postgres14 on OL7

ounds fair to me in the long term, even for non-assert builds. I have added a small-ish wrapper routine in crytohash_openssl.c for this purpose, with a name copied from {be,fe}-secure-openssl.c to ease any future refactoring lookups if that proves to be worth in the future. -- Michael From 0e7ceca421a

Re: md5 issues Postgres14 on OL7

The error areas related to checksum_helper.c and backup_manifest.c could be improved more. Now these refer only to scenarios unlikely going to happen in the field, so I have left that out. -- Michael signature.asc Description: PGP signature

Re: could not accept SSL connection: Success

This would mean that relying on %m would be wrong for this case. And I guess that you are using a version of OpenSSL older than 3.0? -- Michael signature.asc Description: PGP signature

Re: could not accept SSL connection: Success

0. That's mostly what you suggested on the other thread. The error handling changes are really cosmetic, so I'd rather leave the back-branches out of that. Thoughts? -- Michael diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c index 3d0168a

Re: could not accept SSL connection: Success

e about the business with 3.0 though? My gut is telling me that we'd better revisit those code paths in a couple of years when support for legacy OpenSSL is removed, and most likely we would have forgotten about all those details. -- Michael signature.asc Description: PGP signature

Re: could not accept SSL connection: Success

On Wed, Jan 19, 2022 at 08:06:30PM -0500, Tom Lane wrote: > Michael Paquier writes: > > Leaving things in their current state is fine by me. Would it be > > better to add a note about the business with 3.0 though? > > What do you envision saying? "We don't nee

Re: Query much slower from php than psql or dbeaver

When dealing with foreign tables, I believe planning is not the same because of access to statistics (maybe has improved since 9.6 though). I just wonder... Would it be a viable option to create a materialized view using the FDW but then use the PHP script against the local tables only? Materialize

Re: recording of INDEX creation in tables

. Here are some links; https://www.postgresql.org/docs/devel/catalog-pg-index.html https://www.postgresql.org/docs/devel/view-pg-indexes.html https://www.postgresql.org/docs/devel/event-triggers.html -- Michael signature.asc Description: PGP signature

Re: How are md5.h: pg_md5_hash() function and below functions working?

ary() computes 16 raw bytes. pg_md5_encrypt() is a utility wrapper that does the operation of pg_md5_hash() based on a password and a salt, used at authentication time for MD5. Its result is a 36-byte long string, prefixed with "md5" and a 33-byte long hex string. Those routine names are historic. -- Michael signature.asc Description: PGP signature

Re: tstzrange on large table gives poor estimate of expected rows

If interval_end_date is always 1 day ahead, why store it at all? Dependencies on a custom stats object wouldn't do anything I don't think because they are offset. They are 100% correlated, but not in a way that any of the existing stat types capture as far as I can figure.

Undetected Deadlock

Hello Experts I'm hoping you will be able to help me with a tricky issue. We've recently updated our application to PG 14.1, and in the test instance we have started to see some alarming undetected deadlocks. An example of what we have seen is: locktype | database | relation | page | tuple |

Re: Undetected Deadlock

easier to read. Cheers Mike On Tue, 25 Jan 2022 at 15:49, Michael Harris wrote: > > Hello Experts > > I'm hoping you will be able to help me with a tricky issue. > > We've recently updated our application to PG 14.1, and in the test instance we > have started

Re: Undetected Deadlock

her story if it is not detected! I have enabled `log_statement=all`, but the undetected deadlock hasn't happened again since. I can easily reproduce the deadlock itself, but not the undetected case. Thanks again. Cheers Mike On Wed, 26 Jan 2022 at 10:11, Alvaro Herrera wrote: > > On

Re: Undetected Deadlock

I must be missing something. You mentioned dropping a partition, so is there an actual need for the delete? Could you detach concurrently and then drop the table or delete rows if needed? https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION

Re: Undetected Deadlock

> I must be missing something. You mentioned dropping a partition, so is there > an actual need for the delete? Could you detach concurrently and then drop > the table or delete rows if needed? The DELETE is part of a transaction performing data loading. Our application allows data to be overwri

Re: Undetected Deadlock

There may be a bug so perhaps still pursue reproducing the issue, but I would expect drop concurrently to resolve your issue with the two processes conflicting. Also, perhaps trying"insert, on conflict do update" could be more efficient than the copy but obviously there are too many unknowns and va

Re: could not open relation with OID

h reports recently. > Other things we've considered: >     - we run pg_repack, which certainly seems like it could make an error > like this, but we see this error in places and times that pg_repack isn't > currently running It could also take time for the issue to show up, depending on the state of the relcache. -- Michael signature.asc Description: PGP signature

Re: Undetected Deadlock

to my attention. Cheers Mike On Thu, 27 Jan 2022 at 14:20, Michael Lewis wrote: > > There may be a bug so perhaps still pursue reproducing the issue, but I would > expect drop concurrently to resolve your issue with the two processes > conflicting. Also, perhaps trying"insert

Re: pg_try_advisory_lock is waiting?

pg_try_advisory_lock and other advisory lock functions take a lock on a key. The keys you have tried to lock are 0 and 1. Why? What problem are you trying to solve by changing from "for update" lock to advisory lock anyway?

Re: pg_try_advisory_lock is waiting?

On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala wrote: > pg_try_advisory_lock returned TRUE even without "FOR UPDATE" clause in the > subquery. Shouldn't it return false because it can't lock the row until the > uncommitted update finishes? > > > > The rows bein

Re: Undetected Deadlock

Hi The undetected deadlock occurred again today and I was able to collect some more info. The presentation was very similar to the case I reported previously: - One backend trying to do a DROP TABLE on a partition of a partitioned table, waiting for an AccessExclusiveLock on that table - Another

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

What does the row estimate look like on the scan of data table with that statistic in place? Anytime the stats give a mis-estimate this far off, I wouldn't expect that plans would be optimal except by luck. Index Scan using data_pkey on data t (cost=0.57..21427806.53 *rows=58785023* width=131) (ac

Re: Undetected Deadlock

pgsql-hackers list. Cheers Mike On Tue, 1 Feb 2022 at 17:50, Michael Harris wrote: > > Hi > > The undetected deadlock occurred again today and I was able to collect > some more info. > > The presentation was very similar to the case I reported previously: > - One backend tr

Re: pg_cron for vacuum - dynamic table set

Can't you use a do script to construct and execute the statement? >

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

I would guess that autovacuum is analyzing the table and causing the stats to change which is resulting in different estimates that result in different plans. Unless you can get the estimate much more accurate, you won't get far with expecting a stable plan that performs well. How is data_class_pk

Re: Undetected Deadlock

> If Michael's analysis were accurate, I'd agree that there is a robustness > issue, but I don't think there is. See timeout.c:220: Actually that only sets a new timer after the nearest timeout has expired. The pattern I was seeing went like this: 1. Command occurs during which a signal was not

Re: Undetected Deadlock

On Mon, 7 Feb 2022 at 09:57, Tom Lane wrote: > Do you want to try this and see if it actually adds any robustness with your > buggy code? Sorry for the delayed response, & thanks for the patch. I wasn't able to test with our actual application because it could take days for it to actually trigg

FDW error on remote view

I'm working on building a demonstration of this error, but I figured I'd ask the question first. BACKGROUND I've got two databases, stage and app. There is a postgres_fdw connection between them so that stage can see objects in the app database. The app database contains complex metadata that is

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

I think that theoretically if the planner sees a condition like a.column1 > constant_value, and it also has a condition like a.column1 = b.column2 then it could autogenerate the b.column2 > constant_value condition. And of course > could be <, <=, >= and <> But I could be wrong, particularly with

Re: Rows From but with Subqueries (or a cleaner non-array-using alternative)?

In pseudo code, group_index is defined as: case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right? If you have that in the first cte instead of the start/end business, then you can just select vals, group number, and row_num over that new grouping, right? Something like this? WITH

<    3   4   5   6   7   8   9   >