Yes, you are right. The presented index usage data is caused by recursive
queries, which check the integrity of hierarchical structures from the
bottom up. Your explanation has clarified what is going on. Thank you.
My experiments with indexes are caused by the appearance of significant
variance (1
On Mon, Jun 20, 2022 at 1:17 AM Дмитрий Иванов
wrote:
> Your statement seems obvious to me. But what I see doesn't seem like a
> conscious choice. It turns out that it is better to have a lighter
> general-purpose index than to strive to create a target covering index for
> a certain kind of oper
Your statement seems obvious to me. But what I see doesn't seem like a
conscious choice. It turns out that it is better to have a lighter
general-purpose index than to strive to create a target covering index for
a certain kind of operation.
DROP INDEX IF EXISTS bpd.index_class_prop_id_prop_inheri
On Sun, Jun 19, 2022 at 12:06 PM Дмитрий Иванов
wrote:
> Good afternoon.
> I have a query parser question. If there are two kinds of queries using an
> indexed field. In this case, one view is limited to this field, the second
> one uses a number of fields included in the index by the include dir
Good afternoon.
I have a query parser question. If there are two kinds of queries using an
indexed field. In this case, one view is limited to this field, the second
one uses a number of fields included in the index by the include directive.
It makes sense to have two indexes, lightweight and conta
On Fri, 21 Jan 2022 23:38:44 -0700, David G. Johnston wrote:
> On Fri, Jan 21, 2022 at 5:39 AM Matthias Apitz wrote:
>
>>
>> Hello,
>>
>> Does the PostgreSQL (11.4 or 13.1) record somewhere in system tables
>> the creation of INDEXes (or other objects)?
>>
>>
> 13.1? Really?
>
Sorry, this was a t
On Fri, Jan 21, 2022 at 5:39 AM Matthias Apitz wrote:
>
> Hello,
>
> Does the PostgreSQL (11.4 or 13.1) record somewhere in system tables
> the creation of INDEXes (or other objects)?
>
>
13.1? Really?
Features are not point-release dependent so v11 or v13 suffices when trying
to figure out whet
On Fri, Jan 21, 2022 at 01:38:59PM +0100, Matthias Apitz wrote:
> Does the PostgreSQL (11.4 or 13.1) record somewhere in system tables
> the creation of INDEXes (or other objects)?
Hard to say what you are looking for with such a general question.
Would pg_index or pg_indexes be enough? There ar
Hello,
Does the PostgreSQL (11.4 or 13.1) record somewhere in system tables
the creation of INDEXes (or other objects)?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13, 1961: Bet
Extra tables is not something that will work out in my workflow...
I've managed to cut the time in half already, but perhaps there's more to
be won.
In one of the calculations done on the st_area, I used a log(base,value),
but since the only 2-param log function present in postgres takes numerics
a
Why not create a table with cols a, b, c and d. Where you insert a row for
each combination and key and index abc then return d?
ons. 24. feb. 2021, 21:15 skrev Paul van der Linden <
paul.doskabou...@gmail.com>:
> Thanks for all the suggestions,
>
> When the server is not in use for mission-criti
Thanks for all the suggestions,
When the server is not in use for mission-critical work, I'll definitely
going to do some testing based on your ideas.
Will let you know what comes out of that
Cheers,
Paul
On Thu, Feb 18, 2021 at 6:49 PM hubert depesz lubaczewski
wrote:
> On Thu, Feb 18, 2021 a
On 2021-02-16 19:30:23 +0100, Paul van der Linden wrote:
> I have 2 functions:
>
> CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
> $func$
> DECLARE
> retVal text;
> BEGIN
> SELECT
> CASE
> ... snip long list containing various tests on a,b and c
> END INTO
On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote:
> [1]https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/
>
> Thanks for this reference. I enjoy your blog, but haven't made the time to
> read all the archives somehow. Stuff doesn't stick very
> well when it isn
>
> https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/
Thanks for this reference. I enjoy your blog, but haven't made the time to
read all the archives somehow. Stuff doesn't stick very well when it isn't
yet "needed" info besides.
I have seen overhead from 'raise notice' in
On Wed, Feb 17, 2021 at 08:40:17PM +0100, Paul van der Linden wrote:
> The st_area calculation is done mostly once or sometimes twice for each geom,
> and I suspect that can't explain the factor 20 slower.
> Creating an index with only one st_area calculation is also done rather
> quickly.
In th
The st_area calculation is done mostly once or sometimes twice for each
geom, and I suspect that can't explain the factor 20 slower.
Creating an index with only one st_area calculation is also done rather
quickly.
On Wed, Feb 17, 2021 at 7:48 PM hubert depesz lubaczewski
wrote:
> On Tue, Feb 16,
On Tue, Feb 16, 2021 at 07:30:23PM +0100, Paul van der Linden wrote:
> Hi,
> I have 2 functions:
> CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
> $func$
> DECLARE
> retVal text;
> BEGIN
> SELECT
> CASE
> WHEN a='v1' AND b='b1' THEN 'r1'
> WHEN a='v1' THE
take?
Ditto SELECT a, b, c, foo(a, b, c), bar(foo(a, b, c)) from blarg;
Ditto SELECT a, b, c, foo(a, b, c) from blarg where bar(foo(a, b, c)) < 12;
That'll narrow the problem.
Is there any way to speed up that index creation? Is upgrading to a newer
postgres a viable option (so the JITTER can speed up the functions)?
Paul
--
Angular momentum makes the world go 'round.
a
> generated column (PG12+ natively, or maintained by a trigger before)? Or
> even bar(foo(a,b,c),geom)?
>
> Do you know if parallel_workers are being used?
>
> JIT is available in PG11, it is just off by default. If it is available,
> turning it on and trying it seems like the
parallel_workers are being used?
JIT is available in PG11, it is just off by default. If it is available,
turning it on and trying it seems like the simplest check if it would speed
up the index creation.
gist(geom) WHERE bar(foo(a,b,c),geom)<12 it takes
over 20 hours...
Is that because I'm using functions in the WHERE clause, or because my CASE
lists are quite long, or both?
Is there any way to speed up that index creation? Is upgrading to a newer
postgres a viable option (so the JITTER can speed up the functions)?
Paul
Hi David, Adrian,
Thanks for the information.
Sure, will post on PostGIS community.
Regards,
PostgAnn.
On Thu, May 21, 2020 at 8:21 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, May 21, 2020 at 7:45 AM postgann2020 s
> wrote:
>
>> >And what type of data exactly are we tal
On Thu, May 21, 2020 at 7:45 AM postgann2020 s
wrote:
> >And what type of data exactly are we talking about. ==> Column is
> stroing GIS data.
>
GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this
is maybe better posted to the PostGIS community directly...
David J.
On 5/21/20 7:27 AM, postgann2020 s wrote:
Hi Team,
Thanks for your support.
Could you please suggest on below query.
Environment
PostgreSQL: 9.5.15
Postgis: 2.2.7
Mostly table contains GIS data and we are trying to creating an index on
the column which is having an avg width of 149bytes.
C
Hi David,
Thanks for your email.
>And what type of data exactly are we talking about. ==> Column is stroing
GIS data.
Regards,
PostgAnn.
On Thu, May 21, 2020 at 8:06 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, May 21, 2020 at 7:28 AM postgann2020 s
> wrote:
>
>> which
On Thu, May 21, 2020 at 7:28 AM postgann2020 s
wrote:
> which is having an avg width of 149bytes.
>
The average is meaningless if your maximum value exceeds a limit.
2. What type of index is the best suited for this type of data?.
>
And what type of data exactly are we talking about. "TEXT" i
Hi Team,
Thanks for your support.
Could you please suggest on below query.
Environment
PostgreSQL: 9.5.15
Postgis: 2.2.7
Mostly table contains GIS data and we are trying to creating an index on
the column which is having an avg width of 149bytes.
CREATE INDEX index_idx
ON SCHEMA.TABLE
USING
e building of indexes is slow
(actually I'm not sure whether less than 50 seconds per index is slow
for tables of this size, but it's too slow for your application)
> After successfully inserting all the rows, we are trying to create all those
> indexes.
>
> Examp
Hi Peter,
Thanks for your time.
Out Postgresql version: 9.5.9.14.
We are using COPY command to insert rows into the tables. While running COPY
command, all indexes dropped.
After successfully inserting all the rows, we are trying to create all those
indexes.
Example of index creation script
SEZ Block 1A, Rajarhat
700156, Kolkata, West Bengal
India
ericsson.com<http://www.ericsson.com/>
From: Jayadevan M
Sent: Friday, January 31, 2020 3:30 PM
To: Sandip Pradhan
Cc: Ron ; pgsql-general@lists.postgresql.org
Subject: Re: Need support on tuning at the time of index creation
On
On Fri, Jan 31, 2020 at 3:22 PM Sandip Pradhan
wrote:
> Hi Ron,
>
> Thanks for your time.
>
> We are using the version 9.5.9.14.
>
May be you could try tweaking maintenance_work_mem?
Regards,
Jayadevan
-Original Message-
From: Ron
Sent: Monday, January 27, 2020 4:57 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Need support on tuning at the time of index creation
On 1/27/20 5:10 AM, Sandip Pradhan wrote:
> Dear Sir/Madam,
>
> One of our ericsson product used backend db as postgre
On 2020-01-27 11:10:36 +, Sandip Pradhan wrote:
> One of our ericsson product used backend db as postgresql 9. We are facing
> following performance issues where we need some support from your side.
> We are having 10 tables and we are inserting around 150 million to 250 million
> records on ea
On Mon, Jan 27, 2020 at 5:27 AM Ron wrote:
> On 1/27/20 5:10 AM, Sandip Pradhan wrote:
> > Dear Sir/Madam,
> >
> > One of our ericsson product used backend db as postgresql 9. We are
> facing
> > following performance issues where we need some support from your side.
> > We are having 10 tables a
On 1/27/20 5:10 AM, Sandip Pradhan wrote:
Dear Sir/Madam,
One of our ericsson product used backend db as postgresql 9. We are facing
following performance issues where we need some support from your side.
We are having 10 tables and we are inserting around 150 million to 250
million records on
Dear Sir/Madam,
One of our ericsson product used backend db as postgresql 9. We are facing
following performance issues where we need some support from your side.
We are having 10 tables and we are inserting around 150 million to 250 million
records on each of those tables. After that we need to
Hi Peter,
On 2018-02-28 16:50:44 +, Phil Florent wrote:
> With an index creation (create index t1_i1 on t1(c1, c2);) I have this kind
> of output :
>
> ./t -d 20 -o "pid, backend_type, query, wait_event_type, wait_event"
> busy_pc | distinct_exe | pid | backend_ty
max(c1) from t1
group by c2; | IO | BufFileWrite
1 | 1 / 2| 10065 | parallel worker | select max(c1) from t1
group by c2; | IO | BufFileWrite
With an index creation (create index t1_i1 on t1(c1, c2);) I have this kind of
output :
./t -d 20 -o "
39 matches
Mail list logo
