Thread: force index problem in 8.4.1
Hi there,
I tried to force query to use index by specifying high execution cost, but
without success, even seqscan cost doesn't changed. This is 8.4.1
=# explain select count(*) from spots where coordinates <@ '(0,0),(0.1,0.1)'::box; QUERY
PLAN
-------------------------------------------------------------------- Aggregate (cost=26620.84..26620.85 rows=1
width=0) -> Seq Scan on spots (cost=0.00..25328.12 rows=517085 width=0) Filter: (coordinates <@
'(0.1,0.1),(0,0)'::box)
(3 rows)
Time: 1.944 ms
=# alter FUNCTION pt_contained_box(point, box) COST 10000;
ALTER FUNCTION
Time: 369.800 ms
=# explain select count(*) from spots where coordinates <@ '(0,0),(0.1,0.1)'::box; QUERY
PLAN
-------------------------------------------------------------------- Aggregate (cost=26620.84..26620.85 rows=1
width=0) -> Seq Scan on spots (cost=0.00..25328.12 rows=517085 width=0) Filter: (coordinates <@
'(0.1,0.1),(0,0)'::box)
(3 rows)
Time: 0.891 ms
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> I tried to force query to use index by specifying high execution cost, but
> without success, even seqscan cost doesn't changed. This is 8.4.1
I think you altered the wrong function. The function underlying
point <@ box is on_pb() not pt_contained_box ... in fact, I don't
even see a function named that.
regards, tom lane
On Wed, 25 Nov 2009, Tom Lane wrote:
> Oleg Bartunov <oleg@sai.msu.su> writes:
>> I tried to force query to use index by specifying high execution cost, but
>> without success, even seqscan cost doesn't changed. This is 8.4.1
>
> I think you altered the wrong function. The function underlying
> point <@ box is on_pb() not pt_contained_box ... in fact, I don't
> even see a function named that.
sorry, I forgot to say, that I created operator
CREATE OR REPLACE FUNCTION pt_contained_box(point, box) RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE
'C' IMMUTABLE RETURNS NULL ON NULL INPUT;
CREATE OPERATOR <@ ( LEFTARG = point, RIGHTARG = box, PROCEDURE = pt_contained_box,
COMMUTATOR= '@>', RESTRICT = contsel, JOIN = contjoinsel
);
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> On Wed, 25 Nov 2009, Tom Lane wrote:
>> I think you altered the wrong function. The function underlying
>> point <@ box is on_pb() not pt_contained_box ... in fact, I don't
>> even see a function named that.
> sorry, I forgot to say, that I created operator
Unless you changed search_path, I think the parser would have preferred
the built-in point <@ box operator to one in "public". Are you sure
your code was being used at all?
regards, tom lane
On Thu, 26 Nov 2009, Tom Lane wrote:
> Oleg Bartunov <oleg@sai.msu.su> writes:
>> On Wed, 25 Nov 2009, Tom Lane wrote:
>>> I think you altered the wrong function. The function underlying
>>> point <@ box is on_pb() not pt_contained_box ... in fact, I don't
>>> even see a function named that.
>
>> sorry, I forgot to say, that I created operator
>
> Unless you changed search_path, I think the parser would have preferred
> the built-in point <@ box operator to one in "public". Are you sure
> your code was being used at all?
oops, you were right. Sorry, for noise.
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83