I have three databases, two of databases where I am experiencing the issue
below.
The first database was created from a dump in Feb 2022 (a few weeks after
the time period for which I seem to have problematic indexes, maybe).
The second database was then cloned from the first (ie filesystem level
Here's an interesting read on regex improvements in dot net 7
See "Goodbye, Boyer-Moore" where they drop Boyer-Moore and replace it with
vectorized search:
https://devblogs.microsoft.com/dotnet/regular-expression-improvements-in-dotnet-7/#vectorization
Vladimir
--
Vladimir
On Thu, 9 Feb 2023 at 14:49, Tom Lane wrote:
>
> David Rowley writes:
> > Tom's argument seems to think it's impossible, so if you find that
> > it's definitely not impossible, then you can assume he's wrong about
> > that.
>
> My point was that it seems like you'd need a separate BMH engine for
David Rowley writes:
> On Thu, 9 Feb 2023 at 13:05, Martin L. Buchanan
> wrote:
>> str LIKE '%foo%'
>> str ILIKE '%foo%'
>> position('foo' in str) > 0
>> Is Boyer-Moore string searching now used by any of these three?
> We use a sort of "lossy" Boyer-Moore-Horspool algorithm. See
> text_positio
On Thu, 9 Feb 2023 at 13:05, Martin L. Buchanan
wrote:
> For the common and simple cases of find this string anywhere in another
> string:
>
> str LIKE '%foo%'
>
> str ILIKE '%foo%'
>
> position('foo' in str) > 0
>
> Is Boyer-Moore string searching now used by any of these three?
We use a sort
In the PostgreSQL Todo wiki, Boyer-Moore string searching for LIKE is
mentioned as an outstanding item.
For the common and simple cases of find this string anywhere in another
string:
str LIKE '%foo%'
str ILIKE '%foo%'
position('foo' in str) > 0
Is Boyer-Moore string searching now used by any
Dear Laurenz,
Thank you for your help. As you suspected, I was seeking a way to view the
execution plan of a function defined in SQL. Your suggestion was exactly
what I needed and has been very helpful.
Additionally, I also appreciated learning about the debugging techniques
for PL/pgSQL. Thank y
Andrus writes:
> This file size is only 6.2 GB. If custom format is used, pg_dump creates
> large file without problems. There are no file size limits. Error
> message is not about this.
Are you certain that the pg_dump you're using is v15, and not something
pre-v14? We got rid of the 4GB limi
Dear David,
Thank you for your prompt reply.
This is exactly what I was looking for. Thank you for your help.
Best regards,
Katsuya Okizaki
2023年2月8日(水) 8:51 David G. Johnston :
> On Tue, Feb 7, 2023 at 4:49 PM Katsuya Okizaki
> wrote:
>
>> In a normal SQL, we can use the EXPLAIN command to v
> On 08/02/2023 22:37 CET Andrus wrote:
>
> > > How to create backup in format from which tables can selectively
> > > restored?
> > >
> > Dump as custom-format archive (-F custom) and use that with pg_restore
> > and options --table or --list/--use-list to select what should be
> > restored.
> >
1. The whole index does not need to fit in memory, just the parts of it you
need at that time.
2. Partition the table by the primary key. Each index will be *much*
smaller, since each child will be smaller.
On 2/8/23 16:14, Siddharth Jain wrote:
OK so in that case we are left with the B-Tree
> On Feb 8, 2023, at 14:14, Siddharth Jain wrote:
>
> If the B-Tree index will be so large that it cannot fit in memory, then is it
> worth creating it at all?
Yes. Of course, more memory is better, and more recently versions of
PostgreSQL have optimizations that are valuable for large B-
OK so in that case we are left with the B-Tree index.
If the B-Tree index will be so large that it cannot fit in memory, then is
it worth creating it at all? Are there any established patterns here?
On Wed, Feb 8, 2023 at 1:21 PM Christophe Pettus wrote:
>
>
> > On Feb 8, 2023, at 13:17, Siddha
Hi!
How to create backup in format from which tables can selectively restored?
Dump as custom-format archive (-F custom) and use that with pg_restore and
options --table or --list/--use-list to select what should be restored.
How to select tables interactively like pgAdmin allows to select w
> On Feb 8, 2023, at 13:17, Siddharth Jain wrote:
>
> As I explained in my question that is indeed our dilemma. Our insertion order
> will not be equal to index order. i.e., referring to your response:
>
> > who's data is added in the same order as the key in the BRIN index
>
> does NOT hol
> On 08/02/2023 21:59 CET Andrus wrote:
>
> How to create backup in format from which tables can selectively restored?
Dump as custom-format archive (-F custom) and use that with pg_restore and
options --table or --list/--use-list to select what should be restored.
--
Erik
As I explained in my question that is indeed our dilemma. Our insertion
order will not be equal to index order. i.e., referring to your response:
> who's data is added in the same order as the key in the BRIN index
does NOT hold.
On Wed, Feb 8, 2023 at 12:27 PM Ron wrote:
> Is the data in your
Hi!
>Looks like your filesystem on client is having limits on file sizes.
Use better filesystem, or just dump on linux, it's filesystems usually
don't hit these limits.
This file size is only 6.2 GB. If custom format is used, pg_dump creates
large file without problems. There are no file siz
On Wed, Feb 8, 2023 at 11:56 AM Kirk Wolak wrote:
>
> CREATE FUNCTION generate_ulid() RETURNS uuid
> LANGUAGE sql
> RETURN ((lpad(to_hex((floor((EXTRACT(epoch FROM clock_timestamp()) *
> (100)::numeric)))::bigint), 14, '0'::text)
> || encode(gen_random_bytes(9), 'hex'::text)))::uuid
Is the data in your tables stored in natural correlation with those *three*
columns? I'm dubious that can even happen.
BRIN is best for *range queries* on tables who's data is added in the same
order as the key in the BRIN index (for example, a BRIN index on a timestamp
field in a log table w
our insertion order is of course != index order otherwise the question
would have been trivial.
we use postgres 14
On Wed, Feb 8, 2023 at 11:51 AM Siddharth Jain wrote:
> Hello,
>
> We have large tables with billions of rows in them and want to take
> advantage of the BRIN index on them.
>
> Iss
On Wed, Feb 8, 2023 at 4:18 AM veem v wrote:
> Thank you So much all for such valuable feedback.
> ..
> So wanted to know from experts here, is there really exists any scenario
> in which UUID really cant be avoided?
>
> Funny you are asking about this. My recent experience is that UUIDs
really
Hello,
We have large tables with billions of rows in them and want to take
advantage of the BRIN index on them.
Issues we are facing:
- as I understand, BRIN index is useful only if the data is stored in
index order. As an example we want to create a composite BRIN index on 3
columns -
Hi the Postgresql binaries and installer are provided by EDB
How can i verify the download
Usually other projects provide a SHA256 hash list to verify the downloads
EDB doesnt seem to provide that, so how else can i verify the download
Thanks
Ali
On Wed, Feb 08, 2023 at 05:00:10PM +0200, Andrus wrote:
> Hi!
>
> Creating backup in directory format using
>
> pg_dump -f "sba" -Fdirectory --jobs=32 --verbose sba
>
> throws error
>
> pg_dump: error: could not stat file "sba/282168.data.gz": value too
> large
>
> How to fix it ?
>
>
OK: so I think I've got some new insight by using test_decoding to
peek into the changes being replicate, here is what i think is
happening:
- there is a background job generating a Huge transaction (10K
changes) one after another that basically does DELETE on 1 table,
INSERT on 1 table and UPDAT
Hi!
Creating backup in directory format using
pg_dump -f "sba" -Fdirectory --jobs=32 --verbose sba
throws error
pg_dump: error: could not stat file "sba/282168.data.gz": value too
large
How to fix it ?
Server is Postgres 12 running in Debian Linux 10 under WSL
Client is pg_dump fro
On Wednesday, February 8, 2023, Philippe VIEGAS
wrote:
> Hi all,
>
> I was wondering why the index statistics usage were not reflecting the
> index usage for primary key constraint validation ?
>
> When we create a table with a primary key, PostgreSQL creates a Btree
> index for the validation of
Good point, thanks Peter!
Seb
From: Peter Eisentraut
Sent: Wednesday, February 8, 2023 12:07 PM
To: Sebastien Flaesch ;
pgsql-general@lists.postgresql.org
Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY
DEFAULT } AS IDENTITY column
Hi all,
I was wondering why the index statistics usage were not reflecting the
index usage for primary key constraint validation ?
When we create a table with a primary key, PostgreSQL creates a Btree
index for the validation of this constraint : index is defined unique
(indisunique=t) and p
On 2023-02-08 14:48:03 +0530, veem v wrote:
> So wanted to know from experts here, is there really exists any scenario in
> which UUID really cant be avoided?
Probably not. The question is usually not "is this possible" but "does
this meet the requirements at acceptable cost".
> Sequence Number
On 07.02.23 11:43, Sebastien Flaesch wrote:
select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname
Just a side note: You might find the quote_ident() function useful.
On 04.02.23 21:55, Erik Wienhold wrote:
Why doesn't this work?
BEGIN;
DROP SEQUENCE t_id;
[This won't work, you need to use ALTER TABLE / DROP IDENTITY.]
CREATE SEQUENCE new_t_id_seq AS INTEGER OWNED BY t.id;
ALTER SEQUENCE new_t_id_seq OWNER TO new_owner;
SELECT setval('new_t_id'
On Wed, 2023-02-08 at 08:49 +0900, Katsuya Okizaki wrote:
> In a normal SQL, we can use the EXPLAIN command to view the execution plan.
> However, in this case, I am not sure how a user-defined function work.
>
> If anyone has faced a similar situation and found a way to view the execution
> plan
Thank you So much all for such valuable feedback.
As "Julian" was pointing, I also tried to test the INSERT independently(as
in below test case) without keeping the "generate_series" in the inline
query. But in all the cases sequence is performing better as compared to
both UUID V4 and UUID V7. An
35 matches
Mail list logo
