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;
