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.1I 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