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
24 matches
Mail list logo
