On Thu, Sep 7, 2023 at 3:48 AM David Rowley wrote:
> On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote:
> > It seems likely that the problem here is that some of the predicates
> > appear as so-called "Filter:" conditions, as opposed to true index
> > quals.
>
x Cond: (a = 1)
Filter: (b <> 42)
Rows Removed by Filter: 1
Heap Fetches: 0
Buffers: shared hit=11
Planning Time: 0.076 ms
Execution Time: 3.204 ms
(8 rows)
(There are lots of index tuples matching (a, b) = "(1, NULL)" here, you see.)
--
Peter Geoghegan
by the
implementation (if there were deletes involved then that wouldn't
apply, but there aren't).
--
Peter Geoghegan
going to
stop autovacuum/autoanalyze from doing pending list cleanup.
The ANALYZE pending list cleanup path has some problems:
https://postgr.es/m/cah2-wzkjrk556envtflmyxedw91xguwiyzvep2kp5yqt_-3...@mail.gmail.com
--
Peter Geoghegan
is void of the aforementioned limitation.
> The downside is it is rather verbose.
The MERGE command has various race conditions that are particularly
relevant to UPSERT type use cases. See the wiki page you referenced
for a huge amount of information on this.
> *Feature Request*
> Given
have frozen that
one tuple and then set the page all-visible. The page would likely be
frozen again by the next aggressive VACUUM, which is usually much more
expensive.
--
Peter Geoghegan
). And if you were on 14+, things in
this area would be much better still.
--
Peter Geoghegan
n amcheck's bt_index_check() routine against some of the
indexes you've shown? There is perhaps some chance that index
corruption exists and causes VACUUM to take a very long time to delete
index pages. This is pretty much a wild guess, though.
--
Peter Geoghegan
n access method like GIN. Do you have
any non-btree indexes on the table? Can you show us the details of the
table, including all of its indexes? In other words, can you show "\d
applications" output from psql?
--
Peter Geoghegan
face for
running amcheck routine, so maybe look into that once you upgrade.
--
Peter Geoghegan
hing I
> should check before upgrading the remaining 13.8 DB to 13.9?
I recommend running amcheck on all indexes, or at least all
possibly-affected text indexes.
--
Peter Geoghegan
ng of each script
can be changed this way. There is also something called merged
tailorings.
The OP should see the Postgres ICU docs for hints on how to use these
facilities to make a custom collation that matches whatever their
requirements are:
https://www.postgresql.org/docs/current/collation.ht
ved example.
FWIW I think that it could be a lot less bad, even with indexes that
you'd think would be almost as bad as the bad one from your test case.
Even things that appear to be random aren't usually nearly as random
as what you've shown.
--
Peter Geoghegan
random and uniformly distributed clustering of index tuples, which is
what the index built via hashing exhibits. Even a v4 UUID index could
easily have plenty of duplicates, which would probably do
significantly better on the metric you've focussed on.
--
Peter Geoghegan
I think it means "chosen as
> arbiter index", but maybe I misunderstand.)
Unique index/constraint inference is the process by which we choose an
arbiter index. See the second paragraph of the "ON CONFLICT Clause"
section of the INSERT docs.
--
Peter Geoghegan
of
nbtree index scans with MVCC snapshots -- but *not* with index-only
scans. See "Making concurrent TID recycling safe" in the nbtree
README. I only got around to documenting all of the details here quite
recently. The index-only scan thing dates back to 9.5.
--
Peter Geoghegan
On Tue, Oct 11, 2022 at 10:04 PM Tom Lane wrote:
> Do we propagate visibility-map bits to standbys?
Yes.
--
Peter Geoghegan
be
set to false on the standby.
--
Peter Geoghegan
r* workload, where huge differences in the rate of
MultiXact consumption among tables is likely the norm. This still
isn't perfect (far from it), but it has the potential to make things
far better here.
--
Peter Geoghegan
ion is in dire need of an
overhaul. :-(
--
Peter Geoghegan
ot really true (apart from VACUUM FULL, which
really is quite different). The difference between aggressive and
non-aggressive can be big in practice due to an accumulation of
unfrozen pages over multiple non-aggressive vacuums.
--
Peter Geoghegan
ge).
See my recent response to a similar question here:
https://postgr.es/m/CAH2-WzkFQ-okvVXizpy4dCEVq75N-Qykh=crhzao-eajflv...@mail.gmail.com
--
Peter Geoghegan
On Mon, Apr 18, 2022 at 11:37 PM bhargav kamineni wrote:
> executing the vacuum on the entire cluster is also giving the same HINTS and
> WARNING's
You're using Aurora, not PostgreSQL. Perhaps this is actually a bug,
but there is no way for anybody here to know.
--
Peter Geoghegan
allow an
organization to deploy a program in a production environment, complete
with version control? Does it have something to do with decoupling the
mutable business data stored in tables from the programs contained/run
in the same database?
--
Peter Geoghegan
d with him online, and
I've worked on Postgres more or less full time for a full decade now.
As far as I'm aware he hasn't ever publicly posting to any of the
mailing lists.
--
Peter Geoghegan
how can you expect it for yourself?
--
Peter Geoghegan
e interpretation is that Alex intends to
update those columns using their current values (not new values),
which won't really change anything -- that seems unlikely to have been
the intent.
--
Peter Geoghegan
er way), but it's
definitely considered the way to go.
Note that you can do stuff like this from SQL:
pg@regression:5432 [2999218]=# select 'pg_class'::regclass::oid;
oid
───
1,259
(1 row)
--
Peter Geoghegan
e:
https://www.postgresql.org/message-id/flat/CAL9smLC%3DSxYiN7yZ4HDyk0RnZyXoP2vaHD-Vg1JskOEHyhMXug%40mail.gmail.com#e79eca5922789de828314e296fdcb82d
--
Peter Geoghegan
On Mon, Sep 6, 2021 at 7:52 AM Daniel Westermann (DWE)
wrote:
> >Try running vacuum with index cleanup = on.
>
> Thank you, Peter
Thanks for testing!
--
Peter Geoghegan
eds to do work that is proportional to
the number of unset-in-vm heap pages.
I believe that that trade-off makes a lot of sense. Autovacuum has
little chance of keeping anything like 100% of all pages set in the VM
anyway. But it can get a lot closer to it in some cases now.
--
Peter Geoghegan
ssary index vacuuming doesn't increase at all (except perhaps
due to rounding effects).
--
Peter Geoghegan
It's a non-hot update, and so there is a single dead index tuple. You're
seeing the new optimization that makes vacuum skip indexes in marginal
cases.
Try running vacuum with index cleanup = on.
Peter Geoghegan
(Sent from my phone)
just use the first query if this is
running in a production environment.
--
Peter Geoghegan
pecially on Postgres 14.
--
Peter Geoghegan
practice.
--
Peter Geoghegan
e lots of duplicates, which did
perform rather badly prior to Postgres 12. I bet that you'd benefit
from upgrading to Postgres 12, or especially to Postgres 13. The
B-Tree space management is a lot better now. (Actually, it'll be
better again in Postgres 14.)
--
Peter Geoghegan
On Thu, Jan 21, 2021 at 12:55 PM Tommy Li wrote:
> Is there any way to configure autovacuum to log the same information as
> VACUUM VERBOSE?
No. Though there really should be.
--
Peter Geoghegan
pdates. That will make the table larger initially, but leaving enough
space behind on the same heap pages for successor tuples makes it
possible to use HOT updates.
--
Peter Geoghegan
hard to
test. And it might defeat certain future optimizations based on heap
TID being the only tiebreaker. Having two types of equality might have
to bleed into the optimizer.
It's a question of engineering trade-offs. I don't think that it's worth it.
--
Peter Geoghegan
ugly, but I can see no
better way. A three-way posting list split (i.e. the other design that
you sketched) is a special case that is very hard to test, very
complicated, and of little value in the grand scheme of things.
--
Peter Geoghegan
y -- the original, the new, and a
second copy of the original. That's much more complicated.
--
Peter Geoghegan
On Tue, Aug 18, 2020 at 9:44 AM Peter Geoghegan wrote:
> If we wanted to fix this for numeric, we'd have to invent a new
> numeric datatype (called numeric2, say). That probably isn't as hard
> as it sounds, since it could be part of the same B-Tree operator
> family as numeric. I
eric, we'd have to invent a new
numeric datatype (called numeric2, say). That probably isn't as hard
as it sounds, since it could be part of the same B-Tree operator
family as numeric. It could also be implicitly cast to numeric.
--
Peter Geoghegan
h to dumping out a single page image
easier, since it doesn't involve relfilenodes or filesystem files:
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#contrib.2Fpageinspect_page_dump
--
Peter Geoghegan
rites didn't restored the page to the state before the
> index-item deletion happened(that is, if full_page_writes were set to
> off.). (If it found to be the cause, I'm not sure why that didn't
> happen on 9.5.)
There is also a Heap/HOT_UPDATE log line with similar errors.
--
Peter Geoghegan
segments that become ready to be archived during crash recovery
were potentially recycled without being archived."
Sorry that you were affected by this bug -- it really sucks.
--
Peter Geoghegan
sses in a free of any other load system.
> It is postgres 11.7
Try increasing maintenance_work_mem from the default of 64MB. MWM
constrains the number of parallel workers used.
--
Peter Geoghegan
il
you REINDEX. This includes cases where you're running pg_upgrade
against a restored physical backup.
--
Peter Geoghegan
ion pageinspect;
CREATE EXTENSION
regression=# select version from bt_metap('pg_aggregate_fnoid_index');
version
-
4
(1 row)
--
Peter Geoghegan
U
collations, but it currently isn't possible to use ICU as the default
collation provider. You really have to go out of your way to use ICU
collations.
--
Peter Geoghegan
my_index', true);
If that doesn't show any errors, then perhaps try this:
SELECT bt_index_parent_check('my_index', true);
If you're on Postgres 10, then you should leave out the second
argument, "true", since that version doesn't have the extra
heapallindexed check.
Let us know what you see.
--
Peter Geoghegan
odified to verify all B-Tree indexes (not
just those indexes in the pg_catalog schema):
https://www.postgresql.org/docs/10/amcheck.html
--
Peter Geoghegan
sort code received many improvements over the years, really
starting in 9.5, and continuing in 9.6, 10 and 11. FWIW, I think that
that was probably the biggest factor here. Though parallel CREATE
INDEX will have helped as well.
--
Peter Geoghegan
a tuple *after*
TOAST compression has been applied.
--
Peter Geoghegan
earlier than pg93. So thank you for
> your hard work and dedication to this awesome piece of software.
How long did it take on 9.3?
I am the author of the parallel CREATE INDEX feature. It's good to get
feedback like this.
--
Peter Geoghegan
happened with any regularity, somebody would have
complained by now.
As Tom said, it's hard to give a useful answer without more context --
how you define "immediate"?
--
Peter Geoghegan
30GB of disk
space on top of the dump file itself.
--
Peter Geoghegan
orrupt B-Tree indexes (not Geometry):
https://trac.osgeo.org/postgis/ticket/3841
Though I also see what could be a comparable bug in Geometry:
https://trac.osgeo.org/postgis/ticket/3777
These bugs are from about 3 years ago. If I'm right you should be able
to isolate the bug using amcheck.
--
On Wed, Jul 17, 2019 at 10:27 AM Peter Geoghegan wrote:
> > It's possible that amcheck would have given you an accurate diagnosis
> > of the problem -- especially if you used bt_index_parent_check():
> >
> > https://www.postgresql.org/docs/current/amcheck.html
pression (including VACUUM),
provided that all the problems only affect the table with the broken
index. OTOH, taking down the entire Postgres cluster as an indirect
consequence of one person's ill-considered CREATE INDEX really sucks.
That distinction seems important to me.
--
Peter Geoghegan
ere is a "failed to re-find parent key" or similar condition.
Perhaps it would make more sense to make the index inactive (for some
value of "inactive") instead of just complaining. That might be the
least worst option, all things considered.
--
Peter Geoghegan
On Wed, Jul 17, 2019 at 9:21 AM Peter Geoghegan wrote:
> It's possible that amcheck would have given you an accurate diagnosis
> of the problem -- especially if you used bt_index_parent_check():
>
> https://www.postgresql.org/docs/current/amcheck.html
BTW, be sure to use the 'he
) ultimately risks the availability of every
database in the cluster. Many installations receive little to no
supervision, so it may just be a matter of time there. That is
certainly a bad thing.
--
Peter Geoghegan
.postgresql.org/docs/current/amcheck.html
--
Peter Geoghegan
ems worth comparing index size in detail.
--
Peter Geoghegan
On Tue, Jul 9, 2019 at 9:04 PM Peter Geoghegan wrote:
> ISTM that the simplest explanation here is that index fragmentation
> (and even index size) is a red herring, and the real issue is that
> you're suffering from problems similar to those that are described in
> these old thread
he heap/table sort order
among duplicates by treating heap TID as a tiebreaker column, which
may make REINDEXing totally unnecessary for you. It's harder to model
this issue because the problem with heap TID order will only be seen
when there is at least a moderate amount of churn.
--
Peter Geoghegan
te there being no real change in the logical contents
of the index. Even then, the table will itself lose much of its
original order, so the index will become "unclustered" as it becomes
fragmented.
--
Peter Geoghegan
tion" isn't usually much of a problem when using Postgres.
--
Peter Geoghegan
fter a REINDEX, then why bother at all? There is no reason
to think that that will be more effective than simple vacuuming.
--
Peter Geoghegan
On Mon, Jul 8, 2019 at 12:19 PM Peter Geoghegan wrote:
> Well, you're still running autovacuum very aggressively here. It'll
> easily keep up when run on a relatively small table such as this.
Also, an exactly equal number of insertions and deletions is rather
likely to result in bloated i
of 9.4 -- not
9.4.6. You're missing years of bug fixes by sticking to such an old
point release, including some rather nasty ones -- 9.4.23 is the
current 9.4 point release. Actually, 9.4 is going to lose support this
year, as the oldest stable version that's currently supported by the
community.
--
Peter Geoghegan
ut it sounds as though maybe it could be relevant to
> this kind of workload - Is that so?
You seem to be worried about keeping indexes as small as possible.
vacuum_cleanup_index_scale_factor won't help with that.
--
Peter Geoghegan
e?
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
Thanks
--
Peter Geoghegan
easy to show problems with very low cardinality indexes in the
old code. You'll definitely notice a difference there.
> Is there a pdf or text version?
Just the talk slides:
https://www.pgcon.org/2019/schedule/attachments/518_nbtree-arch-pgcon.pdf
--
Peter Geoghegan
ey're both causes of index bloat that
VACUUM cannot usually prevent.
--
Peter Geoghegan
rts a avg_leaf_density for the index. Though I agree that
that's not likely to help matters. Apart from anything else, the
steady state of an index is embodied by more than just its
avg_leaf_density. Especially following the v12 enhancements to B-Tree
indexes.
--
Peter Geoghegan
ow, but I think that it's possible
that the page split thing will very effective. Perhaps even so
effective that it won't make much sense to vacuum global indexes just
because there is a pending dropped partition.
--
Peter Geoghegan
ve 16 bits for the
offset number component of a TID/ItemPointer. We need to continue to
support offset numbers that go that high, but the implementation would
optimize for the common case where offset numbers are less than 512
(or maybe less than 1024).
--
Peter Geoghegan
t happen. Maybe this same representation could be used for all
nbtree indexes, not just global nbtree indexes.
--
Peter Geoghegan
nimum
amount of space is used for partition numbers. Maybe it won't matter
that much if partition numbers cannot be recycled due to this
asynchronous processing.
--
Peter Geoghegan
t. I'd be particularly concerned
about the complexity of VACUUM there, whereas that doesn't seem all
that bad in the case of global indexes.
--
Peter Geoghegan
s, though maybe global indexes could focus our attention
on that problem.
--
Peter Geoghegan
for the main entry tree to have duplicates without at
least storing them in a posting list.
--
Peter Geoghegan
> really tracks which database objects were last rebuilt/validated with
> a given version.
Yes, that does seem like a big remaining weakness.
--
Peter Geoghegan
anually, but what would it look like for en-x-icu?
It is safe to call pg_import_system_collations() directly, which is
all that initdb does. This is documented, so you wouldn't be relying
on a hack.
--
Peter Geoghegan
ilable.
See:
https://github.com/petergeoghegan/amcheck
--
Peter Geoghegan
built with ICU support for many years -- they
modify the source code minimally to make this work. It may well still
be impossible to use "az-x-icu" on a FreeBSD installation of 9.6,
though.
--
Peter Geoghegan
l respect it.
In all sincerity, if you're compelled to walk away from participating
in mailing list discussions on a point of principle, then I wish you
well. That is your right.
--
Peter Geoghegan
said something
> somewhere that some other tweet disagreed with on faceplant"?
>
> Great plan if you do for-pay postgresql support for the living.
You can make your own conclusions about my motivations, just as I'll
make my own conclusions about yours. I'm not going to engage with you
on either, though.
--
Peter Geoghegan
e status quo, or a platitude about inclusivity.
--
Peter Geoghegan
nvalidations for coherency.
Fair enough. How about the attached revision?
--
Peter Geoghegan
v2-0001-Add-table-relcache-invalidation-to-index-builds.patch
Description: Binary data
der if it wouldn't be more appropriately placed closer to the
> UpdateIndexRelation(), given that that's essentially what necessitates
> the relcache flush?
That makes sense. I'll do it that way.
--
Peter Geoghegan
ateHeapTuple()).
Since nobody seems to be that excited about the
CacheInvalidateHeapTuple() idea, I haven't pursued it.
--
Peter Geoghegan
0001-Add-table-relcache-invalidation-to-index-builds.patch
Description: Binary data
uietly and painlessly.
> The truth is absolute and cannot be changed.
> Perception is not the truth.
> Flerp!
I cannot imagine what reaction you were expecting to this. In all
sincerity, I suggest reflecting on your words. You don't seem to have
realistic expectations about how the comm
istracted by
project work. I accept responsibility for the open item, though.
--
Peter Geoghegan
ing I could use an index, the
> PK for instance, and see if it references these pages.
Ir'a probably not serious, but you may want to try amcheck's
heapallindexed check. You'll have to use the non-contrib packages for
that right now, though, but those are available from the PGDG repos.
--
Peter Geoghegan
Is there a way to get this to work right ?
If you're using v10 with ICU, then you can create a custom ICU
collation for this, with "natural" sort order. Something like this
should work:
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
See the docs -- "23.2.2.3.2. ICU collations".
--
Peter Geoghegan
some risks that it'd cause issues. Will you tackle this?
Okay.
--
Peter Geoghegan
1 - 100 of 132 matches
Mail list logo
