[GENERAL] Why is this functional index not used?

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