Rodrigo Rosenfeld Rosas wrote:
> explain analyze delete from field_values where transaction_id=226;
> QUERY PLAN
> ---
> Delete on field_valu
Rodrigo Rosenfeld Rosas wrote:
> Em 05-12-2017 15:25, Tom Lane escreveu:
> > > Normally this is because you lack indexes on the referencing columns, so
> > > the query that scans the table to find the referencing rows is a
> > > seqscan.
> > Actually though ... the weird thing about this is that I
Please show the output of these queries in the relevant databases:
select name, setting, source, sourcefile, sourceline from pg_settings where
name like '%vacuum%';
select oid::regclass, reloptions from pg_class where reloptions is not null;
--
Álvaro Herrerahttps://www.2ndQuadr
pavan95 wrote:
> Hi Álvaro Herrera,
>
> Please find the corresponding output:
OK, these settings look pretty normal, so they don't explain your
problem.
What is checkpoint_segments set to? And checkpoint_timeout?
--
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Develop
彭昱傑 wrote:
> My postgre version is 9.4.9, and I face a space issue.
Latest in 9.4 is 9.4.17, so you're missing about two years of bug fixes.
> Every time I restart postgre server, it generates a new history file:
That's strange -- it shouldn't happen ... sounds like you're causing a
crash each
On 2022-Apr-14, Benjamin Tingle wrote:
> It doesn't help if I partition temp_data by textfield beforehand either
> (using the same scheme as the target table). It still opts to concatenate
> all of temp_data, hash it, then perform a sequential scan against the
> target partitions.
Does it still d
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.
--
Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/
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
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
I came here to talk about partitionwise join, but then noticed you have
already thought of that:
On 2023-Jun-18, nicolas paris wrote:
> Note that both plan acome from the same partitioned by hash table with
> 100 parts, with a unique index on the list_id + hash_key. For strategy
> 2.1, I turned o
On 2023-Sep-13, bruno da silva wrote:
> I just had an outage on postgres 14 due to multixact members limit exceeded.
Sadly, that's not as uncommon as we would like.
> So the documentation says "There is a separate storage area which holds the
> list of members in each multixact, which also uses
On 2023-Sep-14, bruno da silva wrote:
> This problem is more acute when the FK Table stores a small number of rows
> like types or codes.
Right, because the likelihood of multiple transactions creating
new references to the same row is higher.
> I think in those cases an enum type should be used
On 2024-Feb-02, James Pang (chaolpan) wrote:
> Possible to increase Subtrans SLRU buffer size ?
Not at present -- you need to recompile after changing
NUM_SUBTRANS_BUFFERS in src/include/access/subtrans.h,
NUM_MULTIXACTOFFSET_BUFFERS and NUM_MULTIXACTMEMBER_BUFFERS in
src/include/access/multix
Hi Chema,
On 2024-Feb-26, Chema wrote:
> Dear pgsqlers,
>
> I'm trying to optimize simple queries on two tables (tenders & items) with
> a couple million records. Besides the resulting records, the app also
> displays the count of total results. Doing count() takes as much time as
> the other
On 2024-Mar-01, James Pang wrote:
> one question:
> we need to increase all SLRU buffers together , MULTIXACT, XACT,
> Subtrans, COMMIT TS , for example, got all of them doubled based on
> existing size ?
No need.
> or only increase Subtrans , or Subtrans and multixact ?
Just increase the
On 2024-Sep-10, James Pang wrote:
> Hi experts,
> we have a Postgresql v14.8 database, almost thousands of backends hang
> on MultiXactOffsetSLRU at the same time, all of these sessions running same
> query "SELECT ", from OS and postgresql slow log, we found all of these
> query on "BIND"
On 2024-Sep-10, Amine Tengilimoglu wrote:
> Hi,
>
>I encountered this in a project we migrated to PostgreSQL
> before, and unfortunately, it’s a situation that completely degrades
> performance. We identified the cause as savepoints being used excessively
> and without control. O
On 2024-Sep-10, James Pang wrote:
> There is no foreign keys, but there is one session who did transactions
> to tables with savepoints, one savepoints/per sql in same transaction. But
> sessions with query "SELECT “ do not use savepoints , just with a lot of
> sessions running same query and
On 2018-Aug-16, Fred Habash wrote:
> One of our database API's is run concurrently by near 40 sessions. We see
> all of them waiting back and forth on this wait state.
What version are you running?
> Why is it called Subtrans Control Lock?
It controls access to the pg_subtrans structure, which
On 2018-Aug-17, Fred Habash wrote:
> Aurora Postgres 9.6.3
Oh, okay, I don't know this one. Did you contact Amazon support?
> So, no chance to recompile (AFAIK).
> Is there a design anti-pattern at the schema or data access level that we
> should look for and correct?
Maybe ...
> And as for t
On 2018-Aug-20, Fred Habash wrote:
> How do we go about calculating appropriate values for these two parameters ...
I don't know a lot about your system, so don't have anything to go on.
Also, Aurora is mostly unknown to me. What did Amazon say?
> > 'NUM_SUBTRANS_BUFFERS'?
> TOTAL_MAX_CACHED_SU
On 2018-Sep-25, Justin Pryzby wrote:
> I asked few weeks ago [0] but didn't get a response on -docs so resending here
> for wider review/discussion/.
I support the idea of adding a link to "Performance Optimization".
That's not a protected page, so you should be able to do it.
> [0]
> https://
On 2018-Nov-26, Jakub Glapa wrote:
> Justin thanks for the information!
> I'm running Ubuntu 16.04.
> I'll try to prepare for the next crash.
> Couldn't find anything this time.
As I recall, the appport stuff in Ubuntu is terrible ... I've seen it
take 40 minutes to write the crash dump to disk,
So, the slowness in this test seems to come from
add_child_rel_equivalences() and bms_overlap() therein, according to
perf (mine and Justin's) ... apparently we end up with a lot of
equivalence class members. I added a debugging block to spit out the
number of ECs as well as the number of members
On 2018-Dec-06, Amit Langote wrote:
Hi
> [ Parallel SeqScan on precio_126 to precio_998 ]
>
> > -> Parallel Seq Scan on precio_999 p_874
> > (cost=0.00..27.50 rows=1 width=16)
> >Filter: ((fecha >= '1990-05-06
> > 00:00:00'::timestamp without
On 2018-Dec-06, Amit Langote wrote:
> The partitionwise join related
> changes in PG 11 moved the add_child_rel_equivalences call in
> set_append_rel_size such that child EC members would be added even before
> checking if the child rel is dummy, but for a reason named in the comment
> above the c
On 2018-Dec-17, Tom Lane wrote:
> Queries like yours are kinda sorta counterexamples to that, but pretty
> much all the ones I've seen seem like crude hacks (and this one is not
> an exception). Writing a bunch of code to support them feels like
> solving the wrong problem. Admittedly, it's not
On 2019-Jan-17, Mariel Cherkassky wrote:
> I tried to set the same threshold for the toasted table but got an error
> that it is a catalog table and therefore permission is denied.
> 2019-01-17 12:04:15 EST db116109 ERROR: permission denied:
> "pg_toast_13388392" is a system catalog
> 2019-01-17
On 2019-Jan-17, Mariel Cherkassky wrote:
> I did it for the original table. But I see in the logs that the autovacuun
> on the toasted table isn't synced with the autovacuun of the original
> table. Therefore I thought that it worth to set it also for the toasted
> table. Can you explain why in th
On 2019-Jan-17, Mariel Cherkassky wrote:
> But you said that the threshold that is chosen for the toasted table is
> identical to the originals table threshold right ?
You can configure them identical, or different. Up to you.
> Is that a normal behavior that the original table has 1000recrods
On 2019-Jan-29, Shreeyansh Dba wrote:
> The virtualxid lock is special. It’s a exclusive lock on the transaction’s
> own virtual transaction ID that every transaction always holds. No other
> transaction can ever acquire it while the transaction is running.
> The purpose of this is to allow one tr
On 2019-Jan-30, Mariel Cherkassky wrote:
> It seems that the version of the db is 9.6.10 :
>
> psql -U db -d db -c "select version()";
> Password for user db:
> version
> ---
> PostgreSQL 9.6.1
On 2019-Feb-13, Mariel Cherkassky wrote:
> Hey,
> I have a very big toasted table in my db(9.2.5).
Six years of bugfixes missing there ... you need to think about an
update.
> Autovacuum doesnt gather
> statistics on it because the analyze_scale/threshold are default and as a
> result autoanalyz
On 2019-Feb-13, Mariel Cherkassky wrote:
> To be honest, it isnt my db, but I just have access to it ...
Well, I suggest you forget the password then :-)
> Either way, so I need to change the vacuum_Analyze_scale/threshold for the
> original table ? But the value will be too high/low for the ori
On 2019-Feb-14, Mariel Cherkassky wrote:
> I meant the anaylze, if anaylze will run very often on the original table,
> arent there disadvantages for it ?
It'll waste time and resources pointlessly. Don't do it -- it won't do
any good.
--
Álvaro Herrerahttps://www.2ndQuadrant.
On 2019-Apr-15, Gunther wrote:
> #0 AllocSetAlloc (context=0x1168230, size=385) at aset.c:715
> #1 0x0084e6cd in palloc (size=385) at mcxt.c:938
> #2 0x0061019c in ExecHashJoinGetSavedTuple
> (file=file@entry=0x8bbc528, hashvalue=hashvalue@entry=0x7fff2e4ca76c,
> tupleSlot=
On 2019-Apr-15, Tom Lane wrote:
> It's barely conceivable that in your particular query, there's something
> acting to break that which doesn't manifest typically; but I think it's
> much more likely that you simply haven't found the culprit allocation.
> It's quite feasible that many many ExecHas
On 2019-Jun-26, Hugh Ranalli wrote:
> From my research in preparing for the upgrade, I understood transparent
> huge pages were a good thing, and should be enabled. Is this not correct?
It is not.
> Wouldn't the plan be the same at both
> the start of the week (when the problematic table is esse
On 2019-Jun-26, Justin Pryzby wrote:
> > Also, Should pg_buffercache perhaps be run at the beginning and end of the
> > week, to see if there is a significant difference?
>
> Yes; buffercache can be pretty volatile, so I'd save it numerous times each at
> beginning and end of week.
Be careful wi
On 2019-Nov-14, Craig James wrote:
> I'm completely baffled by this problem: I'm doing a delete that joins three
> modest-sized tables, and it gets completely stuck: 100% CPU use forever.
Do you have any FKs there? If any delete is cascading, and you don't
have an index on the other side, it'd d
On 2020-Jan-13, Shira Bezalel wrote:
> Hi All,
>
> I'm testing an upgrade from Postgres 9.6.16 to 12.1 and seeing a
> significant performance gain in one specific query. This is really great,
> but I'm just looking to understand why.
pg12 reads half the number of buffers. I bet it's because of
On 2020-Feb-16, Lars Aksel Opsahl wrote:
> On a server with 32 cores and 250 GB memory, with CentOS 7 and kernel
> 4.4.214-1.el7.elrepo.x86_64, I try to run 30 parallel threads using
> dblink. (https://github.com/larsop/postgres_execute_parallel) . I have
> tried to disconnect and reconnect in the
On 2020-Feb-19, Lars Aksel Opsahl wrote:
> With the values above I did see same performance problems and we ended
> with a lot of subtransControlLock.
>
> So I started to change the code based on your feedbacks.
>
> - What seems to work very good in combination with a catch exception
> and retry
On 2020-Jun-20, Tom Lane wrote:
> I wrote:
> > ... oh, now I see: apparently, your filter condition is such that *no*
> > rows of the objectcustomfieldvalues table get past the filter:
> >
> > -> Index Scan using objectcustomfieldvalues3 on
> > objectcustomfieldvalues objectcustomf
On 2020-Sep-14, David Rowley wrote:
> On Tue, 8 Sep 2020 at 06:05, Raj wrote:
> >
> > > This would not exactly look like a bug, because the message says "to
> > > be locked", so at least it's not allowing two workers to lock the same
> > > tuple. But it seems that the skip-locked mode should not
On 2021-Apr-13, Andres Freund wrote:
> > Sounds like I should file this as a requested improvement?
>
> The ability to lock a toast table? Yea, it might be worth doing that. I
> seem to recall this being discussed not too long ago...
Yep, commit 59ab4ac32460 reverted by eeda7f633809. There were
Hello
On 2021-Apr-13, Andres Freund wrote:
> > The concerns that had come to my mind were more along the lines
> > of things like pg_dump requiring a larger footprint in the shared
> > lock table. We could alleviate that by increasing the default
> > value of max_locks_per_transaction, perhaps.
On 2021-Jul-08, Justin Pryzby wrote:
> If I'm not wrong, this is the same thing you asked 2 week ago.
>
> If so, why not continue the conversation on the same thread, and why not
> reference the old thread ?
>
> I went to the effort to find the old conversation.
> https://www.postgresql.org/mess
On 2021-Jul-22, l...@laurent-hasson.com wrote:
> Yes, agreed Peter... The "lower priority" issue was mentioned, but not
> in terms of the applicability of the fix overall. Personally, I would
> prefer going the size_t route vs int/long/int64 in C/C++/. Of course,
> as a user, I'd love a patch on V
49 matches
Mail list logo
