Actually I've simplied my original sql to the previous version, since it's simple yet reveals the same problem.
My original sql is to get two instersected cluster(as same concept as group ) and its commonarea:
SELECT a.clusterid AS clusterida,
b.clusterid AS clusteridb,
St_astext(St_intersection(a.bufferbox, b.bufferbox)) AS commonarea
FROM (SELECT St_buffer(St_convexhull(St_collect(c.a0)), 2100.000000) AS
bufferbox,
d.clusterid AS
clusterid
FROM _mcir_2347694 c,
_mcir_2347694_clusterid2 d
WHERE c.uid = d.uid
GROUP BY d.clusterid) a,
(SELECT St_buffer(St_convexhull(St_collect(c.a0)), 2100.000000) AS
bufferbox,
d.clusterid AS
clusterid
FROM _mcir_2347694 c,
_mcir_2347694_clusterid2 d
WHERE c.uid = d.uid
GROUP BY d.clusterid) b
WHERE b.clusterid > a.clusterid
AND St_intersects(a.bufferbox, b.bufferbox)
ORDER BY a.clusterid; The DDL for
_mcir_2347694 and _mcir_2347694_clusterid2 is:
CREATE TABLE _mcir_2347579
(
a0 geometry,
uid integer
)
CREATE TABLE _mcir_2347579_clusterid2
(
uid integer NOT NULL,
clusterid integer
)
In these two tables, _mcir_2347579_clusterid2.uid = mcir_2347579.uid, just like a forign key.
The same question is how to avoid the following subquery be executed twice:
SELECT St_buffer(St_convexhull(St_collect(c.a0)), 2100.000000) AS
bufferbox,
d.clusterid AS
clusterid
FROM _mcir_2347694 c,
_mcir_2347694_clusterid2 d
WHERE c.uid = d.uid
GROUP BY d.clusterid
2010/10/14 Rob Sargent
<robjsargent@gmail.com>
On 10/14/2010 05:34 PM, sunpeng wrote:
> We have a table A:
> CREATE TABLE A(
> uid integer,
> groupid integer
> )
> Now we use this subsql to get each group's count:
> SELECT count(*) as count
> FROM A
> GROUP BY groupid
> ORDER BY groupid
>
> Then we try to find the group pair with following conditions:
> SELECT c.groupid as groupid1,d.groupid as groupid2
> FROM subsql as c, subsql as d
> WHERE d.groupid > c.groupid
> and d.count > c.count;
>
> Does that mean subsql will be executed twice? or how to write the
> optimized sql?
>
>
tablea? Does the numberical value of groupid have any meaning in your
system?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
