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

[Paul Ramsey]

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.


Mark Cave-Ayland wrote:
> Apologies for the last message, I think Outlook managed to encode it
> somehow! Here is the plain text version!
> 
> M.
> 
> 
> 
> Hi everyone,
>  
> We're having problems with Postgres/Postgis making the wrong decision
> about which index to use on some of our queries, and was hoping that
> someone could suggest a way of fixing it.
>  
> The basic setup is that we have a number of layers built into a table,
> some densely populated and others with only a couple of hundred records
> over the entire map are. Each entry within the table has an indexed
> geometry and also an indexed layerid. In a particular case, we have a
> table called biggeom containing approximately 3 million records split
> across two different layerids. The bulk of these records have a layerid
> of 1, whereas approximately 150 have a layerid of 2.
>  
> The problem we have is that when attempting to render layerid=2 when
> zoomed out to maximum extents, the map takes minutes to appear on screen
> and often results with the browser timing out. After investigating for a
> couple of hours, I have found out why this is the case. It appears that
> no matter what the current view scale is, the geometric index is always
> chosen. Here is the output from the query planner:
>  
> explain select * from biggeom where layerid=2 and (geom &&
> setSRID('BOX3D(-213384 -184341, 763828 686412)'::BOX3D, 27700);
>  
> -> Index scan on biggeom_geom_index (...etc.....)
>  
> So it appears that even though there are only 150 rows with layerid=2,
> postgres decides to use the spatial index which will contain most (if
> not all!) of the 3 million records!
>  
> In contrast, if I do:
>  
> explain select * from biggeom where layerid=2
>  
> -> Index scan on biggeom_layerid_index
>  
> ...which returns all 150 records pretty much instantly. My question is
> therefore how can I correctly get postgres to correctly determine the
> primary index to use when both a layerid AND a bounding box are given?
> Or is this an impossible task in which case I imagine I would need to
> split the one library table into multiple tables?
>  
>  
> Many thanks,
>  
> Mark.
> 
> ---
> Mark Cave-Ayland
> Webbased Ltd.
> 
> Tel: (01752) 764445
> Fax: (01752) 764446
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_