Re: Another try at reducing repeated detoast work for PostGIS

Tom Lane wrote:

> Huh.  As far as I can see this example should traverse the same code
> path.  I was about to ask for the dataset, but I think you might have
> already sent it to me once --- does this look familiar?
> 
> $ tar tvfj geography.tar.bz2
> -rw-r--r-- shade/shade 6444737 2008-06-06 13:33 geography.dbf
> -rw-r--r-- shade/shade 37179008 2008-06-06 13:33 geography.shp
> -rw-r--r-- shade/shade   263140 2008-06-06 13:33 geography.shx
> 
> If so, what do I do with it exactly --- the file extensions convey
> nothing to my mind at the moment ...

Okay. I've gone back and had a look at the original queries, and it 
seems the reason for the inflated times is that my setup for the 
original database was based on PostGIS 1.3, which has a RECHECK applied 
to the && operator.

If I temporarily remove the RECHECK from PostGIS 1.3 then the times drop 
substantially:


postgis13=# explain analyze select count(*) from geography where 
type='Z' and centroid && (select the_geom from geography where id=69495);
QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------
--------- Aggregate  (cost=6892.28..6892.29 rows=1 width=0) (actual 
time=394.183..394.184 rows=1 loops=1)   InitPlan 1 (returns $0)     ->  Seq Scan on geography  (cost=0.00..6884.00
rows=1width=4432) 
 
(actual time=18.192..41.855 rows=1 loops=1)           Filter: (id = 69495::numeric)   ->  Index Scan using
idx_geography_centroid_zon geography 
 
(cost=0.00..8.28 rows=1 width=0) (actual time=46.711..345.940 rows=29687
loops=1)         Index Cond: (centroid && $0)         Filter: ((type)::text = 'Z'::text) Total runtime: 394.265 ms
(8 rows)


Incidentally, the recently-released PostGIS 1.4 has RECHECK disabled by 
default, and so it can be seen that the times are reasonably similar:


postgis14=# explain analyze select count(*) from geography where 
type='Z' and centroid && (select the_geom from geography where id=69495);
QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------
--------- Aggregate  (cost=6892.28..6892.29 rows=1 width=0) (actual 
time=396.314..396.315 rows=1 loops=1)   InitPlan 1 (returns $0)     ->  Seq Scan on geography  (cost=0.00..6884.00
rows=1width=4439) 
 
(actual time=14.198..37.340 rows=1 loops=1)           Filter: (id = 69495::numeric)   ->  Index Scan using
idx_geography_centroid_zon geography 
 
(cost=0.00..8.28 rows=1 width=0) (actual time=42.169..344.337 rows=29687
loops=1)         Index Cond: (centroid && $0)         Filter: ((type)::text = 'Z'::text) Total runtime: 396.375 ms
(8 rows)


If I re-apply your patch to PostgreSQL 8.4 using PostGIS 1.4 (ignoring 
RECHECK) then the results now look like this:


postgis14=# explain analyze select count(*) from geography where 
type='Z' and centroid && (select the_geom from geography where id=69495);
QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------
--------- Aggregate  (cost=6892.28..6892.29 rows=1 width=0) (actual 
time=271.360..271.362 rows=1 loops=1)   InitPlan 1 (returns $0)     ->  Seq Scan on geography  (cost=0.00..6884.00
rows=1width=4439) 
 
(actual time=17.534..32.009 rows=1 loops=1)           Filter: (id = 69495::numeric)   ->  Index Scan using
idx_geography_centroid_zon geography 
 
(cost=0.00..8.28 rows=1 width=0) (actual time=32.393..165.057 rows=29687
loops=1)         Index Cond: (centroid && $0)         Filter: ((type)::text = 'Z'::text) Total runtime: 271.428 ms
(8 rows)

postgis14=# explain analyze select count(*) from geography where 
type='Z' and centroid && (select force_2d(the_geom) from geography where 
id=69495);
QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------
--------- Aggregate  (cost=6892.28..6892.29 rows=1 width=0) (actual 
time=272.091..272.092 rows=1 loops=1)   InitPlan 1 (returns $0)     ->  Seq Scan on geography  (cost=0.00..6884.00
rows=1width=4439) 
 
(actual time=18.644..42.680 rows=1 loops=1)           Filter: (id = 69495::numeric)   ->  Index Scan using
idx_geography_centroid_zon geography 
 
(cost=0.00..8.28 rows=1 width=0) (actual time=43.079..172.788 rows=29687
loops=1)         Index Cond: (centroid && $0)         Filter: ((type)::text = 'Z'::text) Total runtime: 272.185 ms
(8 rows)


So in conclusion, I think that patch looks good and that the extra time 
I was seeing was due to RECHECK being applied to the && operator, and 
not the time being spent within the index scan itself.


ATB,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs