[Chameleon] query performance and weird behavior

[Eijnden, B. van den (Bart)]

Hi,

I don't know the exact internals, i.e. I don't know if the USING UNIQUE
field is used for queries or the oid field. A way to find out is to log your
queries in PostgreSQL, try that. But if USING UNIQUE is not specified, it
defaults to oid AFAIK.

Normally with a POSTGIS view you explicitly select which oid field you use
in the SQL, so that one needs to be indexed. As an example:

CREATE OR REPLACE VIEW	v_test AS SELECT table1.oid, table1.*, table2.* from
table1, table2 WHERE (table1.id = table2.id);

So in this case the oid field of table1 is used in the view.

I assume your data is in EPSG:4326 in the database as well or not? Or is it
reprojected?

Best regards,
Bart

-----Oorspronkelijk bericht-----
Van: Danna Dowdy [mailto:Danna.Dowdy at noaa.gov]
Verzonden: donderdag 5 januari 2006 15:43
Aan: Eijnden, B. van den (Bart)
CC: chameleon at lists.maptools.org
Onderwerp: Re: [Chameleon] query performance and weird behavior


I am pretty new at this so I wasn't aware of the oid field...would I 
create oid indexes on  the tables that are being used in my view?  Do I 
need to use the oid somewhere in the DATA statement or in the view?  
Thanks so much for your help...

Here is the portion of the map file...column gid does have an index on 
it as well....
LAYER
            CONNECTIONTYPE postgis
            NAME "Nutrients"
            CONNECTION "user=postgres dbname=ohh_v8 host=10.49.48.22 
port=5432"
            DATA "geom FROM (select myid as gid, geom, mycollid, 
mysamplingproject, mycontributor, myoriginalcollectioncode, mydatetime, 
myorigstationcode, mysystem, mycommoncreekname, mycolmethod, mynorth, 
mysouth, myeast, mywest, myfieldreplicate, myunits, mywaterdepth, 
mytidestage, myparameter, myquad, mywatershed, myprimarylandinfluence, 
mytype, myresult_numeric, myresult_text,  myparamclassid FROM 
nutrient_collections) as c USING UNIQUE gid USING SRID=4326"
            METADATA
                "wms_title" "OHH Nutrient Collections"
                "wms_srs" "UTM Zone 17"         
                "wms_abstract" "Oceans and Human Health Initiative"
            END # Metadata
            STATUS OFF
            GROUP "Parameter Classes"
            DUMP TRUE
            TYPE POINT
            PROJECTION
                "init=epsg:4326"
            END # Projection
            TEMPLATE "ttt_query.html"
            TOLERANCE 5
            TOLERANCEUNITS pixels
            CLASS
              NAME "rest"
              TEMPLATE "ttt"
              STYLE
                SYMBOL 'circle'
                SIZE 7
                COLOR 4 4 17
               END # Style
            END # Class
END # Nutrient Layer


Eijnden, B. van den (Bart) wrote:

>Do they all have indexes on the oid field?
>
>Also, what is your DATA statement in the MAP file? Does it use "USING
>UNIQUE" or not? If so, is the UNIQUE column also indexed?
>
>You could also turn PostgreSQL logging on, see which queries Mapserver runs
>against PostGIS, and see how fast they run in the database.
>
>Best regards,
>Bart
>
>-----Oorspronkelijk bericht-----
>Van: chameleon-bounces at lists.maptools.org
>[mailto:chameleon-bounces at lists.maptools.org]Namens Danna Dowdy
>Verzonden: woensdag 4 januari 2006 22:15
>Aan: chameleon at lists.maptools.org
>Onderwerp: [Chameleon] query performance and weird behavior
>
>
>I am running into a couple of problems and I really hope someone can 
>help me....
>Currently, I have three different postgis layers which are coming from 
>views.  Two of them work fine, but one layer in particular just runs and 
>runs and finally returns an empty page for query results?  Anyone ever 
>experience this before? 
>Also, I was wondering if anyone could point me in the right direction 
>for some performance tuning for query results...
>I am returning alot of results depending on the active layers chosen, 
>but sometimes it takes several minutes?
>_______________________________________________
>Chameleon mailing list
>Chameleon at lists.maptools.org
>http://lists.maptools.org/mailman/listinfo/chameleon
>
>
>Disclaimer
>************************************************************************
>Aan dit bericht kunnen geen rechten worden ontleend. Dit bericht is
>uitsluitend bestemd voor de geadresseerde. Als u dit bericht per abuis
>hebt ontvangen, wordt u verzocht het te vernietigen en de afzender te 
>informeren. Wij adviseren u om bij twijfel over de juistheid of de 
>volledigheid van de mail contact met afzender op te nemen.
>
>This message shall not constitute any rights or obligations.
>This message is intended solely for the addressee.
>If you have received this message in error, please delete it and
>notify the sender immediately. When in doubt whether this message
>is correct or complete, please contact the sender.
>************************************************************************
>
>_______________________________________________
>Chameleon mailing list
>Chameleon at lists.maptools.org
>http://lists.maptools.org/mailman/listinfo/chameleon
>
>  
>


Disclaimer
************************************************************************
Aan dit bericht kunnen geen rechten worden ontleend. Dit bericht is
uitsluitend bestemd voor de geadresseerde. Als u dit bericht per abuis
hebt ontvangen, wordt u verzocht het te vernietigen en de afzender te 
informeren. Wij adviseren u om bij twijfel over de juistheid of de 
volledigheid van de mail contact met afzender op te nemen.

This message shall not constitute any rights or obligations.
This message is intended solely for the addressee.
If you have received this message in error, please delete it and
notify the sender immediately. When in doubt whether this message
is correct or complete, please contact the sender.
************************************************************************