Given a country table and an order table:
CREATE TABLE g.country
(
-- inherited from table g.standard: oid uuid NOT NULL,
-- ... some more columns inherited...
lisocode integer NOT NULL, -- Numeric ISO 3166 code
szisoalpha2 character varying(2), -- The 2 letter country code
szisoalpha3 character varying(3), -- The 3 letter country code
szisonum3 character varying(3), -- The NUMERIC country code with leading zeros
-- a few more columns here
CONSTRAINT country_pkey PRIMARY KEY (oid)
) INHERITS (g.standard)
WITH (
OIDS=FALSE
);
This table is filled with ISO 3166 country codes.
The order table:
CREATE TABLE t."order"
(
-- inherited from table g.standard: oid uuid NOT NULL,
-- ... some more columns inherited...
szxmladdress text,
-- many more columns in this table
CONSTRAINT country_pkey PRIMARY KEY (oid)
) INHERITS (g.standard)
WITH (
OIDS=FALSE
);
A typical entry in t."order".szxmladdress looks like
<address entityType="Person" country="DE">
<field name="Address.oid">ae0eb84f-9b8b-4fef-b87a-d6757bdfeaf9</field>
<field
name="Address.uaddrtyperef">0bbdb48c-21c7-429e-944e-59a4d9ace9d5</field>
<field name="Address.szstreet">Hauptstraße</field>
<!-some more xml tags here -->
</address>
No XML field in the order table exceeds 2kB.
Getting the 2 letter country code from the xml address by this function:
CREATE OR REPLACE FUNCTION g.getxmladdresscountryid(xaddr text)
RETURNS uuid AS
$BODY$BEGIN
RETURN (SELECT oID FROM G.Country WHERE szIsoAlpha2 =
array_to_string(xpath('/address/@country', xAddr::xml), '') ORDER BY lIsoCode
LIMIT 1);
END$BODY$
LANGUAGE plpgsql IMMUTABLE;
I know that this function is not really IMMUTABLE but the country table is
changing only every several years.
Created a functional index on the order table:
CREATE INDEX order_getxmladdresscountryid_fidx
ON t."order"
USING btree
(g.getxmladdresscountryid(szxmladdress));
Joining order and country table limiting to 10 rows uses the index:
explain analyse
SELECT
GCountry.szISOAlpha2,
GCountry.szISOAlpha3,
GCountry.szISONum3
from
t.order torder
left join G.Country GCountry ON G.GetXmlAddressCountryID(TOrder.szXmlAddress) =
GCountry.oID
limit 10
Gives:
Limit (cost=0.56..8.45 rows=10 width=11) (actual time=0.644..4.764 rows=10
loops=1)
-> Merge Right Join (cost=0.56..10670.45 rows=13517 width=11) (actual
time=0.642..4.754 rows=10 loops=1)
Merge Cond: (gcountry.oid =
g.getxmladdresscountryid(torder.szxmladdress))
-> Index Scan using country_pkey on country gcountry
(cost=0.27..38.05 rows=258 width=27) (actual time=0.025..0.067 rows=32 loops=1)
-> Index Scan using order_getxmladdresscountryid_fidx on "order"
torder (cost=0.29..7019.04 rows=13517 width=366) (actual time=0.020..0.058
rows=10 loops=1)
Planning time: 0.603 ms
Execution time: 4.898 ms
But when I remove the "limit 10", the index is no longer used:
Hash Left Join (cost=17.81..5397.46 rows=13517 width=11) (actual
time=0.941..4721.372 rows=13510 loops=1)
Hash Cond: (g.getxmladdresscountryid(torder.szxmladdress) = gcountry.oid)
-> Seq Scan on "order" torder (cost=0.00..3504.17 rows=13517 width=366)
(actual time=0.011..27.542 rows=13510 loops=1)
-> Hash (cost=14.58..14.58 rows=258 width=27) (actual time=0.427..0.427
rows=258 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> Seq Scan on country gcountry (cost=0.00..14.58 rows=258 width=27)
(actual time=0.008..0.226 rows=258 loops=1)
Planning time: 0.580 ms
Execution time: 4728.602 ms
Event a "limit 100" does not use the index any more.
However, the result of 4728 ms is almost a linear increase from 10 rows to
13500 rows.
I played around with COST of the function between 1 and 20000 and with several
options on postgresql.conf without luck.
A sequential scan on the order table alone is carried out in 15 ms.
Thanks for any idea.
Klaus
