[postgis-users] GIST INDEX!!

[César Medina]

Hi Chris
when i say "slow", i refer that is no so fast than googleMaps, extreme example.!!!
This is a url with one commune,  http://mapas.observatoriourbano.cl/localizacion/map.phtml?config=7101
In this case, we have 11 layer in database format (Geographic data). if you multiply 11 by 270 comunnes in my country, we have 2970 tables aprox, and when i do a zoom over a area, this show the area, but "slow".

for this, I don't do anything relationated with WHERE clause, i don't use dificult SQL, just show the layer in the mapfile with: 
"DATA the_geom from (SELECT the_geom,gid FROM vii_talca_areacomunal) AS pas_pred3 USING UNIQUE gid USING SRID=-1"
as a complement in Hardware, i have two Virtual Machine:1.- Database server, AMD Opteron 64, 2,4 Ghz, 4GB Ram2.- Page server, AMD Opteron 64, 2,4 Ghz, 2GB Ram
in Software, Linux fedora, php pages, apache, postgresql with postgis, mapserver and p.mapper
I don't know what i have to do, to do it  map more fast !!!!
thank very much.!!!!

César
http://www.linkedin.com/in/cesarmedinam
http://foss4gchile.blogspot.com/
mail: ciesareMedina (at) gmail (dot) com
msn: ciesareMedina (at) hotmail (dot) com
skype: ciesare_medina




> From: chris.hermansen at timberline.ca
> To: postgis-users at postgis.refractions.net
> Date: Tue, 29 Dec 2009 11:07:39 -0800
> Subject: Re: [postgis-users] GIST INDEX!!
> 
> César;
> 
> You need to be a bit more specific about what you mean by "slow".  What
> operations precisely are slow?
> 
> Having said that, in general any columns - spatial or otherwise - to
> which you refer in WHERE clauses in your SELECT statements should be
> considered for indexing, especially if you use them in join conditions.
> If you have a specific SELECT statement that gives you problems, you
> might try using an EXPLAIN or ANALYZE together with the statement as for
> example in
> 
> http://www.postgresql.org/docs/8.1/static/sql-explain.html
> 
> 2700 tables - that is a lot of tables.  I have no idea if such a large
> number of tables will slow down query execution, but it seems possible.
> 
> Do you really need that many tables?  For example, if you have all the
> IGM shape files by map sheet for loading, you don't need to create a
> table for each shape file.  Rather you might put all the different
> "caminos" shape files into one table, and the "comunas" into another,
> and so on.  Sorry I'm just guessing at what you might be doing here, if
> I'm wrong please ignore!
> 
> La documentación de PostgreSQL es disponible en castellano, por ejemplo
> en el sitio
> 
> http://palomo.usach.cl/docshtml/node4.html
> 
> On Tue, 2009-12-29 at 15:38 -0300, César Medina wrote:
> > Dear all
> > 
> > 
> > I am trying to do a tunning to my database, and i have many doubt,
> > because i think that is very slow (with 3 o 4 users is slow)
> > 
> > 
> > I have 2700 tables aprox. with geometry column in my database, the
> > street's name, big avenues, regional boundaries, street types,comunal
> > areas, etc. but the tables don't have a index in the geometry
> > column !!!!!!! - Is recommended do a "GIST" index for each tables in
> > my database? - Could be more fast, if that have a gist index? - What
> > is the benefits ? kings regards, thank you... and happy new year.
> > 
> > 
> > PD: Frecuently i do "vacuum" and "reindex" to my database
> > César http://www.linkedin.com/in/cesarmedinam
> > http://foss4gchile.blogspot.com/ mail: ciesareMedina (at) gmail (dot)
> > com msn: ciesareMedina (at) hotmail (dot) com skype: ciesare_medina
> > 
> > CHILE.
> > 
> > 
> > ______________________________________________________________________
> > Windows Live: Make it easier for your friends to see what you’re up to
> > on Facebook.
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> -- 
> Regards,
> 
> Chris Hermansen    ·    mailto:chris.hermansen at timberline.ca
> tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
> Timberline Natural Resource Group · http://www.timberline.ca
> 401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
 		 	   		  
_________________________________________________________________
Windows Live Hotmail: Your friends can get your Facebook updates, right from Hotmail®.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_4:092009
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091229/34b80223/attachment.html>