Hi,
We have implemented LTO in PostGIS's build system a couple releases ago. It
definitely gives +10% on heavy maths. Unfortunately we did not manage to
get it running under FreeBSD because of default system linker issues so we
had to hide it under --with-lto switch which we recommend to
Hello hackers,
On Tue, Jan 18, 2022 at 11:26 PM sergei sh. wrote:
> Hi,
>
> I've addressed Andrey Borodin's concerns about v2 of this patch by
> Aliaksandr
> Kalenik in attached version.
>
[snip]
This patchset got some attention in the PostGIS development channel, as it
is important to really
On Thu, Dec 2, 2021 at 1:14 AM Tom Lane wrote:
> Alexander Korotkov writes:
> > I think losing precision in the gist penalty is generally OK. Thus,
> > it shouldn't be a problem to round a very small value as zero.
>
> Check.
>
> > Probably, we could even tolerate overflow in the gist penalty.
PostGIS has a very similar thing: ST_Transform is marked as immutable but
does depend on contents of spatial_ref_sys table. Although it is shipped
with extension and almost never changes incompatibly, there are scenarios
where it breaks: dump/restore + index or generated column can fail the
import
hearing
> from you.
>
> Best regards,
> Han
>
--
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/
t
>
> I'm thinking thanks to returning "setof text", such a function could read
> a stream,
> and return a line as soon as a delimiter is encountered, not having to keep
> the entire file in memory at any time.
>
> /Joel
>
--
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/
;
>
> to only rebuild indexes depending on outdated libc collations, or
>
> REINDEX (COLLATION 'libc', VERSION 'X.Y') DATABASE mydb;
>
> to only rebuild indexes depending on a specific version of libc.
>
--
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/
чц, 11 лют 2021, 21:33 карыстальнік Tom Lane напісаў:
> Noah Misch writes:
> > On Tue, Feb 09, 2021 at 05:34:37PM -0500, Tom Lane wrote:
> >> * A case could be made for tightening things up a lot more, and not
> >> allowing anything that doesn't look like an identifier. I'm not
> >> pushing
scan
> if we create an index and table use CTAS, else people have to use index
> only scan
> after vacuum. If people do not expect freeze could we at least introduce a
> option to
> specify the visibility during inserting?
>
> Regards,
> Paul
--
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/
Having row count right away is very useful in CTAS in analytical and GIS
usage scenarios.
пт, 22 сту 2021, 16:14 карыстальнік Vik Fearing
напісаў:
> I was recently surprised by the following inconsistencies in returned
> command tags for CTAS:
>
>
> postgres=# create table a as select 123;
>
On Wed, Sep 9, 2020 at 3:09 PM Heikki Linnakangas wrote:
> On 09/09/2020 13:28, Andrey M. Borodin wrote:
> > Thanks Darafei!
> >
> >> 9 сент. 2020 г., в 12:05, Darafei Komяpa Praliaskouski
> >> написал(а):
> >>
> >>> How does the 'sortsuppo
Hi,
On Wed, Sep 9, 2020 at 9:43 AM Andrey M. Borodin
wrote:
>
>
> > 9 сент. 2020 г., в 00:05, Heikki Linnakangas
> написал(а):
> >
> > I've been reviewing the patch today. The biggest changes I've made have
> been in restructuring the code in gistbuild.c for readability, but there
> are a
Hello,
The problem with higher dimension cubes is that starting with
dimensionality of ~52 the "distance" metrics in 64-bit float have less than
a single bit per dimension in mantissa, making cubes indistinguishable.
Developers for facial recognition software had a chat about that on russian
Hi,
On Fri, Jun 12, 2020 at 4:25 AM Tom Lane wrote:
> =?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?=
> writes:
> > I've had the same issue with multiplying two tiny numbers. Select
> > 2e-300::float * 2e-300::float gives an underflow, and it is not a wanted
> > thing. This looks like
пт, 12 чэр 2020, 02:57 карыстальнік Tom Lane напісаў:
> I wrote:
> > The POSIX standard says this about the exp(3) function:
> > If x is -Inf, +0 shall be returned.
> > At least on my Linux box, our version does no such thing:
> > regression=# select exp('-inf'::float8);
> > ERROR: value
>
>
>
> John, I think it's worth pointing out that Postgres most likely does a
> full table scan to validate a constraint by design and not in optimization
> oversight. Think of what's gonna happen if the index used for checking is
> corrupted?
>
>
This can't be true: a corrupted index is a
Hello,
These things for GIST I know that can help:
- Fast sorting GIST build commitfest entry by Andrey Borodin, not parallel
but faster -
https://www.postgresql.org/message-id/flat/1A36620E-CAD8-4267-9067-FB31385E7C0D%40yandex-team.ru
- Fast sorting GIST build by Nikita Glukhov, reuses btree
Hello Yuri,
PDEP is indeed first thing that comes up when you start googling
z-curve and bit interleaving :)
We had the code with z-curve generating PDEP instruction in PostGIS,
and dropped it since. In sorting, we now utilize sort support / prefix
search, and key generated as Hilbert curve, with
> > According to my reckoning, that is the remaining objection to the patch
> > as it is (with ordinary freezing behavior).
> >
> > How about a scale_factor od 0.005? That will be high enough for large
> > tables, which seem to be the main concern here.
>
> Seems low on a first blush. On a
On Fri, Mar 13, 2020 at 3:19 AM Laurenz Albe wrote:
>
> On Fri, 2020-03-13 at 09:10 +1300, David Rowley wrote:
> > So you're suggesting we drive the insert-vacuums from existing
> > scale_factor and threshold? What about the 1 billion row table
> > example above?
>
> I am still not 100% certain
Hi,
Thanks Laurenz for taking action on this and writing a better patch
than my initial.
This will help avoid both Mandrill-like downtimes and get Index Only
Scan just work on large telemetry databases like the one I was
responsible for back when I was in Juno.
On Thu, Mar 5, 2020 at 9:40 AM
Hello,
Thanks for the patch and working on GiST infrastructure, it's really
valuable for PostGIS use cases and I wait to see this improvement in
PG13.
On Sat, Feb 29, 2020 at 3:13 PM Andrey M. Borodin wrote:
> Thomas, I've used your wording almost exactly with explanation how
>
ote:
>
> Greetings,
>
> * Darafei "Komяpa" Praliaskouski (m...@komzpa.net) wrote:
> > can it be raised on pgsql-hackers as a thing impacting PostGIS upgrade path?
>
> Why is it impacting the PostGIS upgrade path? The FROM UNPACKAGED was
> never intended to
that patch there breaks geometric indexing back
then.
On Tue, Jan 21, 2020 at 2:07 AM Egor Rogov wrote:
> On 21.01.2020 0:00, Darafei "Komяpa" Praliaskouski wrote:
> > Hi,
> >
> > Found out today that BRIN indexes don't really work for PostGIS and
> > box
Hello,
> btree_gin
> btree_gist
I would even ask btree_gin and btree_gist to be moved to core.
btree_gist is shipping opclasses for built in types to be used in gist
indexes. btree_* is confusing part in the name pretending there's some
magic happening linking btree and gist.
gist is the
Hi,
Found out today that BRIN indexes don't really work for PostGIS and box
datatypes.
Since
https://github.com/postgres/postgres/commit/7e534adcdc70866e7be74d626b0ed067c890a251
Postgres
requires datatype to provide correlation statistics. Such statistics wasn't
provided by PostGIS and box
>
>
> This is somewhat similar to a memory usage problem with a
> parallel query where each worker is allowed to use up to work_mem of
> memory. We can say that the users using parallel operation can expect
> more system resources to be used as they want to get the operation
> done faster, so we
Hi,
On Fri, Sep 20, 2019 at 11:14 PM Robert Haas wrote:
> On Wed, Jul 17, 2019 at 5:20 PM Darafei "Komяpa" Praliaskouski
> wrote:
> > Indeed, it seems I failed to minimize my example.
> >
> > Here is the actual one, on 90GB table with 16M rows:
Hello,
This is very interesting. In my pipeline currently GiST index rebuild is
the biggest time consuming step.
I believe introducing optional concept of order in the GiST opclass will be
beneficial not only for fast build, but for other tasks later:
- CLUSTER can order the table using that
Hi,
On my PG11 I have set it to 64 upon setup and it propogated to
postgresql.auto.conf and is set after restart. I've upgraded to PG12 since
then, and parameter is read from postgresql.auto.conf correctly and is
displayed via SHOW (just checked on 12beta3).
I also spent some time trying to get
>
> But none of our contrib modules do it like that, and I'd lay long odds
> against any third party code doing it either.
Thoughts?
>
PostGIS has some rarely used box operations as part of GiST opclass, like
"overabove".
These are source of misunderstanding, as it hinges on the fact that
Hi,
On Wed, Jul 17, 2019 at 11:58 PM Tom Lane wrote:
> =?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?=
> writes:
> > Many thanks for the parallel improvements in Postgres 12. Here is one of
> > cases where a costy function gets moved from a parallel worker into main
> > one, rendering
Hi,
Many thanks for the parallel improvements in Postgres 12. Here is one of
cases where a costy function gets moved from a parallel worker into main
one, rendering spatial processing single core once again on some queries.
Perhaps an assumption "expressions should be mashed together as much as
On Thu, Jun 27, 2019 at 6:00 AM Tom Lane wrote:
> =?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?=
> writes:
> > I'm looking at PostGIS geometry GiST index build times and try to
> optimize
> > withing the current GiST framework. The function that shows a lot on my
> > flame graphs is
Hi,
I'm looking at PostGIS geometry GiST index build times and try to optimize
withing the current GiST framework. The function that shows a lot on my
flame graphs is penalty.
I spent weekend rewriting PostGIS penalty to be as fast as possible.
(FYI
the error message.
On Thu, May 16, 2019 at 7:05 PM Darafei "Komяpa" Praliaskouski <
m...@komzpa.net> wrote:
> Hi!
>
> Greetings from OSGeo Code sprint in Minneapolis :)
>
> We're trying to make FULL JOIN on equality of geometry and can't figure
> out why it d
Hi!
Greetings from OSGeo Code sprint in Minneapolis :)
We're trying to make FULL JOIN on equality of geometry and can't figure out
why it doesn't work.
Here's reproducer, it works on bytea but not on PostGIS geometry throwing
out
ERROR: FULL JOIN is only supported with merge-joinable or
>
>
> >I don't think it's helpful to force emergency vacuuming more
> >frequently;
> >quite the contrary, it's likely to cause even more issues. We should
> >tweak autovacuum to perform freezing more preemtively instead.
>
> I still think the fundamental issue with making vacuum less painful is
>
On Wed, Apr 10, 2019 at 6:13 PM Alvaro Herrera
wrote:
> On 2019-Mar-31, Darafei "Komяpa" Praliaskouski wrote:
>
> > Alternative point of "if your database is super large and actively
> written,
> > you may want to set autovacuum_freeze_max_age to even smalle
>
> The invoking autovacuum on table based on inserts, not only deletes
> and updates, seems good idea to me. But in this case, I think that we
> can not only freeze tuples but also update visibility map even when
> setting all-visible. Roughly speaking I think vacuum does the
> following
On Fri, Apr 5, 2019 at 6:58 AM Tom Lane wrote:
> Andres Freund writes:
> > I think the right approach would be to do all of this in heap_insert and
> > heap_multi_insert. Whenever starting to work on a page, if INSERT_FROZEN
> > is specified, remember whether it is either currently empty, or is
Hi!
> I'll plan to push this tomorrow with the above change (and a few
> additional comments to explain what all is going on..).
Is everything ok? Can it be pushed?
I'm looking here, haven't found it pushed and worry about this.
https://github.com/postgres/postgres/commits/master
>
>
> > Idea: look not on dead tuples, but on changes, just like ANALYZE does.
> > It's my first patch on Postgres, it's probably all wrong but I hope it
> > helps you get the idea.
>
> This was suggested and rejected years ago:
>
>
>
> If it's months, we probably want limit vacuum to working at a pretty
> slow rate, say 1% of the table size per hour or something. If it's in
> hours, we need to be a lot more aggressive. Right now we have no
> information to tell us which of those things is the case, so we'd just
> be
>
> By the way, the Routine Vacuuming chapter of the documentation says:
>
> "The sole disadvantage of increasing autovacuum_freeze_max_age (and
> vacuum_freeze_table_age along with it) is that the pg_xact and
> pg_commit_ts subdirectories of the database cluster will take more space
>
> [...]
>
>
On Thu, Mar 28, 2019 at 6:43 PM Masahiko Sawada
wrote:
> >> 1. heap vacuum
> >>
> >> 2. HOT pruning
> >
> > Is it worth skipping it if we're writing a page anyway for the sake of
> hint bits and new xids? This will all be no-op anyway on append-only tables
> and happen only when we actually need
Hi,
> > Why not select a table that has inserts, updates and deletes for
autovacuum just like we do for autoanalyze, not only deletes and updates
like we do now?
> >
> > Sounds like a good idea, although I do agree with Alvaro when he
> > mentions that it would be good to only invoke a worker
On Thu, Mar 28, 2019 at 12:32 PM David Rowley
wrote:
> On Thu, 28 Mar 2019 at 22:04, Darafei "Komяpa" Praliaskouski
> wrote:
> >
> > On Thu, Mar 28, 2019 at 2:36 AM David Rowley <
> david.row...@2ndquadrant.com> wrote:
> >> I thought recently that
On Thu, Mar 28, 2019 at 2:36 AM David Rowley
wrote:
> On Thu, 28 Mar 2019 at 11:01, Alvaro Herrera
> wrote:
> >
> > On 2019-Mar-28, Darafei "Komяpa" Praliaskouski wrote:
> > > "Nearing wraparound" is too late already. In Amazon, reading table
>
Hi,
чт, 28 мар. 2019 г. в 00:32, Alvaro Herrera :
> On 2019-Mar-27, Darafei "Komяpa" Praliaskouski wrote:
>
> > Attached is sketch of small patch that fixes several edge cases with
> > autovacuum. Long story short autovacuum never comes to append only
> tables
Hi hackers,
Attached is sketch of small patch that fixes several edge cases with
autovacuum. Long story short autovacuum never comes to append only tables,
killing large productions.
First case, mine.
Hello,
at the very least my Ubuntu Cosmic has createdb, createuser and createlang
in user's space, and I had at least two cases when people were trying to
use createuser to create a new OS user.
I would prefer them having pg_ prefix to have less confusion.
On Wed, Mar 27, 2019 at 4:51 PM Tomas
I really appreciate the addition of tanh into core postgres.
If someone doubts it is useful: it is used as a part of math in
geographical calculations.
Say you have your cars in planar Mercator projection and want to move them
"1 second forward by this heading with this speed". sin/cos and the
I support rising the default.
>From standpoint of no-clue database admin, it's easier to give more
resources to Postgres and google what process called "autovacuum" does than
to learn why is it being slow on read.
It's also tricky that index only scans depend on working autovacuum, and
>
>
> Thoughts?
I have a feeling this is over-engineering in slightly different direction,
solving the way for hack to work instead of original problem.
What's currently happening in PostGIS is that there are functions that need
to perform index-based lookups.
Postgres is unable to plan this
Hello,
Today I bumped into need to limit first VACUUM time on data import.
I'm using utility called osmium together with COPY FREEZE to import
openstreetmap data into database.
osmium export -c osmium.config -f pg belarus-latest.osm.pbf -v --progress
| psql -1 -c 'create table byosm(geom
Hi,
Writing as someone who used TID joins and group by's in the past.
One use case is having a chance to peek into what will DELETE do.
A lot of GIS tables don't have any notion of ID, and dirty datasets tend to
have many duplicates you need to cross-reference with something else. So,
you write
Call it "pile" and "hoard":
https://www.thesaurus.com/browse/heap
https://www.thesaurus.com/browse/pile
https://www.thesaurus.com/browse/hoard
ср, 19 дек. 2018 г. в 07:17, Andres Freund :
> Hi,
>
> The current pluggable table storage patchset [1] introduces the ability
> to specify the access
>
> > In PostGIS workloads, UPDATE table SET geom = ST_CostyFunction(geom,
> magicnumber); is one of biggest time-eaters that happen upon initial load
> and clean up of your data. It is commonly followed by CLUSTER table using
> table_geom_idx; to make sure you're back at full speed and no VACUUM
On Sat, Nov 17, 2018 at 8:51 AM Adam Brusselback
wrote:
> > I don't know how much what I write on this thread is read by others or
> how useful this is for others who are following this work
>
> I've been following this thread and many others like it, silently soaking
> it up, because I don't
>
>
>> At pgconf.eu, I canvassed this problem and some potential solutions:
>>
>
I wonder if there is a middle ground between #2 and #3. A proper mechanism
for deduplicating entries might be hard, but on the inlining stage we
already know they're going to get duplicated. Can we make a
>
>
> Because you said "faster than reasonable IPC" - which to me implies that
> you don't do full blown IPC. Which using threads in a bgworker is very
> strongly implying. What you're proposing strongly implies multiple
> context switches just to process a few results. Even before, but
>
>
> In theory, simulating such global limit should be possible using a bit
> of shared memory for the current total, per-process counter and probably
> some simple abort handling (say, just like contrib/openssl does using
> ResourceOwner).
>
I would expect that this limit is already available and
Hi,
I've tried porting some of PostGIS algorithms to utilize multiple cores via
OpenMP to return faster.
Question is, what's the best policy to allocate cores so we can play nice
with rest of postgres?
What I'd like to see is some function that I can call and get a number of
threads I'm allowed
>
> > That is not the first file people looking at. Especially not people
> looking
> > at the GitHub copy:
> >
> > https://github.com/postgres/postgres
> >
> > I understand that there is documentation, but for the casual developer
> > looking at this, it seems broken.
>
> FWIW, I think that
Hello,
пн, 23 июл. 2018 г. в 8:13, Andres Freund :
> Hi,
>
> On 2018-07-21 23:14:47 +0300, Darafei "Komяpa" Praliaskouski wrote:
> >
> > I suspect that a fix would require to bisect llvm/clang version which
> stops
> > showing this beha
Hi,
Here's somewhat minimized example.
https://gist.github.com/Komzpa/cc3762175328ff5d11de4b972352003d
You can put this file into regress/jitbug.sql in PostGIS code tree and run
after building postgis:
perl regress/run_test.pl regress/jitbug.sql --expect
perl regress/run_test.pl
Hi,
Today I spent some time closing PostGIS tickets in preparation to Monday's
release.
One of the blockers, https://trac.osgeo.org/postgis/ticket/4125, was filed
by Postgres APT repository maintainer Christoph Berg who noticed a test
suite failure on Debian Stretch with Postgres 11.
Upon
вт, 26 июн. 2018 г. в 15:42, Alexander Korotkov :
> On Tue, Jun 26, 2018 at 1:46 PM Masahiko Sawada
> wrote:
> > On Fri, Jun 22, 2018 at 6:55 PM, Alexander Korotkov
> > wrote:
> > > So, I propose to just
> > > increase maximum value for both GUC and reloption. See the attached
> > > patch. It
Hi!
It is cool to see this in Postgres 11. However:
> 4) vacuum_cleanup_index_scale_factor can be set either by GUC or
> reloption.
> Default value is 0.1. So, by default cleanup scan is triggered after
> increasing of
> table size by 10%.
>
vacuum_cleanup_index_scale_factor can be set to the
>
> >> The real question is why check_function_bodies doesn't cover this;
> >> there's a comment in fmgr_c_validator that this is deliberate, but it's
> >> rather unclear what the advantage is supposed to be:
>
> Tom> Error detection, ie did you spell the C symbol name correctly.
>
> Right,
This thing also bites PostGIS upgrades.
When distro's packaging system decides to upgrade PostGIS, or both
Postgres/PostGIS at the same time, you may often get to a situation when
you only have one version of PostGIS .so installed, and it's not the one
referenced in catalog. There are workarounds
>
> Therefore, I propose the attached patch, which simply sees to it that
> we discard any partial query result at the start of error message
> collection not the end. This makes the behavior very much better,
> at least on Linux.
>
I have tested the build of Postgres with attached patch and
>
> The above is all about getting the build system to work at all. If that
> isn't a showstopper there's a subsequent discussion to be had about older
> platforms where one could get the build system to work but convenient
> packages are missing. For example not even RHEL7 has any Python3
> Another thing that could be done for PostGIS geometries is just another
> opclass which
> stores geometries "as is" in leafs. As I know, geometries contain MBRs
> inside their
> own, so there is no need to store extra MBR. I think the reason why
> PostGIS
> doesn't have such opclass yet is
>
>
> > Is it expected behavior (so I can have a look at something server
> returned
> > somehow and it's kept there for me), or a plain leak?
>
> This is totally normal behaviour for any C program.
>
Thanks Konstantin and Craig for the help.
To mitigate the issue I've changed the allocator on
Hi,
psql (PostgreSQL) 10.3
Here are the steps to reproduce a leak:
1. connect to 10.3 server, perform the query similar to:
select 'message' || generate_series(1,10);
2. monitoring psql memory usage in htop or similar tool, press ctrl+c at
some point where you can clearly distinguish
Hi,
on a PostGIS system tuned for preferring parallelism heavily (
min_parallel_table_scan_size=10kB) we experience issues with QGIS table
discovery query with this patch:
Failing query is:
[local] gis@gis=# SELECT
Hi Tom,
> I hadn't been following this thread particularly, but I happened to notice
> this bit, and I thought I'd better pop up to say No Way. There will be
> *no* implicit casts from json to any numeric type. We have learned the
> hard way that implicit cross-category casts are dangerous.
>
вт, 6 мар. 2018 г. в 4:57, Craig Ringer <cr...@2ndquadrant.com>:
> On 3 March 2018 at 00:30, Darafei "Komяpa" Praliaskouski <m...@komzpa.net>
> wrote:
>
>
>> I gave this all some thought and it looks like it all could have not
>> happened if Postgres
пн, 5 мар. 2018 г. в 19:48, Ants Aasma <ants.aa...@eesti.ee>:
> On Mon, Mar 5, 2018 at 2:11 PM, Darafei "Komяpa" Praliaskouski
> <m...@komzpa.net> wrote:
> >> This approach mixes well with hash
> >> partitioning. It would be neat indeed if Post
>
> This approach mixes well with hash
> partitioning. It would be neat indeed if PostgreSQL do something
> equivalent on its own, and pluggable storage work being done could
> enable index organized tables that would help. But you probably need
> something right now.
>
Fixing glaring issues (no
Hi,
I work at a ride sharing company and we found a simple scenario that
Postgres has a lot to improve at.
After my talk at pgconf.ru Alexander Korotkov encouraged me to share my
story and thoughts in -hackers.
Story setting:
- Amazon RDS (thus vanilla unpatchable postgres),
> Attached new version of the patch in which I removed duplicated code
using new subroutine JsonbExtractScalar(). I am not sure what is better to
do when a JSON item has an unexpected type: to throw an error or to return
SQL NULL. Also JSON nulls could be converted to SQL NULLs.
I would expect
Hello!
> However, while I was doing that, it seemed like the tests I was adding
> were mighty repetitive, as many of them were just exactly the same thing
> adjusted for a different kind of loop statement. And so I began to wonder
> why it was that we had five copies of the RC_FOO management
85 matches
Mail list logo
