[postgis-users] postgis and mapserver - queries

[C F]

I think this discussion is probably dead and you've moved on with the 
'explain verbose'.... but an interesting note if you're still interested...
As I said, I was doing something similar for my own version of a map server 
and this looks like it may alleviate the problem of trying to execute a 
query to see the result set structure (data types and column names) without 
eating up much cpu time.  This *appears* to be a solution but I don't have a 
large dataset handy to test it on or maybe someone can see a flaw in my 
logic for other query types.
Basically it appears that PostgreSQL is smart enough to not to execute 
subqueries when it knows the outer expression will always be false.  Here 
are some samples...  notice in examples 3 & 4, the "never executed" 
statements.


1)
==================================
postgres=# explain analyze select id from layer;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
Seq Scan on layer  (cost=0.00..1.04 rows=4 width=4) (actual time=0.00..0.00 
rows=4 loops=1)
Total runtime: 0.00 msec


2)
=================================
postgres=# explain analyze select * from (select id from layer) a limit 0;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
Limit  (cost=0.00..0.00 rows=1 width=4) (actual time=0.00..0.00 rows=0 
loops=1)
   ->  Seq Scan on layer  (cost=0.00..1.04 rows=4 width=4) (actual 
time=0.00..0.00 rows=1 loops=1)
Total runtime: 0.00 msec



3)
=================================
postgres=# explain analyze select * from (select id from layer) a where 1 < 
0;
                                    QUERY PLAN
----------------------------------------------------------------------------------
Result  (cost=0.00..1.04 rows=4 width=4) (actual time=0.00..0.00 rows=0 
loops=1)
   One-Time Filter: false
   ->  Seq Scan on layer  (cost=0.00..1.04 rows=4 width=4) (never executed)
Total runtime: 1.00 msec


4)
=================================
postgres=# explain analyze select * from (select id from layer group by id) 
a where 1 < 0;
                                          QUERY PLAN
---------------------------------------------------------------------------------------------
Result  (cost=1.08..1.10 rows=1 width=4) (actual time=0.00..0.00 rows=0 
loops=1)
   One-Time Filter: false
   ->  Subquery Scan a  (cost=1.08..1.10 rows=1 width=4) (never executed)
         ->  Group  (cost=1.08..1.10 rows=1 width=4) (never executed)
               ->  Sort  (cost=1.08..1.09 rows=4 width=4) (never executed)
                     Sort Key: id
                     ->  Seq Scan on layer  (cost=0.00..1.04 rows=4 width=4) 
(never executed)
Total runtime: 1.00 msec





>From: David Blasby <dblasby at refractions.net>
>Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>Subject: [postgis-users] postgis and mapserver - queries
>Date: Mon, 09 Jun 2003 10:04:56 -0700
>
>I recently changed mappostgis.c so its a bit wiser about how it determines 
>the columns in a query.  Previously, it did this:
>
>EXPLAIN VERBOSE <query>;
>
>The advantage of this is that it is extreamly quick.  Unfortunately, the 
>EXPLAIN VERBOSE command changes its output with different versions of 
>postgresql.  For example, in 7.1 and 7.2, the EXPLAIN VERBOSE is set as a 
>NOTICE message.  In 7.3 its sent as a query result (and is formated 
>differently).
>
>The new mappostgis.c determines columns like this (<query> is basically the 
>query in your DATA statement):
>
>SELECT * FROM (<query>) as foo LIMIT 0;
>
>Basically, it executes the query asking for zero rows.  In most cases this 
>will return a result (where you can determine the column names and types 
>easily) quickly.  Unfortunately, i noticed that it can actually take a long 
>time to execute.  For example, imagine you have a big table with road data 
>in it:
>
>SELECT * FROM (  SELECT gid, sum(len) FROM roads GROUP BY gid) as foo LIMIT 
>0;
>
>This can take many seconds to compute because postgresql will do the entire 
>GROUP BY (which involves a SORT) before it can start returning results.
>
>I was thinking that you can use the fact you know the unique column 
>attribute ("using unique <column>") and the geometry column's name to speed 
>things up by reducing the query set:
>
>SELECT * FROM (<query>) as foo WHERE <unique column> = -666 AND 
><geom_column> && 'BOX3D(0 0, 0 0)' LIMIT 0;
>
>[Here -666 and the BOX3D are just made up to reduce the query results]
>
>But this seems pretty hacky (and will not always work cut down on the work) 
>- anyone else have ideas?
>
>dave
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail