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

[Paul Ramsey]

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".