[postgis-devel] Re: [postgis-users] GIST index speed

[Mark Cave-Ayland]

Tom Lane wrote:

> Hm, this is with that patch in place, right?  Because it's showing only
> 87 msec spent inside the indexscan proper, which wouldn't square with
> our theories if there were multiple detoastings going on in there.
> Can you show the same case without the patch?

Yes, that's correct. Here are the same results without the patch in place:

(Note I've also added an extra case at the bottom to show that the 
de-TOASTing also benefits plain sequential scans)


postgis=# set enable_indexscan='f';
SET
postgis=# explain analyze select id,name from geography where type='Z' and
postgis-# centroid && (select the_geom from geography where id=69495);
                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on geography  (cost=7096.26..7100.28 rows=1 width=14) 
(actual time=1772.346..3610.121 rows=29687 loops=1)
    Filter: ((centroid && $0) AND ((type)::text = 'Z'::text))
    InitPlan
      ->  Seq Scan on geography  (cost=0.00..7092.00 rows=1 width=4503) 
(actual time=23.373..56.344 rows=1 loops=1)
            Filter: (id = 69495::numeric)
    ->  Bitmap Index Scan on geography_centroid_index  (cost=0.00..4.26 
rows=1 width=0) (actual time=1770.802..1770.802 rows=32880 loops=1)
          Index Cond: (centroid && $0)
  Total runtime: 3646.019 ms
(8 rows)

postgis=# set enable_bitmapscan = 'f';
SET
postgis=# explain analyze select id,name from geography where type='Z' and
postgis-# centroid && (select the_geom from geography where id=69495);
                                                     QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
  Seq Scan on geography  (cost=7092.00..14266.20 rows=1 width=14) 
(actual time=53.867..1852.810 rows=29687 loops=1)
    Filter: ((centroid && $0) AND ((type)::text = 'Z'::text))
    InitPlan
      ->  Seq Scan on geography  (cost=0.00..7092.00 rows=1 width=4503) 
(actual time=25.730..53.595 rows=1 loops=1)
            Filter: (id = 69495::numeric)
  Total runtime: 1889.969 ms
(6 rows)

postgis=# explain analyze select id,name from geography where type='Z' and
centroid && (select mcatest(the_geom) from geography where id=69495);
                                                     QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
  Seq Scan on geography  (cost=7092.00..14266.20 rows=1 width=14) 
(actual time=54.452..161.947 rows=29687 loops=1)
    Filter: ((centroid && $0) AND ((type)::text = 'Z'::text))
    InitPlan
      ->  Seq Scan on geography  (cost=0.00..7092.00 rows=1 width=4503) 
(actual time=26.126..54.133 rows=1 loops=1)
            Filter: (id = 69495::numeric)
  Total runtime: 209.119 ms
(6 rows)


> Ugh.  Is that to be expected given the particular data involved here,
> or does it suggest there's some larger problem with indexing of &&
> queries?

Yeah that's fine with this dataset - it appears to be the type field 
which is the main filter for this query.

> I wonder if it'd be sane for the TOAST code to keep a small lookaside
> cache of recently-detoasted values ...

Sounds like it could be a plan. I suspect that the caching will be the 
easy bit, whereas as managing the entry lifetimes will be hard.


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063