of this issue:
https://www.postgresql.org/message-id/flat/da3205c4-5b07-a65c-6c26-a293c6464fdb%40postgrespro.ru
I've heard of this happening when an aggressive VACUUM updates
relfrozenxid on a larger table.
--
Peter Geoghegan
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
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
y will need to upgrade to 17.
--
Peter Geoghegan
btree-index-scans
--
Peter Geoghegan
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
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
s Thomas said, later
versions do have parallel VACUUM, though that cannot be used by
autovacuum workers.
--
Peter Geoghegan
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
#x27;s that they have much more
*predictable* performance characteristics as conditions change.
--
Peter Geoghegan
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
;ll
see will be out of all proportion to what you're ever likely to see in
the real world.
--
Peter Geoghegan
model, which includes automation, but also some high level top-down
theory.
--
Peter Geoghegan
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
ts made by both Laurenze and Jeff. They may not be
irreconcilable, or anything like it.
--
Peter Geoghegan
r work_mem consumer on Windows. Of course this won't
matter much to users like Laurent.
--
Peter Geoghegan
r way, as far as addressing this long standing "int64 vs long"
issue goes.
--
Peter Geoghegan
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
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
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
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
input data
is clustered. I recall noticing that that could significantly alter
the behavior of HashAggs as of Postgres 13.
--
Peter Geoghegan
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
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
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
ot;buffers shared/read" numbers? That seems odd.
--
Peter Geoghegan
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.
ave fixed the problem.
That seems like the simplest and most likely explanation to me, even
though it isn't particularly simple.
--
Peter Geoghegan
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
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
happens to be contained in the statistics,
such as the most common values.
--
Peter Geoghegan
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
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
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
the total
number of distinct keys rather low? Not just at any given time, but
over time?
--
Peter Geoghegan
slow
> query, though.
What columns are indexed by
index_unproc_notifications_on_notifiable_type, and what are their
datatypes?
--
Peter Geoghegan
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
problem is described in high level
terms from a user's perspective here:
https://brandur.org/postgres-queues
--
Peter Geoghegan
ly grow as the workload needs to scale up?
--
Peter Geoghegan
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
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
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
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
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
44 matches
Mail list logo
