[postgis-users] query index trouble

[Mark Cave-Ayland]

On Wed, 2008-01-16 at 10:21 +1100, James M. Kelly wrote:
> 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))"


Hi James,

As you suspect, I believe that the problem you are seeing is related to
the join between PARCEL and your view here:

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

Without seeing your schema, my guess is that PARCEL_VIEW.PFI is a
text/varchar column, while PARCEL.view_pfi is an integer column. Because
the index semantics between text and numbers are different, PostgreSQL
can't use the index to join between directly between the tables, which
forces a sequential scan of PARCEL so that it can convert the
PARCEL.view_pfi column to text for the join.

So, in short, making both columns the same type and ensuring that they
both have a B-Tree index should resolve the issue.


HTH,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk