Обсуждение: 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.