lations for
global indexes, though maybe global indexes could focus our attention
on that problem.
--
Peter Geoghegan
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
make a good case for
asynchronously cleaning up the dead entries that point to a dropped
partition (probably within VACUUM). Perhaps we should offer *both* as
options.
--
Peter Geoghegan
bare minimum
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
could make
that happen. Maybe this same representation could be used for all
nbtree indexes, not just global nbtree indexes.
--
Peter Geoghegan
as hard as it sounds. You don't have to be Claude
Shannon to realize that it's kind of silly to reserve 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
anything now, 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
an "immediate" mode shutdown is appropriate, then. That will
mean that the primary will need to go through crash recovery if and
when you bring it back up, though.
--
Peter Geoghegan
which reports 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
ey're both causes of index bloat that
VACUUM cannot usually prevent.
--
Peter Geoghegan
nce.
It's 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
e?
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
Thanks
--
Peter Geoghegan
7;t necessarily 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
ing is something that theoretically can be avoided by the
implementation (if there were deletes involved then that wouldn't
apply, but there aren't).
--
Peter Geoghegan
1)
Index 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
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.
>
s
> and the total size of the WAL. Instead, WAL numbers have significantly
> degraded.
>
> What am I doing wrong?
That does seem weird.
CC'ing the authors of the relevant VACUUM enhancements.
--
Peter Geoghegan
On Mon, Sep 2, 2024 at 4:58 PM Peter Geoghegan wrote:
> On Mon, Sep 2, 2024 at 4:35 PM Pavel Luzanov wrote:
> > If it helps, without creating index on id column, the numbers will be
> > much closer:
>
> Yes, avoiding all index vacuuming seems useful. It makes the test cas
ed to WAL, buffers, and
CPU time that changed.
Perhaps I'm not thinking of something obvious. Maybe it's extra
VISIBILITY records? But I'd expect the number of VISIBILITY records to
match the number of pages frozen, given these particulars. VACUUM
VERBOSE at least shows that that hasn't changed.
--
Peter Geoghegan
inated in
pages that only contained existing LP_UNUSED items when scanned by
VACUUM?
--
Peter Geoghegan
ally cause an increase in the number of WAL records written?
I'd have thought that that was simply impossible.
--
Peter Geoghegan
're writing extra FPIs to set hint
bits. But that explanation only works if you assume that page-level
checksums are in use (or that wal_log_hints is turned on).
--
Peter Geoghegan
ave 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
ly rare in
practice.
--
Peter Geoghegan
dexes won't grow at all due to
garbage index tuples, especially on Postgres 14.
--
Peter Geoghegan
ll block
write DML. You might prefer to just use the first query if this is
running in a production environment.
--
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)
ing alone, the number of vacuum operations that do
unnecessary index vacuuming doesn't increase at all (except perhaps
due to rounding effects).
--
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
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
e:
https://www.postgresql.org/message-id/flat/CAL9smLC%3DSxYiN7yZ4HDyk0RnZyXoP2vaHD-Vg1JskOEHyhMXug%40mail.gmail.com#e79eca5922789de828314e296fdcb82d
--
Peter Geoghegan
do just pass a string some other 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
CONFLICT. And
because the only alternative 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
hen how can you expect it for yourself?
--
Peter Geoghegan
communicated 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
g because of how they 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
mportant to be
*exactly* compatible with EBCDIC order? As long as you're paying for a
custom collation, why not just use a collation that is helpful to
humans?
--
Peter Geoghegan
ar, varchar and text data types with UDFs
>> that behave as Tom mentioned.
>>
>> From: Peter Geoghegan [mailto:p...@bowt.ie]
>> > That said, the idea of an "EBCDIC collation" seems limiting. Why
>> > should a system like DB2 for the mainframe (that hap
st major Linux
distributions. See:
https://github.com/petergeoghegan/amcheck
Note also that only this external version has the "heapallindexed" check.
--
Peter Geoghegan
provider without
accounting for pg_upgrade. If so, then that's a bug in the package. This is
a total speculation, but makes a certain amount of sense to me.
--
Peter Geoghegan
(Sent from my phone)
Please report this as a bug to the freebsd package maintainer.
--
Peter Geoghegan
(Sent from my phone)
avioral change in
OS collations, even though the OS collation behavior apparently did
not change.
I'm currently feeling too lazy to check that I guessed right about all
of this, but somebody should look into it.
--
Peter Geoghegan
Look into amcheck:
https://github.com/petergeoghegan/amcheck
--
Peter Geoghegan
(Sent from my phone)
pass "pg_index.indisprimary" as
"heapallindexed" argument, while generalizing from the example SQL
query for bt_index_check()). This process has a good chance of
isolating the problem, especially if you let this list see any errors
raised by the tool.
--
Peter Geoghegan
ot;indisprimary" as the heapallindexed argument. That way, only primary
keys would be verified against the heap, which is potentially a lot
faster.
--
Peter Geoghegan
On Sun, Dec 31, 2017 at 1:39 PM, Peter Geoghegan wrote:
> SELECT bt_index_check(index => c.oid, heapallindexed => true),
> c.relname,
> c.relpages
> FROM pg_index i
> JOIN pg_opclass op ON i.indclass[0] = op.oid
> JOIN pg_am am ON op.opc
org/gitweb/?p=postgresql.git;a=commit;h=a4ccc1cef5a04cc054af83bc4582a045d5232cb3
My guess is that that would make a noticeable difference, once v11
becomes available. Could you test this yourself by building from the
master branch?
--
Peter Geoghegan
ck on 9.4:
https://github.com/petergeoghegan/amcheck#redhatcentossles
I would start there.
--
Peter Geoghegan
sistently:
https://www.youtube.com/watch?v=p5RaATILoiE&t=2079s
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f21668f3
Of course, this is only a guess. I vaguely recall a complaint that
sounded vaguely like yours, also involving partitioning.
--
Peter Geoghegan
still 15.7) on Ubuntu 22.04 LTS.
That shouldn't matter, as far as this theory of mine is concerned.
Anything after 12 could be affected by the issue I'm thinking of.
--
Peter Geoghegan
ind by fillfactor in each case. In
general page splits tend to come in distinct "waves" after CREATE
INDEX is run.
--
Peter Geoghegan
tually make any promises about not getting unique
violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING)
make such a promise. That's the main reason why Postgres supports
both.
--
Peter Geoghegan
NFLICT DO UPDATE.
--
Peter Geoghegan
es with a generic table name
alias?
--
Peter Geoghegan
e noticed the effects of the problem.
That sounds plausible.
--
Peter Geoghegan
101 - 155 of 155 matches
Mail list logo
