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

[Mark Cave-Ayland]

Happy New Year everybody!


Many thanks for your advice Paul! Today has been my first day back after
Christmas so I have been busy installing the CVS tarball to see how the
new index selectivity works....

The process I used was to install the new version was as follows:

1) Drop database
2) Recreate database
3) pg_restore last backup into the database
4) vacuum analyze (takes about 3mins)
5) select update_geometry_stats() (takes about 30s)


After finally getting pg_restore going, I've been trying the new index
selectivity features on our development system but unfortunately my
query plans still keep choosing the geometry index!

Here are the results of the query plan:


# explain SELECT * from osgb_point WHERE layerid=23::bigint;  NOTICE:
QUERY PLAN:

Index Scan using osgb_point_layerid_index on osgb_point
(cost=0.00..983.82 rows=21821 width=85)

EXPLAIN


# explain SELECT * from osgb_point WHERE layerid=23::bigint and (geom &&
setSRID( 'BOX3D(-213384.685527067 -184341.966247553,763828.983612401
686412.704241455)'::BOX3D,find_srid('','osgb_point','geom') ));
NOTICE:  QUERY PLAN:

Index Scan using osgb_point_geom_index on osgb_point  (cost=0.00..8.47
rows=1 width=85)

EXPLAIN


When actually executing the query, the first one returns all 22,000
records in about 1-2s, where as the second takes at least 30s before I
get bored and abort the query...

Looking closer, the costs seem a little strange. The osgb_point table
contains 330,000-ish points with approx 22,000 having a layerid=3, and
the extents given in the query above cover the area of all 330,000
points -  so I'm wondering if I have missed something obvious?

I am becoming more convinced that as the number of layerids in our
library table increases into the hundreds, that postgres does not
maintain enough data about the table (eg. in pg_statistics) to allow
this system to work, so in which case we will have to break this out in
many separate tables anyway :(

Anyway, I would still be interested in getting this working as a short
term measure, and I look forward to hearing yours and Dave's comments.


Many thanks,


Mark.


---
Mark Cave-Ayland
Webbased Ltd.

Tel: (01752) 764445
Fax: (01752) 764446




> -----Original Message-----
> From: Paul Ramsey [mailto:pramsey at refractions.net]
> Sent: 23 December 2002 18:06
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Postgres choses wrong index? (Repost)
> 
> Fortunately, we have a solution for this problem :)
> You are going to have to use Dave's index selectivity upgrades, which
> are available in the CVS version. There are some manual steps, and
your
> GEOMETRY_COLUMNS table will need to be expanded to make room for the
> index histogram information. Start by going back into the mail
archives
> and reading Dave's emails on the subject for more information...
> 
> http://postgis.refractions.net/pipermail/postgis-users/2002-
> October/001526.html
> http://postgis.refractions.net/pipermail/postgis-users/2002-
> October/001595.html
> 
> P.