It's mostly a matter of being globally careful about not reading
things into memory that you don't need. For 2.0 we're just going to
use the new index structure, so the 2D index will use the same
machinery as the geography index.
P.
On Tue, Jun 1, 2010 at 12:03 PM, Martin Davis wrote:
>
>
> Paul
Paul Ramsey wrote:
FYI, it is not the index that is slower, it is the op. The index is
actually (surprisingly) faster.
Wow, that's unexpected. Do you know why this is? Is there a message
there for the current 2D GIST index?
--
Martin Davis
Senior Technical Architect
Refractions Research,
Bounding cube you mean? Could do. The problem with that is, it turns
out that computing the bounding cube is actually the most
computationally intensive part of the geography code! So we don't
really want to do that for every edge of a shape. My approach is to
use "bounding circles" for each edge.
Perhaps it could use an in-memory bounding prism index? You're using a
disk-based one used for geography types, right?
Paul Ramsey wrote:
On Mon, May 31, 2010 at 10:27 PM, Paragon Corporation wrote:
On that thought. Remember how geometry intersects performance significantly
increased wi
On Mon, May 31, 2010 at 10:27 PM, Paragon Corporation wrote:
> On that thought. Remember how geometry intersects performance significantly
> increased with prepared geometry algorithm, are we using that same kind of
> prepared geometry logic for geography.
No, we are not. The algorithm is curre
-
> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Paul Ramsey
> *Sent:* Monday, May 31, 2010 9:20 PM
>
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] No index usage on geography
Ramsey
Sent: Monday, May 31, 2010 9:20 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?
FYI, it is not the index that is slower, it is the op. The index is actually
(surprisingly) faster.
P
On May 31, 2010, at 5:06 PM, Nicholas Bower wrote
rom: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf
Of Nicholas
> Bower
> Sent: Sunday, May 30, 2010 7:38 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] No index usage on geography query plan?
>
>
oun...@postgis.refractions.net
> > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
> Nicholas
> > Bower
> > Sent: Sunday, May 30, 2010 7:38 PM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] No index usage on geography query pla
t;
>
>
> From: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Nicholas
> Bower
> Sent: Sunday, May 30, 2010 7:38 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users]
Sent: Sunday, May 30, 2010 7:38 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?
Well the index says it is being used, however I'm still quite suspicious
because of performance results below.
I attach 3 versions of a simply query (
Well the index says it is being used, however I'm still quite suspicious
because of performance results below.
I attach 3 versions of a simply query (Geography ST_Intersects, Geometry
ST_Intersects, Geography &&) which is a simple square ROI intersection over
150k rows, each having a single polygo
o: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?
Right you are.
wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
border && ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373
ID=4326;POLYGON((116.751709 -31.381779,116.883545
> -32.676373,114.741211 -32.796510,114.796143-31.316101,116.751709
> -31.381779))'));
>
> Thanks
> Regina and Leo
> http://www.postgis.us
> --
> *From:* postgis-users-boun...@postgis.refractions.net [mailto:
> postgi
Paragon Corporation wrote:
Nick,
Okay we are seeing the same issue with our fastfoods data even with
smaller windows. I think the clue is the plan here.
The ST_Intersects geography function seems to be treated as a
blackbox rather than a transparent function composed of && and _ST_Distance
Paragon Corporation wrote:
Mark,
He did include it in an earlier email
wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.3161
et] On Behalf Of Nicholas
Bower
Sent: Wednesday, May 26, 2010 6:42 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?
That does look like a pretty huge bounding polygon, but the geography we
agree should still be using the spatial index, so prob
> That does look like a pretty huge bounding polygon, but the geography we
> agree should still be using the spatial index, so probably making the index
> cost higher than it should
>
Fyi the border values are are simply composed of a regular 20km grid of
ajoining polygon squares covering Australi
//www.postgis.us
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Wednesday, May 26, 2010 8:44 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geograp
Nick Bower wrote:
Try making a copy of your wastac.t_tile_geometry_old table but with a
geography instead of geometry column for border, and you should see an
improvement.
That's precisely what I showed in the original post - geography
intersecting geography column. See the table def. I was
Try making a copy of your wastac.t_tile_geometry_old table but with
a geography instead of geometry column for border, and you should
see an improvement.
That's precisely what I showed in the original post - geography
intersecting geography column. See the table def. I was outlining in
Nicholas Bower wrote:
But simply swapping the query region above from geometry to geography
we're back to no index usage,
explain analyze select count(*) from wastac.t_tile_geometry_old where
ST_Intersects(border,
ST_GeographyFromText('SRID=4316;POLYGON((116.751709
-31.381779,116.883545 -32
>
> What does the output of:
>
> SELECT version(), postgis_full_version();
>
> return?
>
PostgreSQL 8.4.3 on x86_64-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3
(csl-sol210-3_4-branch+sol_rpath), 64-bit | POSTGIS="1.5.1"
GEOS="3.2.1-CAPI-1.6.1" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.6.2
Nicholas Bower wrote:
Perhaps this makes it more obvious - 9s to query a table of just 1.3M
rows with ST_Intersects and 20ms using &&.
explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.8
Perhaps this makes it more obvious - 9s to query a table of just 1.3M rows
with ST_Intersects and 20ms using &&.
explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -
On 25 May 2010 22:35, Mark Cave-Ayland wrote:
> Nicholas Bower wrote:
>
> Neither of the ST_Intersects clauses below invoke index usage according to
>> explain output, despite docs saying they should automatically be doing bbox
>> on the index;
>>
>
> (cut)
>
>
> What's going on - the difference
Nicholas Bower wrote:
Neither of the ST_Intersects clauses below invoke index usage according
to explain output, despite docs saying they should automatically be
doing bbox on the index;
(cut)
What's going on - the difference in total cost above proves to me the
indexes are not being used.
Neither of the ST_Intersects clauses below invoke index usage according to
explain output, despite docs saying they should automatically be doing bbox
on the index;
explain analyze SELECT wastac.t_swath_metadata.swath_id AS
wastac_t_swath_metadata_swath_id
FROM wastac.t_swath_metadata
JOIN wast
28 matches
Mail list logo
