Search Postgresql Archives

Re: Why is this functional index not used?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I played around with COST up to 999999 – still no improvement.

 

HOWEVER – I determined that more than 95% of the records in the database are local addresses and the indexed function returns NULL.

This seems to create an issue with LEFT JOIN.

 

Increasing the COST of the function to 20000 and rewriting the query as follows provides a significantly better result:

 

explain analyse

SELECT

GCountry.szISOAlpha2,

GCountry.szISOAlpha3,

GCountry.szISONum3

from

t.order torder

join G.Country GCountry ON G.GetXmlAddressCountryID(TOrder.szXmlAddress) = GCountry.oID

UNION ALL

SELECT

'XX'::TEXT, -- can use NULL::TEXT with no difference

'XXX'::TEXT, -- can use NULL::TEXT with no difference

'999'::TEXT -- can use NULL::TEXT with no difference

from

t.order torder

WHERE G.GetXmlAddressCountryID(TOrder.szXmlAddress) IS NULL

 

Gives:

 

Append  (cost=0.29..7661.82 rows=13578 width=11) (actual time=0.106..20.464 rows=13510 loops=1)

  ->  Nested Loop  (cost=0.29..7386.57 rows=13510 width=11) (actual time=0.104..1.235 rows=73 loops=1)

        ->  Seq Scan on country gcountry  (cost=0.00..14.58 rows=258 width=27) (actual time=0.012..0.126 rows=258 loops=1)

        ->  Index Scan using order_getxmladdresscountryid_fidx on "order" torder  (cost=0.29..27.89 rows=68 width=366) (actual time=0.003..0.003 rows=0 loops=258)

              Index Cond: (g.getxmladdresscountryid(szxmladdress) = gcountry.oid)

  ->  Index Scan using order_getxmladdresscountryid_fidx on "order" torder_1  (cost=0.29..139.47 rows=68 width=0) (actual time=0.013..12.840 rows=13437 loops=1)

        Index Cond: (g.getxmladdresscountryid(szxmladdress) IS NULL)

Planning time: 1.104 ms

Execution time: 23.607 ms

 

A similar result was achieved by return a dummy ID from the function for records without country.

 

Any idea why the functional index does not work in a LEFT JOIN query?

 

Thanks Klaus

 

 

Von: Jeff Janes [mailto:jeff.janes@xxxxxxxxx]
Gesendet: Dienstag, 21.
März 2017 08:22
An: Klaus P. Pieper
Betreff: Re: Why is this functional index not used?

 

On Mon, Mar 20, 2017 at 1:25 PM, Klaus P. Pieper <kpi6288@xxxxxxxxx> wrote:

 

I played around with COST of the function between 1 and 20000 and with several options on postgresql.conf without luck.

 

Why not more than 20000 ?   The true value could be much higher than that.

 

Cheers,

 

Jeff


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux