Re: [GENERAL] autovacuum worker running amok - and me too ;)

Circling back on this one, I had a look at our analyze code. I found one place where *maybe* we weren't freeing memory and freed it, but analyzing a 2M record table I barely see any bump up in memory usage (from 22M up to 24M at peak) during analyze. And the change I made didn't appear to alter tha

Re: [GENERAL] Postgres not using GiST index in a lateral join

Stop writing so many subqueries, think in joins; the poor planner! SELECT DISTINCT ON (a.id) a.id AS a_id, b.id AS b_id FROM a JOIN b ON ST_Contains(b.shape, a.shape) WHERE b.kind != 1 Also, the DISTINCT ON syntax (PgSQL custom) lets you winnow a result set down to just one of the inputs. P. O

[GENERAL] Re: [postgis-users] Memory management in postgres (with liblwgeom functions in particular)

Igor, Your supposition is is all correct (you might want to ask your questions on postgis-devel, where the developer density is higher). lwalloc by default is just a facade over malloc, for standalone use. But, when used in PostGIS, it it backed by palloc, which is in turn the memory manager provid

[GENERAL] Less is More

Hey all, I have an odd performance quirk I wonder if anyone has any theories for… (not a one-time result, table all heated up, etc) spain=# explain analyze select way from planet_osm_point;                                                         QUERY PLAN --

[GENERAL] relpages for pg_toast.* tables

I've been trying to write up a "pg_total_relation_size()" replacement that just uses the system tables (sum up relpages for tables, indexes and toast tables), thereby avoiding the overhead of running stat() on thousands of filesystem tables, but I've come up against what seems to be an unsuperable

Re: [GENERAL] relpages for pg_toast.* tables

P. On Fri, Aug 28, 2015 at 6:16 AM, Paul Ramsey wrote: > I've been trying to write up a "pg_total_relation_size()" replacement > that just uses the system tables (sum up relpages for tables, indexes > and toast tables), thereby avoiding the overhead of running stat() on &

[GENERAL] PostGIS 2.2.0 Released

http://postgis.net/2015/10/07/postgis-2.2.0 PostGIS 2.2.0 is released! Over the last two years a number of interesting new features have been added, such as: * True nearest-neighbor searching for all geometry and geography types * New volumetric geometry support, including ST_3DDifference, ST_3D

Re: [GENERAL] Update statement results in Out of memory

Running a multi-million row update will take a long time. It's possible you've exposed a memory leak in ST_Buffer (the older your version of GEOS, the more likely that is) but it's also possible you're just running a really long update. I find for batch processing purposes that creating fresh table

Re: [GENERAL] migrating data from an old postgres version

> On Jul 15, 2016, at 6:55 AM, Melvin Davidson wrote: > > > > On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos > wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from his > old one to the new one. So he installed postgres 9.5 on

Re: [GENERAL] Extensions and privileges in public schema

When you create the student user, remove their create privs in public. Then create a scratch schema and grant them privs there. Finally, alter the student user so that the scratch schema appears FIRST in their search path. This will cause unqualified CREATE statements to create in the scratch schem

Re: [GENERAL] Index impact on update?

You'd be better off forcing the table to write in bulk with something like CREATE TABLE mynewtable AS SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog FROM myoldtable; Then index the new table, rename, etc. Bulk update will, in addition to being slow, use 2x the amount of spa

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

The index filters using bounding boxes. A long, diagonal route will have a large bounding box, relative to the area you actually care about (within a narrow strip of the route). Use ST_Segmentize() to add points to your route, ST_DumpPoints() to dump those out as point and ST_MakeLine to generate

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

Thu, Jan 5, 2017 at 11:36 AM, Israel Brewster wrote: > On Jan 5, 2017, at 8:50 AM, Paul Ramsey wrote: > > > The index filters using bounding boxes. A long, diagonal route will have > a large bounding box, relative to the area you actually care about (within > a narrow strip

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

gt; On Jan 5, 2017, at 10:38 AM, Paul Ramsey > wrote: > > Yes, you did. You want a query that spits out a tupleset of goemetries > (one each for each wee segment), and then you can join that set to your > main table using st_dwithin() as the join clause. > So start by ditching

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

lt, > you'll have to reduce the number of row in your index, > that is partition your table into several tables ! > > This is not easy to do with current postgres partitionning methods as far > as I know > (partitionning is easy, automatic efficient query is hard). > > An

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: > Hi All - > > I am trying to install postgis 1.5.2 on solaris10. When I run the > configure I get the following. > > configure: WAR

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

liblwgeom > ERROR: could not access file "$libdir/liblwgeom": No such file or directory > > I am using pg_upgrade for upgrading > > > Regards > > On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey > wrote: > It's not a dynlib, it's statically lin

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

oad library: $libdir/liblwgeom > ERROR: could not access file "$libdir/liblwgeom": No such file or directory > > I am using pg_upgrade for upgrading > > > Regards > > On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey > wrote: > It's not a dynlib, it

Re: [GENERAL] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests

On 2011-02-07, at 11:27 AM, Edoardo Panfili wrote: > On 07/02/11 18.55, Paul Ramsey wrote: >> Well, maybe you could in-place upgrade if you left your PostGIS version >> at the original and only upgraded the PostgreSQL part, but you aren't >> doing that, you're also

[GENERAL] fmgr_oldstyle in extensions

y stop working. So, I’m wondering what I could have done that is causing my calls to route through fmgr_oldstyle instead of the usual path? P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] fmgr_oldstyle in extensions

--  Paul Ramsey http://cleverelephant.ca http://postgis.net On September 23, 2014 at 10:02:33 AM, Andres Freund (and...@2ndquadrant.com) wrote: You forgot to add a PG_FUNCTION_INFO_V1(yourfunc); for the function. 

[GENERAL] spgist index not getting used

can run to get more clues? Thanks, P --  Paul Ramsey http://cleverelephant.ca http://postgis.net

Re: [GENERAL] spgist index not getting used

://postgis.net http://cleverelephant.ca On September 24, 2014 at 4:32:35 PM, Peter Geoghegan (peter.geoghega...@gmail.com) wrote: > On Wed, Sep 24, 2014 at 2:01 PM, Paul Ramsey wrote: > > If I build an index on the same table using the internal quad-tree ops, and > > use their operator, I

Re: [GENERAL] spgist index not getting used

Tom! P. -- http://postgis.net http://cleverelephant.ca On September 24, 2014 at 8:24:02 PM, Tom Lane (t...@sss.pgh.pa.us) wrote: > Paul Ramsey writes: > > My C implementation is here > > https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.c > >

Re: [GENERAL] Indexing large table of coordinates with GiST

geohash them and index them with a btree instead. The index will work better than a rtree for points, efficiencywise, however you’ll still have a multi-billion record table, which could cause other slowdowns, depending on your plans for accessing this data once you’ve indexed it. P. --  Paul

Re: [GENERAL] Indexing large table of coordinates with GiST

query polygons interact with the geohash split points. Also, if you’re planning to slam pretty large polygons through this process, expect it to be kind of slow. You’ll want to do some sharding, to spread the problem out over multiple nodes.   --  Paul Ramsey http://cleverelephant.ca  htt

Re: [GENERAL] What's a good way to improve this query?

quickly enough, but the uncompression step at each access will still be there. You might want to stuff the query through a sampling profiler (OSX Shark!) and confirm, but I would guess you'll find a lot of cycles spinning in zlib for this query. Paul -- Paul Ramsey http://cleverelephant.

Re: [GENERAL] earthdistance compass bearing

The code for azimuth on a sphere isn't so gnarly you couldn't whip it up in plpgsql, http://trac.osgeo.org/postgis/browser/trunk/liblwgeom/lwgeodetic.c#L924 P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net On Tuesday, June 18, 2013 at 11:16 AM, Jeff Herrin wrote:

Re: [GENERAL] Running out of memory the hard way ...

On Wed, Feb 8, 2017 at 7:44 AM, Tom Lane wrote: > Albe Laurenz writes: > > Bill Moran wrote: > >> What I feel is the best way to mitigate the situation, is to have some > >> setting that limits the maximum RAM any backend can consume. > > > I'd delegate that problem to the operating system which

Re: [GENERAL] Development of an extension for PostgreSQL and PostGIS

In order to get an LWGEOM from PostGIS you'll need to convert from the serialized form (GSERIALIZED) which you can read all about in the liblwgeom.h header. You'll be adding a hard dependency of course, but hopefully you're OK with that. If you're just hoping to build a compound type, as your exam

Re: [GENERAL] Out of memory error

If you are on PostGIS < 1.3.4 there are substantial memory leaks in intersects() for point/polygon cases. Upgrading to 1.3.6 is recommended. P On Mon, Jul 6, 2009 at 1:39 PM, Paul Smith wrote: > On Mon, Jul 6, 2009 at 3:34 PM, Tom Lane wrote: >> Clearly a memory leak, but it's not so clear exactl

Re: [GENERAL] Upgrading from postgreSQL 9.1.6 to 9.3

Recommendation: do one at a time. First postgis 1.5 to 2.1, then pg 9.1 to 9.3. P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net On Thursday, September 19, 2013 at 4:34 PM, fburg...@radiantblue.com wrote: > > > Are there any showstoppers/recommendations/experie

[GENERAL] round(real,integer)

is on purpose, or just an oversight?  Obviously one can work around it, but I'm sick of doing so, it makes my SQL examples ugly. I’d like to fix it, but not if there’s a reason it’s not supposed to be done. P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net -- Sent via pgs

Re: [GENERAL] round(real,integer)

, so perhaps I’ve finally found my bite-size pgsql contribution project :) P. --  Paul Ramsey http://cleverelephant.ca http://postgis.net On January 10, 2014 at 12:37:04 PM, Tom Lane (t...@sss.pgh.pa.us) wrote: Paul Ramsey writes: > History question: > Why does select round(3,3

Re: [GENERAL] round(real,integer)

True (?) though I’m guessing the real test for most folks is if printf renders it as expected. Anything else if icing on the cake, no? P --  Paul Ramsey http://cleverelephant.ca http://postgis.net On January 10, 2014 at 1:09:24 PM, Tom Lane (t...@sss.pgh.pa.us) wrote: to, say, 6 digits

Re: [GENERAL] Analyze against a table with geometry columns runs out of memory

bug I’ll probably need a copy of the data, or access to a system that has the data and a dev environment. Please do file a ticket at http://trac.osgeo.org/postgis on this issue. P --  Paul Ramsey http://cleverelephant.ca http://postgis.net On May 7, 2014 at 11:15:10 PM, Roxanne Reid-Bennett (r...@t

Re: [GENERAL] Strange dump/restore effect

Try just loading the 1.5 dump directly into the 2.0 database without the filtering step. It will be immensely noisy with lots of errors and warnings, but with luck you should find your data is there waiting for you when it's done. P On Sun, Sep 23, 2012 at 2:37 PM, Gražvydas Valeika wrote: > Hi

Re: [GENERAL] locating cities within a radius of another

create table cities ( geog geography, name varchar, id integer primary key ); insert into cities select Geography(ST_SetSRID(ST_MakePoint(lon, lat),4326)) as geog, name, id from mytable; create index cities_gix on cities using gist ( geog ); select st_distance(a.geog, b.geog),

Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search

Did the FAA ever publish slides of those talks? Sure wish I could see them... :) P. On 2010-08-11, at 6:58 PM, Bruce Momjian wrote: > Greg Smith wrote: >> Greg Williamson wrote: >>> Our tests -- very much oriented at postGIS found Oracle to be between 5 >>> and 15% _faster_ depending on the spe

Re: [GENERAL] R-Trees in PostgreSQL

Also for one-dimensional ranges, consider contrib/seg P. On Mon, Nov 2, 2009 at 4:41 PM, Jeff Davis wrote: > On Tue, 2009-11-03 at 00:25 +0100, Viktor Rosenfeld wrote: >> I'd like to know what kind of functions I have to implement for a R-Tree >> index on numeric columns, > > NUMERIC is scalar,

Re: [GENERAL] incorrect restore from pg_dumpall

In order to restore a backup taken with pg_dumpall you'll want to ensure that the postgis installed in your new system is identical to the postgis in your old one. This is because the postgis function definitions will be looking for a particular postgis library name... the name of the library from

Re: [GENERAL] Installation of Postgis/postgresql

Nick, You'll find GEOS questions get answered better on the GEOS mailing list (http://lists.osgeo.org/mailman/listinfo/geos-devel), BUT: If ./configure is not finding g++ then odds are, you don't have it installed (type g++ on the commandline, see what happens). Use your package manager to do a se

Re: [GENERAL] Large index operation crashes postgres

Can you do? alter table placex add column geometry_sector integer; update placex set geometry_sector = geometry_sector(geometry); P. On Wed, Mar 24, 2010 at 1:15 PM, Frans Hals wrote: > Hi, > > running a geo-database from a dump restore where still one of the most > important indexes is missing

Re: [GENERAL] Large index operation crashes postgres

Occams razor says it's PostGIS. However, I'm concerned about how old the code being run is. In particular, the library underneath PostGIS, GEOS, had a *lot* of memory work done on it over the last year. I'd like to see if things improve if you upgrade to GEOS 3.2. On Fri, Mar 26, 2010 at 9:04 AM,

[GENERAL] Custom Type Alignment

Simple question on CREATE TYPE (I hope): When using the "alignment" option, and setting it to "double", what ends up double aligned? VARDATA()? The whole thing datum (so that the alignment of VARDATA() is actually conditioned on the size of VARHDRSZ) ? Thanks, Paul -- Sent via pgsql-general ma

Re: [GENERAL] Is there a meaningful benchmark?

http://blog.redfin.com/devblog/2007/11/elephant_versus_dolphin_which_is_faster_which_is_smarter.html Here's another PgSQL/MySQL testimonial, with a spatial twist :) P On Fri, Mar 20, 2009 at 10:15 AM, Greg Smith wrote: > On Fri, 20 Mar 2009, Will Rutherdale (rutherw) wrote: > >> However, keepin

Re: [GENERAL] PostGIS Binary RPM for Red Hat Linux

PgSQL 7.4 is still supported by the latest PostGIS versions. As is PgSQL 8.1 and (when it comes) 8.2. P On 6-Nov-06, at 8:07 AM, Joshua D. Drake wrote: Sandeep Kumar Jakkaraju wrote: Ya... That was my next question ... if i build the source (the tar.gz) on the postgis.org site .. will it be

Re: [GENERAL] PostGIS Binary RPM for Red Hat Linux

Any one you want.  Using the latest won't hurt.On 6-Nov-06, at 11:36 AM, Sandeep Kumar Jakkaraju wrote:What version of Postgis should i install for postgres 8.1.5 ??On 11/7/06, Paul Ramsey <[EMAIL PROTECTED] > wrote:PgSQL 7.4 is still supported by the latest PostGIS versions.As is Pg

[GENERAL] 8.0, UTF8, and CLIENT_ENCODING

eful tools to pipe a stream through to strip out illegal UTF8 bytes, so I can pipe my dump through that rather than hand editing it? Thanks, Paul -- Paul Ramsey Refractions Research http://www.refractions.net [EMAIL PROTECTED] Phone: 250-383-3022 Cell: 250-885-0632 --

Re: [GENERAL] 8.0, UTF8, and CLIENT_ENCODING

Thanks all for the information. Summary is: - 8.0 wasn't very strict, and allowed the illegal values in, instead of mapping them over into UTF-8 space - the values can be stripped with iconv -c - 8.2 should be more strict I'm in the midst of my upgrade to 8.2 now, hopefully the LATIN1->UTF8

Re: [GENERAL] PostgreSQL On the Mac? OS9 or OSX?

Indeed, on OSX. http://www.pgmac.com Israel Evans wrote: > > In looking over the documentation, I haven't seen any evidence of PostgreSQL > working on the Mac. Is this the case, or am I missing something. Has > anyone heard of it ever being used on a mac? > > Thanks, > > ~Israel~ > > -

Re: [GENERAL] Failure to install 7.3.3

devel RPM. Daniel E. Fisher wrote: I get an error during config I have redhat 7.2. readline library and Zlib library not found...they are installed with the latest version how do I fix this? -Dan -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | P

[GENERAL] pg_dump "all tables" in 7.3.X

We are trying to do an "all tables" dump using the 7.3.3 pg_dump, but are getting no love. The pg_dump command which worked before, in 7.2, no longer returns any tables: pg_dump -t "*" dbname Is this by design, or by accident? Paul -- __ / | Paul Ramse

Re: [GENERAL] Upgrading to 7.3.4?

r/local/src/postgresql-7.3.2-- installs new pgsql version make install initdb -D %PGHOME%/data -- start and restore db. pg_ctl start psql < dbackup -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 8

Re: [GENERAL] Simulating sequences

decide to go to something like MySQL, which doesn't even support procedural languages). -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_ ---(end of broadcast)

[GENERAL] Booleans and Casting

an find no function at all to use to cast booleans to what I want, which is 1 = true, 0 = false (IE, the standard cast for boolean -> number). Any advice? -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_

[GENERAL] Support for Geometric Types

found? (I may be able to bring some funding to bear to support this kind of development) Thanks, Paul -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_