[postgis-users] Too many results

[Jim Jim]

Hello,

Thank you for your reply. The query looks like the following:

SELECT id FROM spots WHERE
                                points2d && SetSRID('BOX3D($w[0] $w[1], $w[3] $w[4])'::box3d,4326)
                                AND
                                timeline && SetSRID('BOX3D($w[2] 0, $w[5] 0)'::box3d,4326)";



and the definitions:

CREATE TABLE spots
(
  id bigserial NOT NULL,
  points2d geometry NOT NULL,
  timeline geometry NOT NULL,
  CONSTRAINT spots_pkey PRIMARY KEY (id),
  CONSTRAINT enforce_dims_points2d CHECK (ndims(points2d) = 2),
  CONSTRAINT enforce_dims_timeline CHECK (ndims(timeline) = 2),
  CONSTRAINT enforce_geotype_points2d CHECK (geometrytype(points2d) = 'POINT'::text OR points2d IS NULL),
  CONSTRAINT enforce_geotype_timeline CHECK (geometrytype(timeline) = 'LINESTRING'::text OR timeline IS NULL),
  CONSTRAINT enforce_srid_points2d CHECK (srid(points2d) = 4326),
  CONSTRAINT enforce_srid_timeline CHECK (srid(timeline) = 4326)
)
WITH (OIDS=FALSE);


-- Index: idindex

-- DROP INDEX idindex;

CREATE INDEX idindex
  ON spots
  USING btree
  (id);

-- Index: idx_spots_points2d

-- DROP INDEX idx_spots_points2d;

CREATE INDEX idx_spots_points2d
  ON spots
  USING gist
  (points2d);
ALTER TABLE spots CLUSTER ON idx_spots_points2d;

-- Index: idx_spots_timeline

-- DROP INDEX idx_spots_timeline;

CREATE INDEX idx_spots_timeline
  ON spots
  USING gist
  (timeline);


I have not yet tried the EXPLAIN ANALYZE statement you suggested.

Thank you!


Date: Tue, 14 Apr 2009 04:16:45 -0700
From: gwilliamson39 at yahoo.com
Subject: Re: [postgis-users] Too many results
To: postgis-users at postgis.refractions.net



Jim --

Can we see the SQL and table/index defintions (or something close, anyway) ? The limit won't help much if the database has to process all x-thousand tuples before deciding it has the number needed. Are you using an "order by" clause ?

If you can run the query in an EXPLAIN ANALYZE statement (wrap it in a transaction and roll it back when done if it does any updates etc. that you don't want to keep) ... this will take longer than the regular evaluation because of the timing calls but gives useful information about what the planner is doing.

HTH,

Greg Williamson


From: Jim Jim <gamecleaner at hotmail.com>
To: postgis-users at postgis.refractions.net
Sent: Tuesday, April 14, 2009 2:00:29 AM
Subject: [postgis-users] Too many results






Hello,

I have a table with 2d points and lines. My query is a rectangle and a line. The problem is there are too many results to the point where it becomes non-practical to wait. So I wanted to limit the results. I know that this can be done by LIMIT N, however, this does not stop searching after finding N results and the whole point is not to wait too long.

Do you have any ideas on how this could be accomplished? I suppose when it comes to intersections and geometrical problems this is quite important.

Thank you!

Get the New Internet Explore 8 Optimised for MSN. Download Now

_________________________________________________________________
View your Twitter and Flickr updates from one place – Learn more!
http://clk.atdmt.com/UKM/go/137984870/direct/01/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090414/39c71bc7/attachment.html>