[postgis-users] query index trouble

[James M. Kelly]

Hi all

 

I am fairly new to postGIS and postgreSQL, so I am still bumbling
through a few performance issues at the moment.

 

I am having some trouble with doing an inner join on a spatial table
back with a normal table, and from what I can see in the query analyser,
it seems doesn't seem to be using my indexes as efficiently as it should
be.

 

The query is as follows:

 

SELECT "PARCEL_VIEW"."UFI", "PARCEL_VIEW"."UFI_OLD",
"PARCEL_VIEW".version, "PARCEL_VIEW"."PFI",
"PARCEL_VIEW"."CENTROID_PFI", "PARCEL_VIEW"."Z_LEVEL",
"PARCEL_VIEW"."FEATURE_TYPE", "PARCEL_VIEW"."FEATURE_QUALITY_ID",
"PARCEL_VIEW"."PFI_CREATED", "PARCEL_VIEW"."UFI_CREATED","PARCEL".spi,
"PARCEL_VIEW".geometry

   FROM "PARCEL_VIEW", "Coord","PARCEL"

  WHERE "PARCEL_VIEW".geometry && expand(geomfromtext(((('POINT('::text
|| "Coord"."Long"::text) || ' '::text) || "Coord"."Lat"::text) ||
')'::text, 4019), 0.1::double precision) 

AND distance(geomfromtext(((('POINT('::text || "Coord"."Long"::text) ||
' '::text) || "Coord"."Lat"::text) || ')'::text, 4019),
"PARCEL_VIEW".geometry) < 0.1::double precision 

AND "PARCEL".view_pfi = "PARCEL_VIEW"."PFI";

 

I am using a table with one row with a lat and a long value to select
all the shapes in the parcel_view table within 0.1 degrees. Parcel_view
does not hold the details I want, which are held in the PARCEL table,
which seems to be where the problem comes in.  Running the query without
the join to the PARCEL table is very fast, but it slows down
dramatically when adding it.

 

PARCEL_VIEW has two indexes on it, a GIST one on the geometry column,
and a btree one on the PFI column.  PARCEL has a btree index on the
VIEW_PFI column.  I have reduced the RANDOM_PAGE_COST down to 1.

 

Here is the output from the explain

"Hash Join  (cost=32.56..240113.97 rows=5 width=321)"

"  Hash Cond: (("PARCEL".view_pfi)::text = "PARCEL_VIEW"."PFI")"

"  ->  Seq Scan on "PARCEL"  (cost=0.00..224592.23 rows=3097823
width=36)"

"  ->  Hash  (cost=32.50..32.50 rows=5 width=299)"

"        ->  Nested Loop  (cost=2.74..32.50 rows=5 width=299)"

"              Join Filter:
(distance(geometryfromtext((((('POINT('::text || ("Coord"."Long")::text)
|| ' '::text) || ("Coord"."Lat")::text) || ')'::text), 4019),
"PARCEL_VIEW".geometry) < 0.1::double precision)"

"              ->  Seq Scan on "Coord"  (cost=0.00..1.01 rows=1
width=16)"

"              ->  Bitmap Heap Scan on "PARCEL_VIEW"  (cost=2.74..30.72
rows=14 width=299)"

"                    Filter: ("PARCEL_VIEW".geometry &&
expand(geometryfromtext((((('POINT('::text || ("Coord"."Long")::text) ||
' '::text) || ("Coord"."Lat")::text) || ')'::text), 4019), 0.1::double
precision))"

"                    ->  Bitmap Index Scan on "PARCEL_VIEW_Idx"
(cost=0.00..2.73 rows=14 width=0)"

"                          Index Cond: ("PARCEL_VIEW".geometry &&
expand(geometryfromtext((((('POINT('::text || ("Coord"."Long")::text) ||
' '::text) || ("Coord"."Lat")::text) || ')'::text), 4019), 0.1::double
precision))"

 

I assume there is something fairly simple that I am missing, so any help
would be most appreciated.

 

Cheers

 

James Kelly

 

 

-- 
Message  protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au/tt


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080116/f505cc7e/attachment.html>