Thread: spgist index not getting used

spgist index not getting used

From
Paul Ramsey
Date:
Hi all,
I continue to bang along towards a binding of the spgist api from a run-time extension (postgis, in this case).
To avoid complication, I am actually not doing any postgis code at this point, just copying the internal point quadtree implementation and seeing if I can get it to turn over.

My C implementation is here https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.c
My SQL binding calls are here https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.sql

Thanks to help from Andres Freund, I can now build an index based on my extension. However, when I run a query using the operator(s) I have defined, the query never uses my index, it always sequence scans.

explain analyze select * from somepoints where '(5898.82450178266,7990.24286679924)'::point = pt;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on somepoints  (cost=0.00..1887.00 rows=100 width=20) (actual time=26.675..26.675 rows=0 loops=1)
   Filter: ('(5898.82450178266,7990.24286679924)'::point = pt)
   Rows Removed by Filter: 100000
 Total runtime: 26.743 ms


If I build an index on the same table using the internal quad-tree ops, and use their operator, I do get an index scan.

The database is recognizing that the index is there, and if I put a breakpoint on the spgist ‘config’ API function, I see it getting turned over as the query starts and the planner looks at things, but none of the other hooks get called, and the plan ends up being a sequence scan every time.

So, the system knows the index exists, it just thinks it is irrelevant to the query. The system also knows my operators exist, and uses them (in sequence scan mode). But even though they are bound into strategies declared for the operator class, the index is not getting used.

I’ve poked around looking at all the places I can in the system catalogue to try and find out what might differ between my index and the internal quad-tree, but no luck so far: they seem to be defined exactly the same.

Presumably I’ve again forgotten something simple-yet-crucial in my attempt to bind this access method to the point type: any suggestions for fixes or at least diagnostics I can run to get more clues?

Thanks,

P


-- 
Paul Ramsey
http://cleverelephant.ca
http://postgis.net

Re: spgist index not getting used

From
Peter Geoghegan
Date:
On Wed, Sep 24, 2014 at 2:01 PM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:
> If I build an index on the same table using the internal quad-tree ops, and
> use their operator, I do get an index scan.


What about when enable_seqscan = off?

--
Regards,
Peter Geoghegan


Re: spgist index not getting used

From
Paul Ramsey
Date:
Still no go. I actually tried a bunch of different selectivity functions too, and the planner correctly used them to
estimatethe number of potential returned functions, but in no case did the index actually kick in, no matter how
selectiveI made the operator appear.  

P.


--
http://postgis.net
http://cleverelephant.ca


On September 24, 2014 at 4:32:35 PM, Peter Geoghegan (peter.geoghegan86@gmail.com) wrote:
> On Wed, Sep 24, 2014 at 2:01 PM, Paul Ramsey wrote:
> > If I build an index on the same table using the internal quad-tree ops, and
> > use their operator, I do get an index scan.
>
>
> What about when enable_seqscan = off?
>
> --
> Regards,
> Peter Geoghegan
>



Re: spgist index not getting used

From
Tom Lane
Date:
Paul Ramsey <pramsey@cleverelephant.ca> writes:
> My C implementation is here https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.c
> My SQL binding calls are here https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.sql

> Thanks to help from Andres Freund, I can now build an index based on my extension. However, when I run a query using
theoperator(s) I have defined, the query never uses my index, it always sequence scans. 

> explain analyze select * from somepoints where '(5898.82450178266,7990.24286679924)'::point = pt;

(I assume that's a typo and you meant "... &=& pt")

As stated, this WHERE clause cannot be used with the index: indexable
clauses in PG are always of the form "indexed_column operator something",
and you've written it the other way round.  I gather that you think the
operator is commutative; but since you didn't declare that, the planner
doesn't know it can flip the clause around.  Try adding "commutator = &=&"
to the declaration of the "point &=& point" operator.

            regards, tom lane


Re: spgist index not getting used

From
Paul Ramsey
Date:
Yep, that was a typo (or, rather, an unpushed commit). And yep, the lack of a commutator was the problem.  Thanks so
much,it’s a huge relief to see it turning over properly :) now, onwards to actually doing the PostGIS implementation. 

(On an semi-related note, if the spgist example had been in contrib, so it had to show a working instance of the SQL
definitionsof the operators and operator classes it would have been much easier to get started. For an API like spgist
thatis really meant for extension, having the example in contrib rather than core would help a lot.) 

Thanks again Tom!

P.


--
http://postgis.net
http://cleverelephant.ca


On September 24, 2014 at 8:24:02 PM, Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Paul Ramsey writes:
> > My C implementation is here https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.c
> > My SQL binding calls are here https://github.com/pramsey/postgis/blob/spgist/postgis/gserialized_spgist_2d.sql
>
> > Thanks to help from Andres Freund, I can now build an index based on my extension. However,
> when I run a query using the operator(s) I have defined, the query never uses my index,
> it always sequence scans.
>
> > explain analyze select * from somepoints where '(5898.82450178266,7990.24286679924)'::point
> = pt;
>
> (I assume that's a typo and you meant "... &=& pt")
>
> As stated, this WHERE clause cannot be used with the index: indexable
> clauses in PG are always of the form "indexed_column operator something",
> and you've written it the other way round. I gather that you think the
> operator is commutative; but since you didn't declare that, the planner
> doesn't know it can flip the clause around. Try adding "commutator = &=&"
> to the declaration of the "point &=& point" operator.
>
> regards, tom lane
>