Обсуждение: cube_contains and indexing

I am playing around with the cube extension in contrib and was not able to
get cube_contains to use an index. I was able to get (what I believe to be)
an equivalent @ operation (cubement containment) to use indexes.
So I am either confused about there being a @ equivalent to cube_contains
or cube_contains should be an indexable operation but isn't (or at least
is harder to get to use an index).

Bruno Wolff III <bruno@wolff.to> writes:
> I am playing around with the cube extension in contrib and was not able to
> get cube_contains to use an index. I was able to get (what I believe to be)
> an equivalent @ operation (cubement containment) to use indexes.
> So I am either confused about there being a @ equivalent to cube_contains
> or cube_contains should be an indexable operation but isn't (or at least
> is harder to get to use an index).

It says here (cube.sql.in) that the @ operator *is* cube_contains.
Also, AFAICT both @ and ~ (the commutator operator, cube_contained)
should be equally indexable.  Can you show us your test case?

            regards, tom lane

On Sat, Aug 17, 2002 at 21:02:43 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > I am playing around with the cube extension in contrib and was not able to
> > get cube_contains to use an index. I was able to get (what I believe to be)
> > an equivalent @ operation (cubement containment) to use indexes.
> > So I am either confused about there being a @ equivalent to cube_contains
> > or cube_contains should be an indexable operation but isn't (or at least
> > is harder to get to use an index).
>
> It says here (cube.sql.in) that the @ operator *is* cube_contains.
> Also, AFAICT both @ and ~ (the commutator operator, cube_contained)
> should be equally indexable.  Can you show us your test case?
>
>             regards, tom lane

A short example is:
cube=> explain select col1 from c where cube_contains('(-78,39),(-77,40)',col1);NOTICE:  QUERY PLAN:

Seq Scan on c  (cost=0.00..369.30 rows=6195 width=24)

EXPLAIN
cube=> explain select col1 from c where '(-78,39),(-77,40)' @ col1;
NOTICE:  QUERY PLAN:

Index Scan using c_index on c  (cost=0.00..71.87 rows=19 width=24)

If this really looks like a problem I can provide more details.
I am running 7.2.1 with the cube package added on.

Bruno Wolff III <bruno@wolff.to> writes:
>> It says here (cube.sql.in) that the @ operator *is* cube_contains.

> A short example is:
> cube=> explain select col1 from c where cube_contains('(-78,39),(-77,40)',col1);NOTICE:  QUERY PLAN:

> Seq Scan on c  (cost=0.00..369.30 rows=6195 width=24)

> EXPLAIN
> cube=> explain select col1 from c where '(-78,39),(-77,40)' @ col1;
> NOTICE:  QUERY PLAN:

> Index Scan using c_index on c  (cost=0.00..71.87 rows=19 width=24)

Well, yeah.  Indexes work with operators, not with functions.  This is
a consequence of decisions taken a decade ago at Berkeley: the system
catalogs that show what indexes can do connect *operators* to indexes,
not functions to indexes.  Use the operator.

            regards, tom lane

On Sun, Aug 18, 2002 at 12:58:39 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Well, yeah.  Indexes work with operators, not with functions.  This is
> a consequence of decisions taken a decade ago at Berkeley: the system
> catalogs that show what indexes can do connect *operators* to indexes,
> not functions to indexes.  Use the operator.

Thanks for the explanation of what was happenning.