Re: how to write an optimized sql with two same subsql?

[sunpeng <bluevaley@xxxxxxxxx>]

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@xxxxxxxxx>

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?
>
>

What are you trying to discover about groups and their frequency in
tablea? ÂDoes the numberical value of groupid have any meaning in your
system?


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general