Thread: GIST operators docs [was: [HACKERS] Patch: add GiST support for BOX @> POINT queries]

On Sat, Jul 9, 2011 at 3:29 PM, Hitoshi Harada <umi.tanuki@gmail.com> wrote:

> It looks like the time to wrap up. I marked "Return with Feedback" on
> this patch, since response from author has not come for a while.

Hello,

I've just stumbled on the issue mentioned in this thread, which is not
fixed in 9.1. To my knowledge the box @> point index limitation is not
described anywhere in the docs (which has costed me quite some time in
debug and the redesign of several functions to work around the issue).

Wouldn't be awesome to document what are the types/operators
combinations that can be successfully used in gist indexes?

The style of the docs is currently:

- in 11.2: on the type page, a list of operators without a single word
on their meaning, with a link to 9.11
<http://www.postgresql.org/docs/9.1/static/indexes-types.html>
- in 9.11 the ops explanation with no info about indexing or types
support <http://www.postgresql.org/docs/9.1/static/functions-geometry.html>

I would suggest dropping the list in 11.2, leaving only the link
("several operators support indexing: see section 9.11 for a list"),
and be explicit in 9.11 in what operator and what data type can be
used in an index. I regularly need two browser tabs open to consult
these docs, bouncing back and forth.

It's worth noting that @> is listed in the indexed operators but the
only example provided for it in 9.11 is circle @> point which is
exactly one of the combinations not working with the index. Once ops
and index support info are on the same page, the extra notes about the
supported data types would feel like at home there.

I'm new to them, but I suspect the range operators for 9.2 have the
same docs usability issue (operator and index support on two different
page).

I don't know what the operators limitations are, and you have a few
samples of my English. Modulo that, I can provide patches if you want
:)

-- Daniele

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> The style of the docs is currently:

> - in 11.2: on the type page, a list of operators without a single word
> on their meaning, with a link to 9.11
> <http://www.postgresql.org/docs/9.1/static/indexes-types.html>
> - in 9.11 the ops explanation with no info about indexing or types
> support <http://www.postgresql.org/docs/9.1/static/functions-geometry.html>

I agree this is pretty incomplete ...

> I would suggest dropping the list in 11.2, leaving only the link
> ("several operators support indexing: see section 9.11 for a list"),
> and be explicit in 9.11 in what operator and what data type can be
> used in an index.

... but I don't think I want to fix it along those lines.  Consider
what would happen if we tried to annotate every operator supported by
btree indexes that way, for example.  It'd be very cluttering.  It'd
probably be pretty cluttering even if we restrict it to GIST cases.
I think the charter of 9.11 is to tell you what the operators *do*,
not which ones are amenable to indexing.

Over in 11.9
http://www.postgresql.org/docs/devel/static/indexes-opclass.html
there are sample queries for finding out which operators are indexable
by consulting the system catalogs directly.  I wonder whether it'd be
helpful to provide some simplified version of those queries in 11.2,
instead of or in addition to the text that's there now.

Alternatively, I could see adding tables of supported operators in
the GIST (and SPGIST and GIN) chapters of part VII, and linking to
those from 11.2.

Another idea is to extend psql's \do command to report indexability
of the operators it shows.

            regards, tom lane

On Sat, Aug 11, 2012 at 6:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
>> The style of the docs is currently:
>
>> - in 11.2: on the type page, a list of operators without a single word
>> on their meaning, with a link to 9.11
>> <http://www.postgresql.org/docs/9.1/static/indexes-types.html>
>> - in 9.11 the ops explanation with no info about indexing or types
>> support <http://www.postgresql.org/docs/9.1/static/functions-geometry.html>
>
> I agree this is pretty incomplete ...
>
>> I would suggest dropping the list in 11.2, leaving only the link
>> ("several operators support indexing: see section 9.11 for a list"),
>> and be explicit in 9.11 in what operator and what data type can be
>> used in an index.
>
> ... but I don't think I want to fix it along those lines.  Consider
> what would happen if we tried to annotate every operator supported by
> btree indexes that way, for example.  It'd be very cluttering.

Agreed it would be cluttering for btree, but btree operators are
pretty well known and surprise-free, whereas the geometric/interval
ones are quite exotic and the interaction between types not obvious.

> It'd
> probably be pretty cluttering even if we restrict it to GIST cases.

There would be more information, but I don't think a useful page is an
impossible task. What I have in mind is a notes column and free
descriptive text below the table as in
<http://docs.python.org/library/stdtypes.html#index-15>.

> I think the charter of 9.11 is to tell you what the operators *do*,
> not which ones are amenable to indexing.

In the context of using intervals or geometric types to build indexes
one must design his system with this knowledge. He can only use the
operators that support indexes, the others are out of the game.

> Alternatively, I could see adding tables of supported operators in
> the GIST (and SPGIST and GIN) chapters of part VII, and linking to
> those from 11.2.

That would be probably fine too. The other propositions (extending
psql's \do and a query to be copypasted into psql) are still a form of
help but require another tool to be used out of the Fine Manual.

-- Daniele

Is there a TODO here?

---------------------------------------------------------------------------

On Tue, Aug 14, 2012 at 06:40:58PM +0100, Daniele Varrazzo wrote:
> On Sat, Aug 11, 2012 at 6:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> >> The style of the docs is currently:
> >
> >> - in 11.2: on the type page, a list of operators without a single word
> >> on their meaning, with a link to 9.11
> >> <http://www.postgresql.org/docs/9.1/static/indexes-types.html>
> >> - in 9.11 the ops explanation with no info about indexing or types
> >> support <http://www.postgresql.org/docs/9.1/static/functions-geometry.html>
> >
> > I agree this is pretty incomplete ...
> >
> >> I would suggest dropping the list in 11.2, leaving only the link
> >> ("several operators support indexing: see section 9.11 for a list"),
> >> and be explicit in 9.11 in what operator and what data type can be
> >> used in an index.
> >
> > ... but I don't think I want to fix it along those lines.  Consider
> > what would happen if we tried to annotate every operator supported by
> > btree indexes that way, for example.  It'd be very cluttering.
>
> Agreed it would be cluttering for btree, but btree operators are
> pretty well known and surprise-free, whereas the geometric/interval
> ones are quite exotic and the interaction between types not obvious.
>
> > It'd
> > probably be pretty cluttering even if we restrict it to GIST cases.
>
> There would be more information, but I don't think a useful page is an
> impossible task. What I have in mind is a notes column and free
> descriptive text below the table as in
> <http://docs.python.org/library/stdtypes.html#index-15>.
>
> > I think the charter of 9.11 is to tell you what the operators *do*,
> > not which ones are amenable to indexing.
>
> In the context of using intervals or geometric types to build indexes
> one must design his system with this knowledge. He can only use the
> operators that support indexes, the others are out of the game.
>
> > Alternatively, I could see adding tables of supported operators in
> > the GIST (and SPGIST and GIN) chapters of part VII, and linking to
> > those from 11.2.
>
> That would be probably fine too. The other propositions (extending
> psql's \do and a query to be copypasted into psql) are still a form of
> help but require another tool to be used out of the Fine Manual.
>
> -- Daniele
>
>
> --
> Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-docs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +