Query plan question, and a memory leak - Mailing list pgsql-general

One question, and one possible bug report:

1) The following query has an odd plan that I can't figure out how to read. It
   seems to include the subplan twice, does that mean it's executing it twice?
   Even twice doesn't explain the cost which is much higher than similar plans
   that don't trigger the duplicate subplan. What am I doing wrong to trigger
   this behaviour?

2) The version of the query at the bottom appears to trigger a big memory
   leak. The only difference is the addition of a "WHERE geom2 @ make_box()"
   clause. (make_box returns a box, the definition is below). That version
   grows continuously, quickly reaching 200M before I kill it.

The queries are simplified versions of the actual query I'm working with, so
they might not make much logical sense, but they cause the same problems.


This is the query with the strange plan:

slo=> explain SELECT 1
                         FROM gg, ad, store_location
                        WHERE store_location_id = (
                               SELECT store_location_id
                                 FROM ad_store_location JOIN store_location USING (store_location_id)
                                WHERE ad_id = ad.ad_id
                                LIMIT 1
                               ) ;

slo-> slo-> slo(> slo(> slo(> slo(> slo(>                                                             QUERY PLAN
                                                     

-----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..12974034.71 rows=375971060 width=8)
   ->  Nested Loop  (cost=0.00..624633.51 rows=45140 width=8)
         ->  Seq Scan on ad  (cost=0.00..2597.40 rows=45140 width=4)
         ->  Index Scan using store_location_pkey on store_location  (cost=0.00..8.39 rows=1 width=4)
               Index Cond: (store_location.store_location_id = (subplan))
               SubPlan
                 ->  Limit  (cost=0.00..5.37 rows=1 width=8)
                       ->  Nested Loop  (cost=0.00..24.32 rows=5 width=8)
                             ->  Index Scan using idx_ad_store_location_ad on ad_store_location  (cost=0.00..10.60
rows=5width=4) 
                                   Index Cond: (ad_id = $0)
                             ->  Index Scan using store_location_pkey on store_location  (cost=0.00..3.02 rows=1
width=4)
                                   Index Cond: ("outer".store_location_id = store_location.store_location_id)
                 ->  Limit  (cost=0.00..5.37 rows=1 width=8)
                       ->  Nested Loop  (cost=0.00..24.32 rows=5 width=8)
                             ->  Index Scan using idx_ad_store_location_ad on ad_store_location  (cost=0.00..10.60
rows=5width=4) 
                                   Index Cond: (ad_id = $0)
                             ->  Index Scan using store_location_pkey on store_location  (cost=0.00..3.02 rows=1
width=4)
                                   Index Cond: ("outer".store_location_id = store_location.store_location_id)
   ->  Seq Scan on gg  (cost=0.00..190.29 rows=8329 width=0)





This is the query that triggers the memory leak:

slo=> explain SELECT 1
                         FROM gg, ad, store_location
                        WHERE store_location_id = (
                               SELECT store_location_id
                                 FROM ad_store_location JOIN store_location USING (store_location_id)
                                WHERE ad_id = ad.ad_id
                                  AND store_location.geom2 @ make_box(gg.longitude,gg.latitude,65)
                                LIMIT 1
                               ) ;

slo-> slo-> slo(> slo(> slo(> slo(> slo(> slo(>                                                          QUERY PLAN
                                                     

-----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..19453708582.74 rows=375971060 width=24)
   ->  Nested Loop  (cost=0.00..12351998.60 rows=375971060 width=20)
         ->  Seq Scan on ad  (cost=0.00..2597.40 rows=45140 width=4)
         ->  Seq Scan on gg  (cost=0.00..190.29 rows=8329 width=16)
   ->  Index Scan using store_location_pkey on store_location  (cost=0.00..27.36 rows=1 width=4)
         Index Cond: (store_location.store_location_id = (subplan))
         SubPlan
           ->  Limit  (cost=0.00..24.34 rows=1 width=8)
                 ->  Nested Loop  (cost=0.00..24.34 rows=1 width=8)
                       ->  Index Scan using idx_ad_store_location_ad on ad_store_location  (cost=0.00..10.60 rows=5
width=4)
                             Index Cond: (ad_id = $0)
                       ->  Index Scan using store_location_pkey on store_location  (cost=0.00..3.02 rows=1 width=4)
                             Index Cond: ("outer".store_location_id = store_location.store_location_id)
                             Filter: (geom2 @ make_box($1, $2, 65::double precision))
           ->  Limit  (cost=0.00..24.34 rows=1 width=8)
                 ->  Nested Loop  (cost=0.00..24.34 rows=1 width=8)
                       ->  Index Scan using idx_ad_store_location_ad on ad_store_location  (cost=0.00..10.60 rows=5
width=4)
                             Index Cond: (ad_id = $0)
                       ->  Index Scan using store_location_pkey on store_location  (cost=0.00..3.02 rows=1 width=4)
                             Index Cond: ("outer".store_location_id = store_location.store_location_id)
                             Filter: (geom2 @ make_box($1, $2, 65::double precision))




This is the definition of make_box:

-- make_box(longitude, latitude, distance) --
CREATE OR REPLACE FUNCTION make_box(float,float,float) RETURNS box AS
'SELECT box(point(long-d_long,lat-d_lat),point(long+d_long,lat+d_lat))
   FROM (SELECT $1 AS long, $2 AS lat,
         $3*1000::float/1852::float/60::float as d_lat,
         $3*1000::float/1852::float/60::float/cos(radians($2)) as d_long
        ) as x'
LANGUAGE SQL
STRICT IMMUTABLE;


--
greg