Should from_collapse be switched off? (queries 10 times faster)

My queries get up to 10 times faster when I disable from_collapse (setting from_collapse_limit=1). After this finding, The pramatic solution is easy: it needs to be switched off. BUT: I found this perchance, accidentally (after the queries had been running for years). And this gives me some ques

Re: Should from_collapse be switched off? (queries 10 times faster)

On Fri, Mar 23, 2018 at 12:41:35PM +0100, Laurenz Albe wrote: ! https://www.postgresql.org/docs/current/static/explicit-joins.html ! states towards the end of the page that the search tree grows ! exponentially with the number of relations, and from_collapse_limit ! can be set to control that. Ye

Re: Should from_collapse be switched off? (queries 10 times faster)

The problem appeared when I found the queries suddenly taking longer than usual. Investigation showed that execution time greatly depends on the way the queries are invoked. Consider fn(x) simply a macro containing a plain SQL SELECT statement returning SETOF (further detail follows below): # SEL

Re: Should from_collapse be switched off? (queries 10 times faster)

On Fri, Mar 23, 2018 at 10:14:19AM -0400, Tom Lane wrote: ! It's conceivable that the OP's problem is actually planning time ! (if the query joins sufficiently many tables) and that restricting ! the cost of the join plan search is really what he needs to do. Negative. Plnning time 10 to 27 ms. E

functions: VOLATILE performs better than STABLE

Given an arbitrary function fn(x) returning numeric. Question: how often is the function executed? A. select fn('const'), fn('const'); Answer: Twice. This is not a surprize. B. select v,v from fn('const') as v; [1] Answer: Once. C. select v.v,v.v from (select fn('const') as v) as v;

Re: Should from_collapse be switched off? (queries 10 times faster)

Laurenz, thank You very much for Your comments! On Sun, Mar 25, 2018 at 07:12:08AM +0200, Laurenz Albe wrote: ! Your reported execution times don't match the time reported in the ! EXPLAIN output... Should these match? It seems the EXPLAIN (ANALYZE, BUFFERS) does additional things, not just e

Re: Out of Memory errors are frustrating as heck!

On Sun, Apr 14, 2019 at 05:19:11PM -0400, Tom Lane wrote: ! Gunther writes: ! > For weeks now, I am banging my head at an "out of memory" situation. ! > There is only one query I am running on an 8 GB system, whatever I try, ! > I get knocked out on this out of memory. It is extremely impenetrab

pgaudit and create postgis extension logs a lot inserts

tever you want without any audit. I supposed this changing of audit session log parameter should be logged to file? Regards, Peter

SV: pgaudit and create postgis extension logs a lot inserts

they have disabled pgaudit. I think postgis extension is not the only extention that creates both tables, functions and insert data, but if there are a way to configure pgaudit so you get rid of the inserts maybe its a way to handle it. /Peter Från: Joe

SV: pgaudit and create postgis extension logs a lot inserts

OK, thanks a lot. Regards, Peter Från: David Steele [da...@pgmasters.net] Skickat: den 19 januari 2018 14:41 Till: Magnus Hagander; Joe Conway Kopia: Svensson Peter; pgsql-performa...@postgresql.org Ämne: Re: pgaudit and create postgis extension logs a

Re: blending fast and temp space volumes

e starting the database. I've done > some tests and it seems to work great. I don't mind rolling back any > transaction that might be in play during a power failure. It sounds like you're treating a temp_tablespaces tablespace as ephemeral, which IIRC can have problems that an

Re: blending fast and temp space volumes

On Wed, Feb 21, 2018 at 12:07 PM, Claudio Freire wrote: > On Wed, Feb 21, 2018 at 4:50 PM, Peter Geoghegan wrote: >> On Wed, Feb 21, 2018 at 7:53 AM, Rick Otten wrote: >>> side note: The disadvantage of local SSD is that it won't survive "hitting >>> the

Re: creating hash indexes

#x27;s that they have much more *predictable* performance characteristics as conditions change. -- Peter Geoghegan

Re: time sorted UUIDs

Hi Tim -- I am looking at the issue of random IDs (ie, UUIDs) as well. Did you have a chance to try time sorted UUIDs as was suggested in one of the responses? On Mon, Apr 17, 2023 at 5:23 PM Tim Jones wrote: > Hi, > > could someone please comment on this article > https://vladmihalcea.com/uuid-

High QPS, random index writes and vacuum

Hi all The company I work for has a large (50+ instances, 2-4 TB each) Postgres install. One of the key problems we are facing in vanilla Postgres is vacuum behavior on high QPS (20K writes/s), random index access on UUIDs. In one case the table is 50Gb and has 3 indexes which are also 50Gb each.

Re: High QPS, random index writes and vacuum

On Mon, Apr 17, 2023 at 5:35 PM peter plachta wrote: > My question is: what is the recommended strategy to deal with such cases in > Postgres? You didn't say what version of Postgres you're using... -- Peter Geoghegan

Re: High QPS, random index writes and vacuum

rstand whether there are any known workarounds for random access + index vacuums. Are my vacuum times 'normal' ? On Mon, Apr 17, 2023 at 7:01 PM Tom Lane wrote: > peter plachta writes: > > The company I work for has a large (50+ instances, 2-4 TB each) Postgres > > inst

Re: High QPS, random index writes and vacuum

Thank you David -- I increased this to 1GB as well (seeing as that was the max). We are doing mostly single passes now. On Mon, Apr 17, 2023 at 7:40 PM David Rowley wrote: > On Tue, 18 Apr 2023 at 12:35, peter plachta wrote: > > I increased work_mem to 2Gb > > maintenance_

Re: High QPS, random index writes and vacuum

On Mon, Apr 17, 2023 at 7:43 PM peter plachta wrote: > Version: I sheepishly admit it's 9.6, 10 and 11 (it's Azure Single Server, > that's another story). If you can upgrade to 14, you'll find that there is much improved management of index updates on that version:

Re: Forced to use UNION ALL when having multiple ANY operators and ORDER BY LIMIT

s, what is the effect of "set enable_indexscan = off" on your original query? Does that speed up execution at all? (I think that this approach ought to produce a plan that uses a bitmap index scan in place of the index scan, without changing anything else.) -- Peter Geoghegan

Results of experiments with UUIDv7, UUIDv8

(actually, pretty good random distribution on a 500Gb table). This isn't as much as I expected, but, again for large indexes, slow IO, it was significant. peter

Table copy with SERIALIZABLE is incredibly slow

Hi all Background is we're trying a pg_repack-like functionality to compact a 500Gb/145Gb index (x2) table from which we deleted 80% rows. Offline is not an option. The table has a moderate (let's say 100QPS) I/D workload running. The typical procedure for this type of thing is basically CDC: 1.

Re: Index bloat and REINDEX/VACUUM optimization for partial index

ich Postgres version you're on. Note that Postgres 14 can deal with index bloat a lot better than earlier versions could. This is known to work well with partial indexes. See: https://www.postgresql.org/message-id/flat/CAL9smLAjt9mZC2%3DqBeJwuNPq7KMAYGTWWQw_hvA-Lfo0b3ycow%40mail.gmail.com -- Peter Geoghegan

Re: Trying to understand why a query is filtering when there is a composite index

btree-index-scans -- Peter Geoghegan

Re: Trying to understand why a query is filtering when there is a composite index

y will need to upgrade to 17. -- Peter Geoghegan

Re: Trying to understand why a query is filtering when there is a composite index

that part is a weakness I've wanted to fix for a long > time: it could do the filter condition by fetching b from the > index, but it doesn't notice that and has to go to the heap > to get b. It was fixed? At least on 17. -- Peter Geoghegan

Re: Trying to understand why a query is filtering when there is a composite index

ch that makes as many decisions as possible at runtime. This is particularly true of how we skip within the index scan. I wouldn't expect skipping to be useful in the exact scenario shown, but why not be open to the possibility? If the planner only has one choice then there are no wrong choices. -- Peter Geoghegan

Re: Has gen_random_uuid() gotten much slower in v17?

On 10.09.24 15:58, David Mullineux wrote: I'm getting a bit concerned by the slow performance of generating uidds on latest dev code versus older versions. Here I compare the time to generate 50k random uuids. Both son the same machine. I must be missing something. Are you sure that the 18dev

Re: Trigger overhead/performance and alternatives?

? Maybe wal2json will give you a starting point. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: How can sort performance be so different

viated keys technique. The system glibc's collations cannot use this optimization. I believe that some locales have inherently more expensive normalization processes (comparisons) than others, but if you can effective amortize the cost per key by building an abbreviated key, it may not ma

Re: Transaction size and Wal2Json

It's plausible that some naive coding would run into the limitation that you describe, but a bit of effort can probably solve it. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: How can sort performance be so different

wrong. I know a lot more about this stuff than most people on this mailing list, but I'm still far from being an expert. -- Peter Geoghegan

Re: How can sort performance be so different

On Wed, Feb 20, 2019 at 2:25 PM Peter Geoghegan wrote: > I suspect that the reasons why the Lao locale sorts so much slower may > also have something to do with the intrinsic cost of supporting more > complicated rules. I strongly suspect that it has something to do with the issue descr

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

ly grow as the workload needs to scale up? -- Peter Geoghegan

Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

problem is described in high level terms from a user's perspective here: https://brandur.org/postgres-queues -- Peter Geoghegan

Failure to reordering in case of a lateral join in combination with a left join (not inner join) resulting in suboptimal nested loop plan

Hi, The queries in what follows can be executed on the following fiddle: *https://dbfiddle.uk/?rdbms=postgres_10&fiddle=64542f2d987d3ce0d85bbc40ddadf7d6 * - Please note that the queries/functions might look silly/point

Re: GCC 8.3.0 vs. 9.0.1

I'm afraid whatever they're measuring is a more-or-less chance > effect rather than a real system-wide code improvement. Or a compiler bug. Link-time optimizations give the compiler a view of the program as a whole, not just a single TU at a time. This enables it to perform additional aggressive optimization. -- Peter Geoghegan

Re: Temporarily very slow planning time after a big delete

slow > query, though. What columns are indexed by index_unproc_notifications_on_notifiable_type, and what are their datatypes? -- Peter Geoghegan

Re: Temporarily very slow planning time after a big delete

the total number of distinct keys rather low? Not just at any given time, but over time? -- Peter Geoghegan

Re: Temporarily very slow planning time after a big delete

s a far more noticeable impact on VACUUM duration than you might expect, since autovacuum is throttled by delays that vary according to how many pages were dirtied (and other such factors). -- Peter Geoghegan

Re: UUID v1 optimizations...

ike keys. Btw., it might be nice to rerun your tests with PostgreSQL 12beta1. The btree storage has gotten some improvements. I don't think it's going to fundamentally solve your problem, but it would be useful feedback. -- Peter Eisentraut http://www.2ndQuadrant.com/

Re: Incorrect index used in few cases..

and that i_tc_adid_tid is 36 MB, not 57 MB. I can see that both i_tc_lud_agi and i_tc_adid_tid consistently use the "split after new tuple" optimization on v12. -- Peter Geoghegan

Re: Perplexing, regular decline in performance

formance drops, steadily, until the > next week's cleaning operation. Each week the performance increase is clear > and significant. Can you show us the definition of the table, including its indexes? Can you describe the data and distribution of values within the columns, particularly where they're indexed? -- Peter Geoghegan

Re: Perplexing, regular decline in performance

happens to be contained in the statistics, such as the most common values. -- Peter Geoghegan

Re: UUID v1 optimizations...

he standard UUID to enable > time-wise index sort order. This will naturally be very close to > physical order but I doubt that this is something I can tell PostgreSQL, or? PostgreSQL only knows whether or not your page splits occur in the rightmost page in the index -- it fills the page differently according to whether or not that is the case. -- Peter Geoghegan

Re: Huge shared hit for small table

ou can use REINDEX CONCURRENTLY (but beware there's a crash > affecting its progress reporting, fix to be included in v12.1). PG v12 will store B-Tree duplicates in heap TID order, so if that's the problem then upgrading to v12 (and REINDEXing if the upgrade was performed using pg_upgrade) will fix it for good. -- Peter Geoghegan

Re: Huge shared hit for small table

ave fixed the problem. That seems like the simplest and most likely explanation to me, even though it isn't particularly simple. -- Peter Geoghegan

Re: Huge shared hit for small table

not just the tables that happen to have been accessed so far in the long running transaction. This is necessary because nothing stops the long running transaction from SELECTing data from any table at any time -- we need to pessimistically keep around the data required to make that work.

Re: Consecutive Query Executions with Increasing Execution Time

ot;buffers shared/read" numbers? That seems odd. -- Peter Geoghegan

Re: Very slow Query compared to Oracle / SQL - Server

l Postgres contributor, but it's easy to be wrong in any given instance. The real explanation might be something else entirely. Though it's hard not to imagine that what really matters here is avoiding all of that bitmap heap scan I/O. -- Peter Geoghegan

Re: Very slow Query compared to Oracle / SQL - Server

On Fri, May 7, 2021 at 2:28 PM Peter Geoghegan wrote: > That's a very reasonable interpretation, since the bitmap index scans > themselves just aren't doing that much I/O -- we see that there is > much more I/O for the heap scan, which is likely to be what the > general

Re: Big performance slowdown from 11.2 to 13.3

ted. It might even make sense to *decrease* work_mem and increase hash_mem_multiplier even further than 4.0. That approach is more aggressive, though, so I wouldn't use it until it actually proved necessary. -- Peter Geoghegan

Re: Big performance slowdown from 11.2 to 13.3

input data is clustered. I recall noticing that that could significantly alter the behavior of HashAggs as of Postgres 13. -- Peter Geoghegan

Re: Big performance slowdown from 11.2 to 13.3

ash_mem() to be unable to return a hash_mem value that could be represented by work_mem directly. MAX_KILOBYTES is an annoyingly low limit on Windows, where sizeof(long) is 4. But that's nothing new. -- Peter Geoghegan

Re: Big performance slowdown from 11.2 to 13.3

machine had enough RAM. Now, the performance sucks and > there is no knob you can turn to fix it. That's unacceptable in my book. Oh! That makes way more sense. I suspect David's theory about hash_agg_set_limits()'s ngroup limit is correct. It certainly seems like a good starting point. -- Peter Geoghegan

Re: Big performance slowdown from 11.2 to 13.3

On Thu, Jul 22, 2021 at 9:53 AM Peter Geoghegan wrote: > I suspect David's theory about hash_agg_set_limits()'s ngroup limit is > correct. It certainly seems like a good starting point. I also suspect that if Laurent set work_mem and/or hash_mem_multiplier *extremely* ag

Re: Big performance slowdown from 11.2 to 13.3

of long, we don't actually increase risk for non-Windows platforms to any significant degree. I'm pretty sure that "long" means int64 on non-Windows 64-bit platforms anyway. -- Peter Geoghegan

Re: Big performance slowdown from 11.2 to 13.3

r way, as far as addressing this long standing "int64 vs long" issue goes. -- Peter Geoghegan

Re: Big performance slowdown from 11.2 to 13.3

r work_mem consumer on Windows. Of course this won't matter much to users like Laurent. -- Peter Geoghegan

Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle

ts made by both Laurenze and Jeff. They may not be irreconcilable, or anything like it. -- Peter Geoghegan

Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle

e for discussion -- it's easy for two groups of people with similar but different terminology to talk past each other. For example, I think that there may be confusion about what is possible with a tool like eBPF today, and what might be possible in an ideal world. -- Peter Geoghegan

Re: Better, consistent instrumentation for postgreSQL using a similar API as Oracle

model, which includes automation, but also some high level top-down theory. -- Peter Geoghegan

Re: Lock contention high

;ll see will be out of all proportion to what you're ever likely to see in the real world. -- Peter Geoghegan

Re: Lock contention high

tune for it), it's not too sensitive to mwm. You just don't end up accumulating too many TIDs to delete from indexes in practice, even though the overhead from VACUUM is a concern. The new autovacuum instrumentation in Postgres 14 makes this far clearer. -- Peter Geoghegan

Advice needed: query performance deteriorates by 2000% within 1 minute

ast_analyze| 2022-02-24 03:20:05.79219+01 last_autoanalyze| vacuum_count| 3 autovacuum_count| 0 analyze_count | 8 autoanalyze_count | 0 The entry in pg_stat_all_tables after the manual vacuum: relid | 155544 schemaname | orderstore relname | order seq_scan| 249 seq_tup_read| 2209150 idx_scan| 24753 idx_tup_fetch | 1155561 n_tup_ins | 87 n_tup_upd | 1404 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 9476 n_dead_tup | 0 n_mod_since_analyze | 152 n_ins_since_vacuum | 0 last_vacuum | 2022-02-24 14:32:16.083692+01 last_autovacuum | last_analyze| 2022-02-24 03:20:05.79219+01 last_autoanalyze| vacuum_count| 4 autovacuum_count| 0 analyze_count | 8 autoanalyze_count | 0 Can someone provide any hints on how to deal with this issue? What am I missing? In case you need additional informations pls let me know. kind regards, peter

Re: Advice needed: query performance deteriorates by 2000% within 1 minute

efaults. Will json-processing experience some improvements in pg14/15? We are about to update to 14 in the near future with our devs saying that this topic is the main trigger to do so. Any further thoughts on the case are very much appreciated. kr p. Am Do., 24. Feb. 2022 um 17:10 Uhr schrieb Tom

LISTEN NOTIFY sometimes huge delay

ation inserting the data is ok/fast. So the insert of the data is not slowed down. Are the notifications delivered asynchronously to the commit/trigger? Thanks for any help, Peter