[GENERAL] How to get RTREE performance from GIST indexing?

gt; c.srcid; It is this latter query, involving the && operator to find where two rectangular boxes overlap, which seems to be taking the huge amount of time. Is there a way of forcing the use of Rtree indexing in v8.4, or any other work-around? Regards -- Clive Page Dept of Physics & A

[GENERAL] How to get RTREE performance from GIST index?

gt; c.srcid; It is this latter query, involving the && operator to find where two rectangular boxes overlap, which seems to be taking the huge amount of time. Is there a way of forcing the use of Rtree indexing in v8.4, or any other work-around? Regards -- Clive Page Dept of Physics & A

Re: [GENERAL] How to get RTREE performance from GIST index?

or R-tree indexing seriously. I would have thought that geometric queries such as the ones that I've been doing would be more and more important in the real world. -- Clive Page Dept of Physics & Astronomy, University of Leicester, Leicester, LE1 7RH, U.K. -- Sent via pgsql-general

Re: [GENERAL] How to get RTREE performance from GIST index?

the Rtree code could not have been left in there, for those who found that the new-fangled GIST indexing did not work. Regards -- Clive Page -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to get RTREE performance from GIST index?

. Regards -- Clive Page -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to get RTREE performance from GIST index?

ban Thanks - I didn't know that. I'll try removing the TEMPORARY tag. Is it documented somewhere that I should have seen? Regards -- Clive Page -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to get RTREE performance from GIST index?

on, (3::double precision * (a.poserr + b.poserr) -> Seq Scan on pos a (cost=0.00..8032.83 rows=351983 width=68) -> Index Scan using pos_errbox on pos b (cost=0.00..31.27 rows=1760 width=68) Index Cond: (a.errbox && b.errbox) (5 rows) Actual timing using

Re: [GENERAL] How to get RTREE performance from GIST index?

when using complicated real-world data that's not necessarily true. Maybe there's some magic spell that can be used to restore the earlier performance, but I really don't have time at present to do the necessary experimenting. -- Clive Page Dept of Physics & Astronomy, University

Re: [GENERAL] How to get RTREE performance from GIST index?

when using complicated real-world data that's not necessarily true. Maybe there's some magic spell that can be used to restore the earlier performance, but I really don't have time at present to do the necessary experimenting. -- Clive Page Dept of Physics & Astronomy, University

Re: [GENERAL] How to get RTREE performance from GIST index?

an ANALYZE command on it? I haven't been doing that, because I didn't know. Regards -- Clive Page -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres crashed when adding a sequence column

ame objective more easily? -- Clive Page ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Postgres crashed when adding a sequence column

E on the table. Looks as if the data are ok, but the new column is still empty of course. Thanks very much for your help, Richard. Will try again without indices, keeping an eye on the cpu/memory consumption. -- Clive Page Dept of Physics & Astronomy, University of Leicester, Leicester, LE1

Re: [GENERAL] Postgres crashed when adding a sequence column

upgrade to v8.0 soon, as it clearly solves another problem I encountered recently. Thanks for all the help. -- Clive Page Dept of Physics & Astronomy, University of Leicester,Tel +44 116 252 3551 Leicester, LE1 7RH, U.K. Fax +44 116 252 3311

Re: [GENERAL] Postgres crashed when adding a sequence column

This is just to report success: I dropped all indices and repeated: UPDATE intwfs SET id = nextval('myseq'); and it worked fine - took 3681 secs (my estimate of an hour wasn't far out). Now doing a VACUUM FULL to remove the old tuples. -- Clive Page Dept of Physics & As

[GENERAL] Baffled by failure to use index when WHERE uses a function

performance? I am using Postgres 8.1.0 on Linux. -- Clive Page Dept of Physics & Astronomy, University of Leicester, Leicester, LE1 7RH, U.K. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropr

Re: [GENERAL] Baffled by failure to use index when WHERE uses a

of my posting, I had been experimenting with various versions of the same table and didn't quite get the details consistent in what I posted. Thanks also to Richard Huxton and Martin van Oosterhout who gave me the same hint. What an excellent support group this is. -- Clive Page Dept

[GENERAL] Column descriptions - could they be propagated to new tables?

o find any easy way of propagating the descriptions - would it be a useful facility to have them propagated automatically? I would have thought that things like units would be useful even in many scientific applications, e.g. to have monetary columns described as dollars/pounds/euros or whatev

Re: [GENERAL] Column descriptions - could they be propagated to new

appreciate domains from a design perspective. There are a couple of disadvantages to using them so I'd suggest reading about them. Thanks for the suggestion, I'll start reading up on them. -- Clive Page Dept of Physics & Astronomy, University of Leicester, Leicester,

Re: [GENERAL] Column descriptions - could they be propagated to new

yourself quite a bit of work. Well I know about pgAstro and pgSphere (and helped a little in testing them) but maybe there are others. Will look, thanks. -- Clive Page Dept of Physics & Astronomy, University of Leicester, Leicester, LE1 7RH, U.K. ---(end of broad

[GENERAL] How to use dblink within pl/pgsql function:

ECT * FROM DBLINK(''select x,y from mytab'') as temp(x integer, y real) LOOP count := count + 1; END LOOP; RETURN count; END; ' LANGUAGE 'plpgsql'; But this syntax does not work, and I cannot find a form which does work. Does anyone know how to

Re: [GENERAL] How to use dblink within pl/pgsql function:

rows returned from the join using R-trees. Of course there are easier ways of doing what this function does, I just wanted to get that working as a basis on which to build something more advanced. -- Clive Page ---(end of broadcast)--- TIP 2: