[postgis-devel] Geography selectivity now hopefully debugged...

Paul Ramsey pramsey at opengeo.org
Fri Oct 16 15:08:15 PDT 2009


Had a thought, which I can confirm later, that the problem is not in
the access code, but in the structure it's accessing. If the index
structure sucks, if my page splits are no damn good, if it's having to
test far more pages, then that would account for the extra time
necessary to carry out the overall query.

P.

On Fri, Oct 16, 2009 at 2:49 PM, Paul Ramsey <pramsey at opengeo.org> wrote:
> Well, I ran both queries in the Shark, but the profiles aren't all that
> helpful. The time seems to be being spent in untoasting the required data.
> But in both cases I am using my "peek in the front" method of un-toasting a
> slice.  The only difference is that the lwgeom implementation only has to
> untoast 21 bytes, while geography has to untoast 32. Both seem to be calling
> lz decompression as part of the path.
>
> I'm honestly seeing nothing but the difference in size of the things being
> detoasted... hm.
>
> Just did an experiment and increased the size of the data slice lwgeom peaks
> at, made it 32 bytes just like geography, but it didn't change things.
>
> But the profiles look like the main difference is the amount of time in lz
> decompress, which would seem to indicate that more data is being pulled
> through the pipe in the geography case than in the lwgeom case. But I can't
> see why, unless it's happening for some other unclear reason. Both types are
> marked as storage "main". Hm.
>
> And the geography code is definitely peaking not pulling the whole toasted
> object, since that part of the code is totally code in the profile.
>
> Very confused indeed.
>
> P
>
> On 2009-10-16, at 9:47 AM, Mark Cave-Ayland wrote:
>
>> However, the one thing to notice is that the geography code is
>> considerably slower (over 4 times!) than the existing geometry code:
>>
>>
>> pramsey=# explain analyze select count(1) from "public"."buildings" t1,
>> "public"."streets" t2 WHERE t1."geog" &&  t2."geog";
>> QUERY PLAN
>>
>> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>> Aggregate  (cost=49909.12..49909.13 rows=1 width=0) (actual
>> time=180191.516..180191.517 rows=1 loops=1)
>>  ->  Nested Loop  (cost=0.00..49067.07 rows=336820 width=0) (actual
>> time=2.780..180037.184 rows=113481 loops=1)
>>       ->  Seq Scan on streets t2  (cost=0.00..1932.82 rows=17182
>> width=3285) (actual time=0.057..156.968 rows=17182 loops=1)
>>       ->  Index Scan using buildings_the_geog_gist on buildings t1
>> (cost=0.00..2.73 rows=1 width=779) (actual time=6.647..10.446 rows=7
>> loops=17182)
>>             Index Cond: (t1.geog && t2.geog)
>> Total runtime: 180191.800 ms
>> (6 rows)
>>
>> pramsey=# explain analyze select count(1) from "public"."buildings" t1,
>> "public"."streets" t2 WHERE t1."geom" &&  t2."geom";
>> QUERY PLAN
>>
>> --------------------------------------------------------------------------------------------------------------------------------------------------------
>> Aggregate  (cost=47166.63..47166.64 rows=1 width=0) (actual
>> time=38491.772..38491.773 rows=1 loops=1)
>>  ->  Nested Loop  (cost=0.00..46320.32 rows=338523 width=0) (actual
>> time=1.264..38326.564 rows=136359 loops=1)
>>       ->  Seq Scan on streets t2  (cost=0.00..1932.82 rows=17182
>> width=3229) (actual time=0.034..47.689 rows=17182 loops=1)
>>       ->  Index Scan using buildings_the_geom_gist on buildings t1
>> (cost=0.00..2.57 rows=1 width=707) (actual time=1.687..2.205 rows=8
>> loops=17182)
>>             Index Cond: (t1.geom && t2.geom)
>> Total runtime: 38492.507 ms
>> (6 rows)
>>
>>
>> Any ideas on this?
>
> --
> Paul Ramsey
> OpenGeo - http://opengeo.org
> Putting the "Po" in "PostGIS".
>
>



More information about the postgis-devel mailing list