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

[Tom Lane]

"Mark Cave-Ayland" <mark.cave-ayland at siriusit.co.uk> writes:
> postgis=# set enable_indexscan='f';
> SET
> postgis=# explain analyze select id,name from geography where type='Z' and
> 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=89.458..1949.395 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.520..70.221 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=87.842..87.842 rows=32880 loops=1)
>          Index Cond: (centroid && $0)
>  Total runtime: 1985.376 ms
> (8 rows)

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?

> and in fact the index is returning every one of the 32880 rows and passing
> them through the filter.

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?

>> So apparently we need a more general solution.  Not sure what offhand.

> So it almost seems we need some sort of logic at scan level, perhaps
> something  along the lines of: if a TOASTed datum is referenced within a
> qualification higher up in the plan tree, de-TOAST the datum just before
> the tuple is returned as part of the scan.

Getting that right would be tricky though.  For example, if the datum
has to pass through a Sort node before it gets used, early detoasting
would be counterproductive because it'd increase the volume of data
to be sorted.

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

			regards, tom lane