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
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
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:
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
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
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
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
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
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
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
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
-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
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
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
-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
>>
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
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
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.
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, 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
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, 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, 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
5:22 PM
To: PostGIS 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
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
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
[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
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
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
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
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]
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
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
33 matches
Mail list logo
