Dear all
I am trying to do a tunning to my database, and i have many doubt, because i
think that is very slow (with 3 o 4 users is slow)
I have 2700 tables aprox. with geometry column in my database, the street's
name, big avenues, regional boundaries, street types,comunal areas, etc.
but the
Hei all :)
I have a table where it holds 2 Geometry type columns, this_geom, and
last_geom.
I will be using this table as a reference table where I will either pass
thisgid or this_geom and would like all the other data extracted.
However an EXPLAIN SELECT on a where this_geom =
'010
César;
You need to be a bit more specific about what you mean by "slow". What
operations precisely are slow?
Having said that, in general any columns - spatial or otherwise - to
which you refer in WHERE clauses in your SELECT statements should be
considered for indexing, especially if you use th
il (dot) com
skype: ciesare_medina
> From: chris.herman...@timberline.ca
> To: postgis-users@postgis.refractions.net
> Date: Tue, 29 Dec 2009 11:07:39 -0800
> Subject: Re: [postgis-users] GIST INDEX!!
>
> César;
>
> You need to be a bit more specific about what you mean by
> in Software,
> Linux fedora, php pages, apache, postgresql with postgis, mapserver and
> p.mapper
> I don't know what i have to do, to do it map more fast
Dear Cesar,
using GIST index in geographic database is nearly a must, but still
You can't compare to Google maps, which is using differ
sareMedina (at) hotmail (dot) com skype: ciesare_medina
>
>
>
>
>
> > From: chris.herman...@timberline.ca
> > To: postgis-users@postgis.refractions.net
> > Date: Tue, 29 Dec 2009 11:07:39 -0800
> > Subject: Re: [postgis-users] GIST INDEX!!
> >
> &g
One more advice. If Your tables for every area are the same, I will
suggest You having same tables merged in to one. It is better for
management and maybe You can also gain some performance.
___
postgis-users mailing list
postgis-users@postgis.refractions
t
Asunto: Re: [postgis-users] GIST INDEX!!
Hi Chris
when i say "slow", i refer that is no so fast than googleMaps, extreme
example.!!!
This is a url with one commune,
http://mapas.observatoriourbano.cl/localizacion/map.phtml?config=7101
In this case, we have 11 layer in datab
ombre de César
> Medina
> Enviado el: martes, 29 de diciembre de 2009 17:26
> Para: postgis-users@postgis.refractions.net
> Asunto: Re: [postgis-users] GIST INDEX!!
>
>
>
> Hi Chris
>
>
>
> when i say "slow", i refer that is no so fast than googleMa
2008 3:49 AM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] GiST Index
Hei all :)
I have a table where it holds 2 Geometry type columns, this_geom, and
last_geom.
I will be using this table as a reference table where I will either pass
thisgid or this_geom and would like
On Wednesday 27 February 2008 10:49:07 Matthew Pulis wrote:
> Hei all :)
>
>
>
> I have a table where it holds 2 Geometry type columns, this_geom, and
> last_geom.
>
>
>
> I will be using this table as a reference table where I will either pass
> thisgid or this_geom and would like all the other da
Matthew,
There are a number of parameters that go into a decision to use an
index, as well as the stats on the table. Make sure the stats are up to
date with a VACUUM ANALYSE.
The first step would be to find out if an index scan would actually be
faster. You can disable sequence scans using:
ROTECTED] On Behalf Of Gregory
Williamson
Sent: Wednesday, February 27, 2008 12:21 PM
To: PostGIS Users Discussion; PostGIS Users Discussion
Subject: RE: [postgis-users] GiST Index
Matthew --
Try running
ANALYZE streets_relation;
This provides the statistics to the planner ...
Use the ST_ function
Hey all,
I currently am working on a PostGIS database (Postgres 8.3) and the
requests are going slower than expected.
The table I'm querying off of has ~42000 entries in it, and the geometry
field I'm using has a gist index on it. The query however is taking ~6
seconds to return me the entr
First, basic index concepts: when you are requesting the whole data
set, the index (any index) does *nothing* for your performance. To
use an (obsolete) metaphor, if you are checking out *every* book in
the library, do you first go to the card catalog?
So your test case isn't testing anything ind
to try
and speed up our queries.
- Brian Peck
- 858-795-1398
- Software Engineer
- Lockheed Martin
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Paul
Ramsey
Sent: Wednesday, June 04, 2008 4:21 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users]
Behalf Of Paul
> Ramsey
> Sent: Wednesday, June 04, 2008 4:21 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] GIST index speed
>
> First, basic index concepts: when you are requesting the whole data
> set, the index (any index) does *nothing* for your perform
First, basic index concepts: when you are requesting the whole data
set, the index (any index) does *nothing* for your performance. To
use an (obsolete) metaphor, if you are checking out *every* book in
the library, do you first go to the card catalog?
So your test case isn't testing anything
Are you using within() or ST_Within()? The latter automatically uses the
gist index, the former does not.
-- Kevin
Peck, Brian wrote:
Hey all,
I currently am working on a PostGIS database (Postgres 8.3) and the
requests are going slower than expected.
The table I’m querying off of has ~42
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Paul
Ramsey
Sent: Wednesday, June 04, 2008 4:53 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GIST index speed
Did you compile with debugging on? Doing a ST_Within test on 46000
things against a bbox should not take long at
04, 2008 4:53 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GIST index speed
Did you compile with debugging on? Doing a ST_Within test on 46000
things against a bbox should not take long at all. Something else is
slowing things down. What shows up in your postgresql log files? What
tGIS Users Discussion
Subject: RE: [postgis-users] GIST index speed
As far as I know we did not compile with debugging on, and swapping to
contains did not speed things up. However we are not using ST_Contains()
[just contains()]. These don't exist in the database (either the 8.2 I
am testing
ian Peck
- 858-795-1398
- Software Engineer
- Lockheed Martin
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Gregory Williamson
Sent: Wednesday, June 04, 2008 5:36 PM
To: PostGIS Users Discussion; PostGIS Users Discussion
Subject: RE: [postgis-us
Hi, Paul,
"Paul Ramsey" <[EMAIL PROTECTED]> wrote:
> First, basic index concepts: when you are requesting the whole data
> set, the index (any index) does *nothing* for your performance. To
> use an (obsolete) metaphor, if you are checking out *every* book in
> the library, do you first go to th
Hi, Gregory,
"Gregory Williamson" <[EMAIL PROTECTED]> wrote:
> ST_ functions are modern variants of older functions -- they include the
> bounding box check that eliminates most unwanted candidates from a search.
>
> So try something like:
>
> SELECT * FROM asdfs_track_point where the_geom &&
Hi, Brian,
"Peck, Brian" <[EMAIL PROTECTED]> wrote:
> As far as I know we did not compile with debugging on, and swapping to
> contains did not speed things up. However we are not using ST_Contains()
> [just contains()]. These don't exist in the database (either the 8.2 I
> am testing on - or an
Hi, Kevin,
Kevin Neufeld <[EMAIL PROTECTED]> wrote:
> Are you using within() or ST_Within()? The latter automatically uses the
> gist index, the former does not.
As I was absent from the lists for some time, why was this distinction
introduced?
Normally, I would expect the standards compliant
he same
reason.
http://archives.postgresql.org/pgsql-general/2008-02/msg01224.php
Hope that helps,
Regina
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Markus Schaber
Sent: Thursday, June 05, 2008 5:40 AM
To: postgis-users@postgis.refractions.net
Subject: Re:
Hi, Regina,
"Obe, Regina" <[EMAIL PROTECTED]> wrote:
> [great explanation]
>
> Hope that helps,
Yes, that helped a lot. It seems that my absence from the list (and
PostGIS in general) was just to long. :-(
Regards,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'm seeing this same kind of slowness. I am in the process of migrating
an application from Oracle to Postgres. One query that seems to take
longer than it should is a very simple point-in-bbox query which takes 3
seconds. There are 32000 rows in th
Steve Kondik wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'm seeing this same kind of slowness. I am in the process of migrating
an application from Oracle to Postgres. One query that seems to take
longer than it should is a very simple point-in-bbox query which takes 3
seconds. Ther
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Mark Cave-Ayland wrote:
> Steve Kondik wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> I'm seeing this same kind of slowness. I am in the process of migrating
>> an application from Oracle to Postgres. One query that seems to take
>>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Steve Kondik wrote:
> Mark Cave-Ayland wrote:
>> Steve Kondik wrote:
>>> -BEGIN PGP SIGNED MESSAGE-
>>> Hash: SHA1
>>>
>>> I'm seeing this same kind of slowness. I am in the process of migrating
>>> an application from Oracle to Postgres. One
Steve,
Any chance you could share your data with the developers? this is a
very strange result.
P
On Fri, Jun 6, 2008 at 9:37 AM, Steve Kondik <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Steve Kondik wrote:
>> Mark Cave-Ayland wrote:
>>> Steve Kondik wrote:
>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Paul Ramsey wrote:
> Steve,
>
> Any chance you could share your data with the developers? this is a
> very strange result.
>
> P
>
Sure, I exported the table with pgsql2shp.
http://n0rp.chemlab.org/geography.tbz2
There's nothing spectacular about
I just want to see if I can duplicate the result, having the original
data makes that a faster process, since I can copy your use case
exactly.
P
On Fri, Jun 6, 2008 at 10:53 AM, Steve Kondik <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Paul Ramsey wrote:
>> St
OK, I'm seeing the same problem here...
I can fix it by re-writing your query:
select count(*) from geography a join geography b on (b.the_geom &&
b.centroid) where a.id = 69495 and b.type='Z';
However, reading the profile is interesting... all the time is in
memcpy, something about the way your
18% of the time is spent in heap_tuple_untoast_attr
18% of the time is spent in IndexNext
much of the remaining time is in kernel functions servicing the data
being pushed through the pipeline
it's like something in your query plan ended up forcing the USA
polygon to be read out over and over and o
Interesting side note: removing RECHECK changed the worst performance
(subquery, without the force_2d) from 8000ms to 4000ms. It changed the
performance of the subquery syntax with force_2d from 363ms to 318ms.
It didn't change the performance of the fast joined query syntax
measurably: still 180ms
Steve Kondik wrote:
Adding force_2d speeds this up immensely. Not sure I understand why
this is necessary, but I'm still getting used to PostGIS.
health_central_22=> explain analyze select id,name from geography where
type='Z' and centroid && (select force_2d(geometry) from geography where
id
Mark Cave-Ayland wrote:
*blinks* this is definitely a bug somewhere - there is no way that
adding a function wrapper to a constant should make the query several
orders of magnitude quicker :(
Hmmm. It seems the problem is related to whether or not we copy the
incoming geometry. I've just cre
IMMUTABLE.
I bet when you have the function it's caching the result, and when you
don't, it's re-fetching it. And because the bare result is a toasted
tuple, the re-fetch is a lot more expensive.
P.
On Mon, Jun 9, 2008 at 5:39 AM, Mark Cave-Ayland
<[EMAIL PROTECTED]> wrote:
> Mark Cave-Ayland wr
Paul Ramsey wrote:
IMMUTABLE.
I bet when you have the function it's caching the result, and when you
don't, it's re-fetching it. And because the bare result is a toasted
tuple, the re-fetch is a lot more expensive.
P.
Hi Paul,
If only it were that simple ;) I'd already tried removing the IM
Bummer. Well if you need any other cock-eyed theories, let me know :)
P
On Mon, Jun 9, 2008 at 6:34 AM, Mark Cave-Ayland
<[EMAIL PROTECTED]> wrote:
> Paul Ramsey wrote:
>>
>> IMMUTABLE.
>>
>> I bet when you have the function it's caching the result, and when you
>> don't, it's re-fetching it. And
Paul Ramsey wrote:
Bummer. Well if you need any other cock-eyed theories, let me know :)
P
Hi Paul,
Please feel free to add more: I think it's that we're doing something
strange with palloc/pfree/TOAST, maybe casting, or it's something in the
PostgreSQL index AM. Do you have any success wit
Wondering does this happen on just 8.3 or 8.2 as well.
Thanks,
Regina
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Paul
Ramsey
Sent: Monday, June 09, 2008 10:33 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GIST index speed
Bummer
I am having trouble creating a GiST index on a MULTIPOLYGON column.
When the table is empty, the indexes will be created without errors, but
inserts will fail with the error. When the table has entries in it, the
creation of the index will fail with the same error. The index creation
only fails o
Try without the constraints... just a guess, but it might be the
combination of the index and the unique constraint on geometry.
On 13-Dec-07, at 8:14 AM, Kyle Wilcox wrote:
I am having trouble creating a GiST index on a MULTIPOLYGON column.
When the table is empty, the indexes will be create
On Thu, 2007-12-13 at 11:14 -0500, Kyle Wilcox wrote:
> I am having trouble creating a GiST index on a MULTIPOLYGON column.
> When the table is empty, the indexes will be created without errors, but
> inserts will fail with the error. When the table has entries in it, the
> creation of the index w
I am indeed running PostGIS 1.1.6.
# select postgis_full_version();
postgis_full_version
--
POSTGIS="1.1.6" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22 Oct 2006"
USE_STATS
(1 row)
# select * from pg_proc where pr
Updating the PostGIS 1.3.2 solved the problem, thanks Mark.
Upgrading was surprisingly easy.
Kyle Wilcox wrote:
> I am indeed running PostGIS 1.1.6.
>
> # select postgis_full_version();
>
> postgis_full_version
> --
51 matches
Mail list logo
