query from 9.0 fails to return on 9.2 - Mailing list pgsql-admin

I value the work of the community and in particular the dedicated committers on the postgres team, regardless of this
littlewrinkle we have encountered upon our transition from 9.0 to 9.2. Since the number of tables involved is
ridiculouslysmall and the amount of data is negligible, at this point I will not try to create a self contained test
case.Merely looking at the query may tell you what is wrong. 

Here is the top line of the *really long* explain as run on a 9.0 server (it takes only a couple of seconds to return):
HashAggregate (cost=1148.97..1149.36 rows=1 width=205) 
However, the same query run on the same machine after upgrade to 9.2.1 never returns. The query was sitting there for
10hours unbeknownst to me. 

Just for reference, here are the schema and counts of data in the tables, which have all been vacuum analyzed after
usingpg_upgrade. 

CREATE TABLE currency_codes
(
  code character(3) NOT NULL,
   description character varying(50),
   active boolean DEFAULT true,
   currency_symbol character varying(1) DEFAULT ''::character varying,
   html_entity character varying(8) DEFAULT ''::character varying,
   exponent smallint,
   CONSTRAINT currency_codes_pkey PRIMARY KEY (code)
)

network=# select count(*) from currency_codes;
  count
-------
    198
(1 row)

CREATE TABLE currency_by_country
(
   currency_code character(3) NOT NULL,
   country_code character(2) NOT NULL,
   effective_date date NOT NULL,
   CONSTRAINT currency_by_country_pkey PRIMARY KEY (country_code),
   CONSTRAINT currency_by_country_country_code_fkey FOREIGN KEY (country_code)
       REFERENCES tbl_countrycodes (country_code) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE NO ACTION,
   CONSTRAINT currency_by_country_currency_code_fkey FOREIGN KEY (currency_code)
       REFERENCES currency_codes (code) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)

network=# select count(*) from currency_by_country;
  count
-------
    248
(1 row)

CREATE TABLE subscription_pricing
(
   subscription_type smallint NOT NULL,
   amount numeric(10,2) NOT NULL,
   currency_code character(3) NOT NULL,
   notes text,
   stamp timestamp without time zone DEFAULT now(),
   effective_date date NOT NULL,
   expiration_date date,
   CONSTRAINT subscription_pricing_pkey PRIMARY KEY (subscription_type, currency_code, effective_date),
   CONSTRAINT subscription_pricing_currency_code_fkey FOREIGN KEY (currency_code)
       REFERENCES currency_codes (code) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE NO ACTION,
   CONSTRAINT subscription_pricing_subscription_type_fkey FOREIGN KEY (subscription_type)
       REFERENCES subscription_types (subscription_type) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE NO ACTION
)

network=# select count(*) from subscription_pricing;
  count
-------
   8705
(1 row)

CREATE TABLE subscription_point_values
(
   pk serial NOT NULL,
   subscription_type smallint,
   pp numeric(5,2) NOT NULL,
   period integer,
   currency_code character(3) NOT NULL,
   effective_date date,
   CONSTRAINT subscription_point_values_pkey PRIMARY KEY (pk),
   CONSTRAINT subscription_point_values_currency_code_fkey FOREIGN KEY (currency_code)
       REFERENCES currency_codes (code) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE NO ACTION,
   CONSTRAINT subscription_point_values_period_fkey FOREIGN KEY (period)
       REFERENCES periods (period) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT subscription_point_values_subscription_type_fkey FOREIGN KEY (subscription_type)
       REFERENCES subscription_types (subscription_type) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE NO ACTION,
   CONSTRAINT subscription_point_values_subscription_type_key UNIQUE (subscription_type, currency_code)
)


network=# select count(*) from subscription_point_values;
  count
-------
   2236
(1 row)

Here is the ugly, but really basic as far a SQL goes, query. Any comments or explanations as to why it is a no-brainer
on9.0 but basically fails on 9.2? 

SELECT xml FROM (
SELECT DISTINCT '<item>'||

'<country>'||cbc.country_code||'</country>'||

'<currency_code>'||sp1.currency_code||'</currency_code>'||

'<currency_name>'||cc.description||'</currency_name>'||

'<type1_fee>'||sp1.amount||'</type1_fee>'||

'<type2_fee>'||sp2.amount||'</type2_fee>'||

'<type3_fee>'||sp3.amount||'</type3_fee>'||

'<type4_fee>'||sp4.amount||'</type4_fee>'||

'<type40_fee>'||sp40.amount||'</type40_fee>'||

'<type41_fee>'||sp41.amount||'</type41_fee>'||

'<type42_fee>'||sp42.amount||'</type42_fee>'||

'<type43_fee>'||sp43.amount||'</type43_fee>'||

'<type44_fee>'||sp44.amount||'</type44_fee>'||

'<type45_fee>'||sp45.amount||'</type45_fee>'||

'<type46_fee>'||sp46.amount||'</type46_fee>'||

'<type47_fee>'||sp47.amount||'</type47_fee>'||

'<type48_fee>'||sp48.amount||'</type48_fee>'||

'<type49_fee>'||sp49.amount||'</type49_fee>'||

'<type1_pp>'||spv1.pp||'</type1_pp>'||

'<type2_pp>'||spv2.pp||'</type2_pp>' ||

'<type3_pp>'||spv3.pp||'</type3_pp>'||

'<type4_pp>'||spv4.pp||'</type4_pp>'||

'<type40_pp>'||spv40.pp||'</type40_pp>'||

'<type41_pp>'||spv41.pp||'</type41_pp>'||

'<type42_pp>'||spv42.pp||'</type42_pp>'||

'<type43_pp>'||spv43.pp||'</type43_pp>'||

'<type44_pp>'||spv44.pp||'</type44_pp>'||

'<type45_pp>'||spv45.pp||'</type45_pp>'||

'<type46_pp>'||spv46.pp||'</type46_pp>'||

'<type47_pp>'||spv47.pp||'</type47_pp>'||

'<type48_pp>'||spv48.pp||'</type48_pp>'||

'<type49_pp>'||spv49.pp||'</type49_pp>'||

'</item>'AS xml, cbc.country_code

FROM
         currency_codes cc
JOIN
         currency_by_country cbc
         ON cc.code=cbc.currency_code
JOIN
         subscription_pricing sp1
         ON sp1.currency_code=cbc.currency_code
         AND sp1.subscription_type=1
JOIN
         subscription_pricing sp2
         ON sp2.currency_code=cbc.currency_code
         AND sp2.subscription_type=2
JOIN
         subscription_pricing sp3
         ON sp3.currency_code=cbc.currency_code
         AND sp3.subscription_type=3
JOIN
         subscription_pricing sp4
         ON sp4.currency_code=cbc.currency_code
         AND sp4.subscription_type=4
JOIN
         subscription_pricing sp40
         ON sp40.currency_code=cbc.currency_code
         AND sp40.subscription_type=40
JOIN
         subscription_pricing sp41
         ON sp41.currency_code=cbc.currency_code
         AND sp41.subscription_type=41
JOIN
         subscription_pricing sp42
         ON sp42.currency_code=cbc.currency_code
         AND sp42.subscription_type=42
JOIN
         subscription_pricing sp43
         ON sp43.currency_code=cbc.currency_code
         AND sp43.subscription_type=43
JOIN
         subscription_pricing sp44
         ON sp44.currency_code=cbc.currency_code
         AND sp44.subscription_type=44
JOIN
         subscription_pricing sp45
         ON sp45.currency_code=cbc.currency_code
         AND sp45.subscription_type=45
JOIN
         subscription_pricing sp46
         ON sp46.currency_code=cbc.currency_code
         AND sp46.subscription_type=46
JOIN
         subscription_pricing sp47
         ON sp47.currency_code=cbc.currency_code
         AND sp47.subscription_type=47
JOIN
         subscription_pricing sp48
         ON sp48.currency_code=cbc.currency_code
         AND sp48.subscription_type=48
JOIN
         subscription_pricing sp49
         ON sp49.currency_code=cbc.currency_code
         AND sp49.subscription_type=49
JOIN
         subscription_point_values spv1
         ON spv1.currency_code=cbc.currency_code
         AND spv1.subscription_type=1
JOIN
         subscription_point_values spv2
         ON spv2.currency_code=cbc.currency_code
         AND spv2.subscription_type=2
JOIN
         subscription_point_values spv3
         ON spv3.currency_code=cbc.currency_code
         AND spv3.subscription_type=3
JOIN
         subscription_point_values spv4
         ON spv4.currency_code=cbc.currency_code
         AND spv4.subscription_type=4
JOIN
         subscription_point_values spv40
         ON spv40.currency_code=cbc.currency_code
         AND spv40.subscription_type=40
JOIN
         subscription_point_values spv41
         ON spv41.currency_code=cbc.currency_code
         AND spv41.subscription_type=41
JOIN
         subscription_point_values spv42
         ON spv42.currency_code=cbc.currency_code
         AND spv42.subscription_type=42
JOIN
         subscription_point_values spv43
         ON spv43.currency_code=cbc.currency_code
         AND spv43.subscription_type=43
JOIN
         subscription_point_values spv44
         ON spv44.currency_code=cbc.currency_code
         AND spv44.subscription_type=44
JOIN
         subscription_point_values spv45
         ON spv45.currency_code=cbc.currency_code
         AND spv45.subscription_type=45
JOIN
         subscription_point_values spv46
         ON spv46.currency_code=cbc.currency_code
         AND spv46.subscription_type=46
JOIN
         subscription_point_values spv47
         ON spv47.currency_code=cbc.currency_code
         AND spv47.subscription_type=47
JOIN
         subscription_point_values spv48
         ON spv48.currency_code=cbc.currency_code
         AND spv48.subscription_type=48
JOIN
         subscription_point_values spv49
         ON spv49.currency_code=cbc.currency_code
         AND spv49.subscription_type=49
WHERE
         NOW()::DATE BETWEEN sp1.effective_date AND COALESCE(sp1.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp2.effective_date AND COALESCE(sp2.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp3.effective_date AND COALESCE(sp3.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp4.effective_date AND COALESCE(sp4.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp40.effective_date AND COALESCE(sp40.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp41.effective_date AND COALESCE(sp41.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp42.effective_date AND COALESCE(sp42.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp43.effective_date AND COALESCE(sp43.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp44.effective_date AND COALESCE(sp44.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp45.effective_date AND COALESCE(sp45.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp46.effective_date AND COALESCE(sp46.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp47.effective_date AND COALESCE(sp47.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp48.effective_date AND COALESCE(sp48.expiration_date, NOW()::DATE)
AND
         NOW()::DATE BETWEEN sp49.effective_date AND COALESCE(sp49.expiration_date, NOW()::DATE)

) AS tmp
JOIN tbl_countrycodes tcc ON tcc.country_code=tmp.country_code
WHERE tcc.restrictions=FALSE

ORDER BY tmp.country_code;