On 08/07/2019 02:26, Peter Geoghegan wrote:
> Please don't top post -- trim the your response down so that only
> still-relevant text remains.
>
> On Tue, Jun 11, 2019 at 1:27 PM Ancoron Luciferis
> wrote:
>> Primary key indexes after an ANALYZE:
>> table_name | bloat | index_mb
Please don't top post -- trim the your response down so that only
still-relevant text remains.
On Tue, Jun 11, 2019 at 1:27 PM Ancoron Luciferis
wrote:
> Primary key indexes after an ANALYZE:
> table_name | bloat | index_mb | table_mb
> ---++--
Hi,
I've finally found some time to redo some tests using PostgreSQL 11.3.
Scenario is the following:
1.) add 10 M rows
2.) add another 30 M rows
3.) delete the first 10 M rows
4.) VACUUM
5.) REINDEX
My goal is to find the most efficient way for UUID values to somewhat
optimize for index page/n
On Sun, May 26, 2019 at 8:37 PM Tomas Vondra
wrote:
No, an extra column is not a solution, because it has no impact on the
> index on the UUID column.
Possibly talking at cross-purposes here. I was honing in on the OPs wish to
search and sort by creation order. For which my first (and only) ins
Here's what I was thinking of regarding disk space:
https://www.postgresql.org/docs/11/storage-page-layout.html
That's the kind of low-level detail I try *not* to worry about, but like to
have in the back of my mind at least a little. If I read it correctly, a
fixed-length field is going to be ma
On Sun, May 26, 2019 at 02:27:05PM +1000, Morris de Oryx wrote:
I'm not worthy to post here, but a bit of a random thought.
If I've followed the conversation correctly, the reason for a V1 UUID is
partly to order and partition rows by a timestamp value, but without the
cost of a timestamp column
On Sun, May 26, 2019 at 7:38 PM Ancoron Luciferis <
ancoron.lucife...@googlemail.com> wrote:
The BRIN index is something I might need to test, though.
>
Yes, check that out, it might give you some ideas. A B-tree (in whatever
variant) is *inherently *a large index type. They're ideal for finding
On 26/05/2019 06:27, Morris de Oryx wrote:
> I'm not worthy to post here, but a bit of a random thought.
>
> If I've followed the conversation correctly, the reason for a V1 UUID is
> partly to order and partition rows by a timestamp value, but without the
> cost of a timestamp column. As I was to
On 26/05/2019 03:09, Tomas Vondra wrote:
> On Sun, May 26, 2019 at 01:49:30AM +0200, Ancoron Luciferis wrote:
>> On 26/05/2019 00:14, Tomas Vondra wrote:
>>> On Sat, May 25, 2019 at 05:54:15PM -0400, Tom Lane wrote:
Ancoron Luciferis writes:
> On 25/05/2019 16:57, Tom Lane wrote:
>> (
I'm not worthy to post here, but a bit of a random thought.
If I've followed the conversation correctly, the reason for a V1 UUID is
partly to order and partition rows by a timestamp value, but without the
cost of a timestamp column. As I was told as a boy, "Smart numbers aren't."
Is it _absolutel
On Sun, May 26, 2019 at 01:49:30AM +0200, Ancoron Luciferis wrote:
On 26/05/2019 00:14, Tomas Vondra wrote:
On Sat, May 25, 2019 at 05:54:15PM -0400, Tom Lane wrote:
Ancoron Luciferis writes:
On 25/05/2019 16:57, Tom Lane wrote:
(4) it in fact *wouldn't* do anything useful, because we'd stil
On 26/05/2019 00:14, Tomas Vondra wrote:
> On Sat, May 25, 2019 at 05:54:15PM -0400, Tom Lane wrote:
>> Ancoron Luciferis writes:
>>> On 25/05/2019 16:57, Tom Lane wrote:
(4) it in fact *wouldn't* do anything useful, because we'd still have
to sort UUIDs in the same order as today, meani
On Sat, May 25, 2019 at 06:38:08PM -0400, Tom Lane wrote:
Tomas Vondra writes:
On Sat, May 25, 2019 at 05:54:15PM -0400, Tom Lane wrote:
What you might want to think about is creating a function that maps
UUIDs into an ordering that makes sense to you, and then creating
a unique index over tha
On 25/05/2019 23:54, Tom Lane wrote:
> Ancoron Luciferis writes:
>> On 25/05/2019 16:57, Tom Lane wrote:
>>> (4) it in fact *wouldn't* do anything useful, because we'd still have
>>> to sort UUIDs in the same order as today, meaning that btree index behavior
>>> would remain the same as before. P
Tomas Vondra writes:
> On Sat, May 25, 2019 at 05:54:15PM -0400, Tom Lane wrote:
>> What you might want to think about is creating a function that maps
>> UUIDs into an ordering that makes sense to you, and then creating
>> a unique index over that function instead of the raw UUIDs. That
>> would
On Sat, May 25, 2019 at 05:54:15PM -0400, Tom Lane wrote:
Ancoron Luciferis writes:
On 25/05/2019 16:57, Tom Lane wrote:
(4) it in fact *wouldn't* do anything useful, because we'd still have
to sort UUIDs in the same order as today, meaning that btree index behavior
would remain the same as be
Ancoron Luciferis writes:
> On 25/05/2019 16:57, Tom Lane wrote:
>> (4) it in fact *wouldn't* do anything useful, because we'd still have
>> to sort UUIDs in the same order as today, meaning that btree index behavior
>> would remain the same as before. Plus UUID comparison would get a lot
>> more
On 25/05/2019 21:00, Vitalii Tymchyshyn wrote:
> I am not sure why do you want to change on-disk storage format? If we
> are talking about indexes, it's more about comparison function (opclass)
> that is used in an index.
> Am I wrong?
I don't "want" to change the on-disk format of the v1 UUID's
On 25/05/2019 21:00, Vitalii Tymchyshyn wrote:
> I am not sure why do you want to change on-disk storage format? If we
> are talking about indexes, it's more about comparison function (opclass)
> that is used in an index.
> Am I wrong?
I don't "want" to change the on-disk format of the v1 UUID's
I am not sure why do you want to change on-disk storage format? If we are
talking about indexes, it's more about comparison function (opclass) that
is used in an index.
Am I wrong?
сб, 25 трав. 2019 о 11:21 Ancoron Luciferis <
ancoron.lucife...@googlemail.com> пише:
> On 25/05/2019 16:57, Tom Lan
On 25/05/2019 16:57, Tom Lane wrote:
> Ancoron Luciferis writes:
>> So I investigated the PostgreSQL code to see how it is handling UUID's
>> with respect to storage, sorting, aso. but all I could find was that it
>> basically falls back to the 16-byte.
>
> Yup, they're just blobs to us.
>
>> Af
On 25/05/2019 16:19, Peter Eisentraut wrote:
> On 2019-05-25 15:45, Ancoron Luciferis wrote:
>> So, my question now is: Would it make sense for you to handle these
>> time-based UUID's differently internally? Specifically un-shuffling the
>> timestamp before they are going to storage?
>
> It seems
Ancoron Luciferis writes:
> So I investigated the PostgreSQL code to see how it is handling UUID's
> with respect to storage, sorting, aso. but all I could find was that it
> basically falls back to the 16-byte.
Yup, they're just blobs to us.
> After struggling to find a way to optimize things i
On 2019-05-25 15:45, Ancoron Luciferis wrote:
> So, my question now is: Would it make sense for you to handle these
> time-based UUID's differently internally? Specifically un-shuffling the
> timestamp before they are going to storage?
It seems unlikely that we would do that, because that would br
Hi all,
Some time ago, I was having trouble with some rather high load OLTP
application (in Java, but that doesn't really matter) that was using v1
UUID's for primary keys and after some time, the bloat of certain
indexes went quite high.
So I investigated the PostgreSQL code to see how it is han
25 matches
Mail list logo
