[postgis-users] Postgres choses wrong index? (Repost)

[David Blasby]

Mark,

Good to see someone else using this!!

Here a few hints:

1) verify that you turned on support in the makefile - "USE_STATS=1" about a
dozen lines into the makefile.
2) Verify that there's an entry in geometry_columns for your geometry column

3) Verify that there's a big HISTOGRAM2D(...) in the geometry_columns table
4) Eye ball (dont count) the HISTOGRAM2D() and see if there's 'about' enough
entries in it (should be about the same as the number of rows in the
table).  Ignore the first 6 numbers  - they're
&xmin,&ymin,&xmax,&ymax,&boxesPerSide,&avgFeatureArea if you wanted to know.

5) re-run vacuum analyse (cant hurt)

If this all looks good, then send a message with:

1. The entry in your geometry_columns table
2. number of rows in the table
3. the column names and types of your data
4. re-run your test queries using "explain analyse" instead of just
"explain" - it give a bunch more info (but takes a lot longer)
5. replace the find_srid('','osgb_point','geom')  with the actual SRID
number.  ** If this works, re-write you mapserver DATA statement so it looks
like 'geom from (select oid,* from osgb_point) as foo USING UNIQUE oid USING
SRID=#'


The result "Index Scan using osgb_point_geom_index on osgb_point
(cost=0.00..8.47 rows=1 width=85)"  looks like its using the old estimating
system - which always says it will only get a few rows.  I dont think you're
using the new estimates.

dave