DEV Community

Cover image for Similarity Is Not Truth: Comparing vector distance, is_true(), and extract() in Azure AI for PostgreSQL
Franck Pachot
Franck Pachot

Posted on Edited on

Similarity Is Not Truth: Comparing vector distance, is_true(), and extract() in Azure AI for PostgreSQL

Vector search queries often use top-k similarity, assuming the closest results are true and can serve as factual validation. With distance operators such as <=> for cosine distance in PostgreSQL with pgvector or DiskANN, lower values mean greater similarity, and similarity should be used to retrieve a relevant subset to rank or qualify, not as a measure of truth. Azure AI functions for PostgreSQL can call AI models to generate embeddings and provide more specific functions such as is_true() or extract(), for model-based evaluation and structured responses.

Objective

In this blog post, I'll use the three of them on a small dataset, a table of countries, with the same goal: tell me if the country is an island or not.

  • <=> should return a low distance for countries that are islands and a very high distance for those that are not, so I'm tempted to determine a threshold that tells me the truth
  • is_true() should return true for countries that are islands and false for those that are not, or NULL when the result is inconclusive
  • extract() should extract the "is an island?" information as a boolean.

Create the table

I import a list of countries from a public resource


DROP TABLE IF EXISTS countries;
CREATE TABLE countries (
  country text primary key,
  region  text
);

\copy countries(country,region)
FROM PROGRAM 'curl -L "https://raw.githubusercontent.com/cs109/2014_data/master/countries.csv"' WITH (FORMAT csv, HEADER true);

SELECT * FROM countries WHERE region = 'OCEANIA' LIMIT 10;

Enter fullscreen mode Exit fullscreen mode

Here is a sample:

     country      | region
------------------+---------
 Australia        | OCEANIA
 Fiji             | OCEANIA
 Kiribati         | OCEANIA
 Marshall Islands | OCEANIA
 Micronesia       | OCEANIA
 Nauru            | OCEANIA
 New Zealand      | OCEANIA
 Palau            | OCEANIA
 Papua New Guinea | OCEANIA
 Samoa            | OCEANIA
(10 rows)
Enter fullscreen mode Exit fullscreen mode

I generate embeddings from the country name, assuming that the model has enough knowledge about countries:


CREATE EXTENSION vector;
CREATE EXTENSION azure_ai;

ALTER TABLE countries
  ADD COLUMN embedding vector(1536);

UPDATE countries
SET embedding = azure_openai.create_embeddings(
  input => country,
  dimensions => 1536
)::vector(1536);

Enter fullscreen mode Exit fullscreen mode

For a real application, you may provide more information, such as a geographical description of the country, rather than using the AI model as a source of knowledge, but I keep this simple for the demo.

Naive query

Here is a query that uses Azure AI functions to generate embeddings for 'is an island' to compare the vector distance with the country, return a true or false for the question 'is an island' with the country name, and extract an 'is an island' boolean:

SELECT country
    -- cosine distance to 'is an island'
  , azure_openai.create_embeddings(
    input => 'is an island',
    dimensions => 1536
  )::vector(1536) <=> embedding AS distance
    -- boolean answer to 'is an island'
  , azure_ai.is_true(
    statement => country ||' is an island'
) AS is_true_result
    -- extract 'is an island?' as a boolean, in addition to other questions
  , azure_ai.extract(
    country,
    ARRAY[
      'land: boolean - is an area of land',
      'water: boolean - is surrounded by water',
      'continent: boolean - is a continent',
      'island: boolean - is an island?',
      'neighbours: string - list the countries with common border'
    ]
  ) AS extracted
FROM countries
ORDER BY distance
;
Enter fullscreen mode Exit fullscreen mode

I get this result:

             country              |      distance      | is_true_result |                                                                                                          extracted
----------------------------------+--------------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Iceland                          | 0.5288325821583937 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Dominica                         |  0.583984959074203 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Marshall Islands                 | 0.5923968276529713 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Seychelles                       | 0.5933297352264011 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Solomon Islands                  | 0.5992036897764201 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Barbados                         | 0.6026798894629409 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Bahamas                          | 0.6037901963467236 | f              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Mauritius                        | 0.6124306023780004 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Jamaica                          | 0.6180866747206792 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Ireland                          | 0.6208333769699201 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": "United Kingdom (Northern Ireland)"}
 Grenada                          | 0.6314871259473416 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Palau                            | 0.6331963890581136 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Kiribati                         | 0.6340927146328686 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Cyprus                           | 0.6354914993816243 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Maldives                         | 0.6368475975326672 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Malta                            | 0.6370369487669678 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 New Zealand                      | 0.6520422783711599 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Antigua and Barbuda              | 0.6560949198973425 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Fiji                             | 0.6563765953128096 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Saint Lucia                      | 0.6565374364556272 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Saint Kitts and Nevis            | 0.6569025217334334 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Madagascar                       | 0.6579842393491677 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Nauru                            | 0.6588623575239401 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Belize                           | 0.6617206536439253 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Mexico, Guatemala"}
 Cape Verde                       | 0.6656987305382893 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Guyana                           | 0.6690943980832992 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Brazil, Suriname, Venezuela"}
 Micronesia                       | 0.6694775662174064 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Sri Lanka                        | 0.6755248175409809 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Andorra                          | 0.6756446018762446 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "France, Spain"}
 Israel                           | 0.6763307658990072 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Lebanon, Syria, Jordan, Egypt, Palestine"}
 Cuba                             | 0.6780035821480603 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Tonga                            | 0.6854439801282708 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Vanuatu                          | 0.6898198008050471 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Dominican Republic               | 0.6930591990187845 | f              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": "Haiti"}
 Saint Vincent and the Grenadines | 0.6936146574365081 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Singapore                        | 0.6939824470932374 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Samoa                            | 0.6942054226212125 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Haiti                            | 0.6970099016123408 | f              | {"land": true, "water": false, "island": true, "continent": false, "neighbours": "Dominican Republic"}
 Indonesia                        | 0.6982757826983539 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Malaysia, Papua New Guinea, Timor-Leste"}
 Bahrain                          | 0.7010005561641666 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Comoros                          | 0.7067622419634335 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Estonia                          | 0.7085149756741925 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Latvia, Russia"}
 Liechtenstein                    | 0.7098232671398083 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Austria, Switzerland"}
 Tuvalu                           | 0.7100676352558253 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Brunei                           |  0.710790435672632 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Malaysia"}
 San Marino                       | 0.7188084142495059 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Italy"}
 Vatican City                     | 0.7195959897300721 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Italy"}
 Suriname                         | 0.7227729682040444 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Brazil, Guyana, French Guiana"}
 Sao Tome and Principe            | 0.7228708359839435 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Guinea-Bissau                    | 0.7250435443354935 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Senegal, Guinea"}
 Trinidad and Tobago              | 0.7255906511994212 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Greece                           | 0.7294355944328467 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Albania, North Macedonia, Bulgaria, Turkey"}
 Qatar                            | 0.7311974837740045 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Saudi Arabia"}
 Bhutan                           | 0.7334687504057431 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "India, China"}
 Monaco                           | 0.7340602079110607 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "France"}
 Iraq                             |  0.734913671407935 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Turkey, Iran, Kuwait, Saudi Arabia, Jordan, Syria"}
 Kuwait                           | 0.7369097472864523 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Iraq, Saudi Arabia"}
 Denmark                          | 0.7379886532695916 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Germany"}
 Slovenia                         | 0.7381978525807786 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Italy, Austria, Hungary, Croatia"}
 Liberia                          | 0.7392671571539429 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Sierra Leone, Guinea, Côte d'Ivoire"}
 Djibouti                         |  0.740412556074715 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Eritrea, Ethiopia, Somalia"}
 Oman                             | 0.7413224461946565 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Saudi Arabia, United Arab Emirates, Yemen"}
 Somalia                          | 0.7449227200702471 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Djibouti, Ethiopia, Kenya"}
 Gabon                            | 0.7456118851623608 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Cameroon, Equatorial Guinea, Republic of the Congo"}
 Norway                           | 0.7460697360770665 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Sweden, Finland, Russia"}
 Guinea                           | 0.7467644839197858 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Guinea-Bissau, Senegal, Mali, Côte d'Ivoire, Liberia, Sierra Leone"}
 Ivory Coast                      | 0.7468067790057498 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Liberia, Guinea, Mali, Burkina Faso, Ghana"}
 Croatia                          | 0.7472955577185647 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Slovenia, Hungary, Serbia, Bosnia and Herzegovina, Montenegro"}
 Swaziland                        | 0.7483075776278031 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "South Africa, Mozambique"}
 Switzerland                      | 0.7488779010850297 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "France, Germany, Italy, Austria, Liechtenstein"}
 Montenegro                       | 0.7510461676351238 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Croatia, Bosnia and Herzegovina, Serbia, Kosovo, Albania"}
 Gambia                           | 0.7511148494306696 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Senegal"}
 Costa Rica                       | 0.7515789607375122 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Nicaragua, Panama"}
 Iran                             | 0.7520032875575114 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Armenia, Azerbaijan, Turkmenistan, Afghanistan, Pakistan, Iraq, Turkey"}
 Malaysia                         | 0.7523975721266118 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Thailand, Indonesia, Brunei"}
 Australia                        | 0.7531747642187843 | f              | {"land": true, "water": true, "island": true, "continent": true, "neighbours": ""}
 Laos                             | 0.7534458303255653 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "China, Vietnam, Cambodia, Thailand, Myanmar"}
 Lithuania                        | 0.7537826240809542 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Latvia, Belarus, Poland, Russia"}
 Malawi                           | 0.7539340735642389 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Tanzania, Zambia, Mozambique"}
 Albania                          | 0.7543481950991728 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Montenegro, Kosovo, North Macedonia, Greece"}
 Equatorial Guinea                | 0.7552398688680615 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Cameroon, Gabon"}
 Papua New Guinea                 | 0.7579830941609125 | f              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": "Indonesia"}
 Panama                           | 0.7583225717454902 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Costa Rica, Colombia"}
 India                            | 0.7588056536976735 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Pakistan, China, Nepal, Bhutan, Bangladesh, Myanmar"}
 East Timor                       | 0.7593546935613076 | f              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": "Indonesia"}
 Latvia                           | 0.7597057656675359 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Estonia, Lithuania, Russia, Belarus"}
 Ecuador                          | 0.7624754894140511 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Colombia, Peru"}
 Sierra Leone                     | 0.7626424385811169 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Guinea, Liberia"}
 Thailand                         | 0.7633743553697794 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Myanmar, Laos, Cambodia, Malaysia"}
 Kenya                            | 0.7633932963743615 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Ethiopia, Somalia, South Sudan, Tanzania, Uganda"}
 Portugal                         | 0.7635817318640051 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Spain"}
 Yemen                            | 0.7676960249749847 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Saudi Arabia, Oman"}
 Angola                           | 0.7677627170849556 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Namibia, Zambia, Democratic Republic of the Congo, Republic of the Congo"}
 Korea, South                     | 0.7682695194153355 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "North Korea"}
 Philippines                      |  0.768360616054728 | f              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Egypt                            | 0.7698457172953644 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Libya, Sudan, Israel, Palestine (Gaza Strip)"}
 Slovakia                         | 0.7700161287344122 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Austria, Czech Republic, Hungary, Poland, Ukraine"}
 Mali                             | 0.7720214648256873 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Algeria, Niger, Burkina Faso, Côte d'Ivoire, Guinea, Senegal, Mauritania"}
 Mozambique                       | 0.7730232895261306 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Tanzania, Malawi, Zambia, Zimbabwe, South Africa, Eswatini"}
 Chile                            | 0.7736521896991397 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Argentina, Bolivia, Peru"}
 Netherlands                      | 0.7744934254319348 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Belgium, Germany"}
 Japan                            | 0.7752039062900633 | t              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": ""}
 Lebanon                          | 0.7757218763514213 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Syria, Israel"}
 Venezuela                        | 0.7780634943782279 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Colombia, Brazil, Guyana"}
 Saudi Arabia                     | 0.7784696402204396 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Jordan, Iraq, Kuwait, Qatar, United Arab Emirates, Oman, Yemen"}
 Mauritania                       | 0.7800588172236024 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Algeria, Mali, Senegal, Western Sahara"}
 Finland                          | 0.7805862826038644 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Norway, Sweden, Russia"}
 United Kingdom                   | 0.7809162565353357 | f              | {"land": true, "water": true, "island": true, "continent": false, "neighbours": "Ireland"}
 United States                    | 0.7812606623096955 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Canada, Mexico"}
 Luxembourg                       | 0.7822165244509347 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Belgium, France, Germany"}
 Libya                            | 0.7824428027461353 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Egypt, Sudan, Chad, Niger, Algeria, Tunisia"}
 United Arab Emirates             | 0.7824643321659567 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Oman, Saudi Arabia"}
 Azerbaijan                       | 0.7849075953023875 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Armenia, Georgia, Iran, Russia, Turkey"}
 Bulgaria                         | 0.7850856942981823 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Romania, Serbia, North Macedonia, Greece, Turkey"}
 Tunisia                          | 0.7871000070928271 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Algeria, Libya"}
 Algeria                          | 0.7874681044581234 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Tunisia, Libya, Niger, Mali, Mauritania, Western Sahara, Morocco"}
 Korea, North                     | 0.7879892453128181 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "China, Russia, South Korea"}
 Macedonia                        | 0.7880430333357722 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Albania, Bulgaria, Greece, Kosovo, Serbia"}
 Ghana                            | 0.7881981515121235 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Côte d’Ivoire, Burkina Faso, Togo"}
 Italy                            | 0.7885051893564746 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "France, Switzerland, Austria, Slovenia, San Marino, Vatican City"}
 Tanzania                         | 0.7896636529476878 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Kenya, Uganda, Rwanda, Burundi, Democratic Republic of the Congo, Zambia, Malawi, Mozambique"}
 Lesotho                          | 0.7909975802310909 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "South Africa"}
 France                           | 0.7910379679468842 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Belgium, Luxembourg, Germany, Switzerland, Italy, Monaco, Spain, Andorra"}
 Uruguay                          | 0.7925000615095663 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Argentina, Brazil"}
 Canada                           | 0.7932398128874569 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "United States"}
 Sweden                           | 0.7934611312932686 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Norway, Finland"}
 Georgia                          | 0.7941791375881693 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Russia, Azerbaijan, Armenia, Turkey"}
 South Africa                     | 0.7942291523595642 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Namibia, Botswana, Zimbabwe, Mozambique, Eswatini; surrounds Lesotho"}
 Morocco                          | 0.7950397235955838 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Algeria, Western Sahara, Spain (via land borders in Ceuta, Melilla, and Peñón de Vélez de la Gomera)"}
 Poland                           | 0.7963759637284251 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Germany, Czech Republic, Slovakia, Ukraine, Belarus, Lithuania, Russia"}
 Cambodia                         | 0.7965340656169185 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Thailand, Laos, Vietnam"}
 Belgium                          | 0.7980869194190703 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "France, Germany, Luxembourg, Netherlands"}
 Benin                            | 0.7982976845005323 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Togo, Burkina Faso, Niger, Nigeria"}
 Zambia                           | 0.7985490414294518 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Angola, Democratic Republic of the Congo, Tanzania, Malawi, Mozambique, Zimbabwe, Botswana, Namibia"}
 Eritrea                          | 0.8004614320937081 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Djibouti, Ethiopia, Sudan"}
 Bosnia and Herzegovina           | 0.8005094498975392 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Croatia, Serbia, Montenegro"}
 Senegal                          | 0.8027974455435845 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Mauritania, Mali, Guinea, Guinea-Bissau, The Gambia"}
 Spain                            | 0.8031171836179388 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Portugal, France, Andorra, Gibraltar"}
 Congo                            | 0.8038000550145199 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": ""}
 Honduras                         | 0.8044201740032614 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Guatemala, El Salvador, Nicaragua"}
 Moldova                          | 0.8050954356863649 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Romania, Ukraine"}
 Nicaragua                        |  0.805131358531338 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Honduras, Costa Rica"}
 Burundi                          | 0.8061755133193602 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Rwanda, Tanzania, Democratic Republic of the Congo"}
 Austria                          | 0.8068631379420688 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Germany, Czech Republic, Slovakia, Hungary, Slovenia, Italy, Switzerland, Liechtenstein"}
 Hungary                          | 0.8071099570842866 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Austria, Slovakia, Ukraine, Romania, Serbia, Croatia, Slovenia"}
 Namibia                          | 0.8071479230906948 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Angola, Botswana, South Africa, Zambia"}
 Uganda                           | 0.8076595495334273 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Kenya, South Sudan, Democratic Republic of the Congo, Rwanda, Tanzania"}
 Pakistan                         | 0.8079608151489982 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "India, Afghanistan, Iran, China"}
 Zimbabwe                         | 0.8083652201073442 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Zambia, Mozambique, South Africa, Botswana, Namibia"}
 Burma                            | 0.8084678853674937 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "India, Bangladesh, China, Laos, Thailand"}
 Vietnam                          | 0.8086019827831477 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "China, Laos, Cambodia"}
 Togo                             | 0.8086140437309055 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Ghana, Benin, Burkina Faso"}
 Botswana                         | 0.8094561453077773 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Namibia, Zambia, Zimbabwe, South Africa"}
 Bangladesh                       | 0.8116332313945582 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "India and Myanmar"}
 Rwanda                           | 0.8123016329790479 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Uganda, Tanzania, Burundi, Democratic Republic of the Congo"}
 Ukraine                          | 0.8124002831062466 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Belarus, Poland, Slovakia, Hungary, Romania, Moldova, Russia"}
 Burkina                          | 0.8144677094112762 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Mali, Niger, Benin, Togo, Ghana, Côte d'Ivoire"}
 Nepal                            |   0.81548431860318 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "China, India"}
 South Sudan                      | 0.8167656802436088 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Sudan, Ethiopia, Kenya, Uganda, Democratic Republic of the Congo, Central African Republic"}
 Guatemala                        | 0.8169098938203585 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Mexico, Belize, Honduras, El Salvador"}
 Armenia                          | 0.8176363924555679 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Georgia, Azerbaijan, Iran, Turkey"}
 Turkey                           | 0.8193215210793555 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Greece, Bulgaria, Georgia, Armenia, Azerbaijan, Iran, Iraq, Syria"}
 Tajikistan                       | 0.8198533317698937 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Afghanistan, China, Kyrgyzstan, Uzbekistan"}
 Afghanistan                      |  0.820887527584718 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Pakistan, Iran, Turkmenistan, Uzbekistan, Tajikistan, China"}
 Syria                            | 0.8218808248573776 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Turkey, Iraq, Jordan, Israel, Lebanon"}
 Paraguay                         | 0.8231293871420273 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Argentina, Brazil, Bolivia"}
 Sudan                            |  0.824108242328083 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Egypt, Libya, Chad, Central African Republic, South Sudan, Ethiopia, Eritrea"}
 El Salvador                      | 0.8245914824661897 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Guatemala, Honduras"}
 China                            | 0.8252551978082601 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Afghanistan, Bhutan, India, Kazakhstan, North Korea, Kyrgyzstan, Laos, Mongolia, Myanmar, Nepal, Pakistan, Russia, Tajikistan, Vietnam"}
 Mexico                           | 0.8265017573637201 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "United States, Guatemala, Belize"}
 Bolivia                          | 0.8280788220964819 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Brazil, Paraguay, Argentina, Chile, Peru"}
 Czech Republic                   | 0.8310742184694699 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Germany, Poland, Slovakia, Austria"}
 Ethiopia                         | 0.8312814930072527 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Eritrea, Djibouti, Somalia, Kenya, South Sudan, Sudan"}
 Argentina                        | 0.8334394726018135 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Chile, Bolivia, Paraguay, Brazil, Uruguay"}
 Chad                             |  0.833663171842557 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Libya, Sudan, Central African Republic, Cameroon, Nigeria, Niger"}
 Mongolia                         | 0.8375233257332386 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Russia, China"}
 Brazil                           | 0.8385723379743028 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Argentina, Bolivia, Colombia, French Guiana, Guyana, Paraguay, Peru, Suriname, Uruguay, Venezuela"}
 Congo, Democratic Republic of    | 0.8406643182215046 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Republic of the Congo, Central African Republic, South Sudan, Uganda, Rwanda, Burundi, Tanzania, Zambia, Angola"}
 Uzbekistan                       | 0.8432081376769294 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Afghanistan, Kazakhstan, Kyrgyzstan, Tajikistan, Turkmenistan"}
 Jordan                           | 0.8495623559538862 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Saudi Arabia, Iraq, Syria, Israel, Palestine"}
 Belarus                          | 0.8500006729124654 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Poland, Lithuania, Latvia, Russia, Ukraine"}
 Nigeria                          | 0.8501041934223525 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Benin, Niger, Chad, Cameroon"}
 Niger                            | 0.8513790597186496 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Algeria, Libya, Chad, Nigeria, Benin, Burkina Faso, Mali"}
 Colombia                         | 0.8547748039997966 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Brazil, Ecuador, Panama, Peru, Venezuela"}
 Romania                          | 0.8560515005626373 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Ukraine, Moldova, Bulgaria, Serbia, Hungary"}
 Cameroon                         | 0.8596726760020865 | f              | {"land": true, "water": true, "island": false, "continent": false, "neighbours": "Nigeria, Chad, Central African Republic, Republic of the Congo, Gabon, Equatorial Guinea"}
 Central African Republic         | 0.8601319535596592 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Cameroon, Chad, Sudan, South Sudan, Democratic Republic of the Congo, Republic of the Congo"}
 Turkmenistan                     |  0.862325668764606 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Afghanistan, Iran, Kazakhstan, Uzbekistan"}
 Serbia                           | 0.8650650747338458 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Hungary, Romania, Bulgaria, North Macedonia, Croatia, Bosnia and Herzegovina, Montenegro, Kosovo"}
 Kyrgyzstan                       | 0.8686683663332634 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Kazakhstan, Uzbekistan, Tajikistan, China"}
 Germany                          |  0.875407573213199 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Denmark, Poland, Czech Republic, Austria, Switzerland, France, Luxembourg, Belgium, Netherlands"}
 Kazakhstan                       | 0.8803797543940586 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Russia, China, Kyrgyzstan, Uzbekistan, Turkmenistan"}
 Peru                             |  0.880631162635637 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Ecuador, Colombia, Brazil, Bolivia, Chile"}
 Russian Federation               | 0.8882745824724432 | f              | {"land": true, "water": false, "island": false, "continent": false, "neighbours": "Norway, Finland, Estonia, Latvia, Lithuania, Poland, Belarus, Ukraine, Georgia, Azerbaijan, Kazakhstan, China, Mongolia, North Korea"}
(194 rows)

Enter fullscreen mode Exit fullscreen mode

This shows a strong global trend: island countries overwhelmingly top the ranking, while most mainland countries are at the bottom with is_true_result = false.

Still, is_true() and extract() can disagree (e.g., Bahamas, Dominican Republic, Haiti, Papua New Guinea, Philippines, UK). They are separate model calls, with different prompt structures and decision criteria.

Distance is not a “degree of islandness”. Iceland at 0.52 and Madagascar at 0.65 do not mean Iceland is “more of an island” than the others. The distance reflects similarity to the embedding of the phrase "is an island", along with whatever semantics the country embedding captures. Maybe the name "Iceland" is simply very similar to "Island".

Borderline entities—shared-island states, islands with land borders, continent/island ambiguity, and coastal-but-not-island states—create false negatives and positives.

I've extracted more information about being surrounded by water and not being a continent, because that's the Wikipedia definition of an island:

An island is an area of land surrounded by water on all sides that is distinct from a continent. There is no standard of size that distinguishes islands and continents. Continents have an accepted geological definition – they are the largest landmass of a particular tectonic plate. Islands can occur in any body of water, including lakes, rivers, and seas. Low-tide elevations, areas of land that are not above the surface during a high tide, are generally not considered islands.

In the query results, is_true() returned false for the Bahamas, which is clearly an island state. Perhaps the model prompts interpreted “is an island” too literally as “a single island,” even though the Bahamas is an archipelago of hundreds of islands.

I also extracted neighboring countries, since islands generally have none, though there are exceptions. Ireland is geographically an island, and this was flagged correctly, but the country has a land border with Northern Ireland.

I cross-checked the neighbors list because it was generated by the language model, not retrieved from a geographical knowledge base. 178 of the 194 entries are correct. Some differences come from implicit assumptions about what counts as the country’s territory, for example France without French Guiana's borders with Brazil and Suriname, Spain without its North African borders with Morocco, or Denmark without Greenland’s border with Canada. But there are also clear errors, such as Congo having no neighbors at all. Plausible is not the same as true.

Language models (LLMs) are not knowledge bases

Even if there’s an approximate correlation between island attributes and vector distance, with most island attributes appearing below 0.7 and non-island attributes above 0.8, there’s no clear threshold:

The distance reflects the embedding model’s linguistic perception of “island”, not a geographical definition of what an island is.

It is also important to understand which models, prompts, and output formats are involved in these functions.

I ran this on HorizonDB with the managed default models enabled:


postgres=> select alias, model_name, status from model_registry.model_list_all()
;
       alias       |       model_name        |   status
-------------------+-------------------------+------------
 default-chat      | gpt-5.4                 | registered
 default-embedding | text-embedding-3-small  | registered
 default-reranker  | Cohere-rerank-v4.0-fast | registered
(3 rows)

Enter fullscreen mode Exit fullscreen mode

Embedding generation through create_embeddings() uses default-embedding, which in this configuration is text-embedding-3-small. The <=> operator itself doesn't call a model but calculates the cosine distance between the vectors generated by that embedding model.

is_true() and extract() both use default-chat, which here is GPT-5.4, but they make independent calls with different prompts and output contracts. is_true() sends the statement for evaluation and returns a Boolean, or NULL when the result is inconclusive, while extract() asks the same model to extract structured data according to the fields, types, and descriptions supplied in the data argument.

So even when is_true() and extract() use the same underlying model, they don't ask the same question in the same way, and their answers don't have to agree.

Understanding the answer

Here’s a query that more clearly defines what counts as an island for the is_true() function and extracts the reason:

WITH defs AS (
  SELECT '
Definition (apply strictly):
1) Island country = a sovereign state whose core territory is on one or more islands.
2) Archipelagic states count as island countries.
3) A state sharing an island with another state still counts as an island country.
4) Having a land border does NOT disqualify island country status.
5) Mainland states with offshore islands do NOT count.
6) If uncertain, return "uncertain".
'::text AS d
),
qvec AS (
  SELECT azure_openai.create_embeddings(
    input => 'island country',
    dimensions => 1536
  )::vector(1536) AS v
),
ranked AS (
  SELECT
    c.country,
    c.region,
    c.embedding <=> q.v AS distance
  FROM countries c
  CROSS JOIN qvec q
  ORDER BY distance LIMIT 25 -- limit to a relevant subset
)
SELECT
  r.country,
  r.region,
  r.distance,

  -- strict boolean gate (uncertain forced to false)
  azure_ai.is_true(
    statement =>
      d.d || E'\nCountry: ' || r.country ||
      E'\nAnswer true only if this is an island country under the definition above. ' ||
      'If uncertain, answer false.'
  ) AS is_true_result,

  -- richer structured judgment (preferred for analysis)
  e.extracted,
  e.extracted->>'island_country' AS island_country_class,
  (e.extracted->>'confidence')::numeric AS confidence

FROM ranked r
CROSS JOIN defs d
CROSS JOIN LATERAL (
  SELECT azure_ai.extract(
    document =>
      d.d || E'\nCountry: ' || r.country || E'\nRegion: ' || coalesce(r.region, ''),
    data => ARRAY[
      'island_country: string - one of yes,no,uncertain',
      'confidence: number - 0 to 1',
      'archipelago_state: boolean - true if mostly an archipelago',
      'has_land_border: boolean - true if at least one land border exists',
      'mainland_core_territory: boolean - true if core territory is continental mainland',
      'reason: string - short rationale using the definition'
    ]
  )::jsonb AS extracted
) e
ORDER BY r.distance
;

        country        |    region     |      distance       | is_true_result |                                                                                                                                                                                      extracted                                                                                                                                                                                       | island_country_class | confidence
-----------------------+---------------+---------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+------------
 Iceland               | EUROPE        | 0.44426333275809626 | t              | {"reason": "Iceland is a sovereign state whose core territory is on the island of Iceland (with smaller surrounding islands), so it qualifies as an island country; it has no land borders and is not a mainland state with offshore islands.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": false, "mainland_core_territory": false} | yes                  |       0.99
 Solomon Islands       | OCEANIA       | 0.48757740563263785 | t              | {"reason": "Solomon Islands is a sovereign state whose core territory consists of islands in Oceania; it is an archipelagic state and has no continental mainland core territory.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                              | yes                  |       0.99
 New Zealand           | OCEANIA       |   0.506055857678485 | t              | {"reason": "New Zealand’s core sovereign territory is on islands in Oceania; it is an archipelagic island state and has no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                                                       | yes                  |       0.99
 Dominica              | NORTH AMERICA |  0.5209628128173232 | t              | {"reason": "Dominica is a sovereign state whose entire core territory is the island of Dominica in the Caribbean; it is not a mainland state and has no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": false, "mainland_core_territory": false}                                                                         | yes                  |       0.99
 Marshall Islands      | OCEANIA       |  0.5250148712915059 | t              | {"reason": "Marshall Islands is a sovereign archipelagic state in Oceania composed entirely of islands and atolls, with no continental mainland core territory and no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                            | yes                  |       0.99
 Bahamas               | NORTH AMERICA |  0.5256117534657789 | t              | {"reason": "The Bahamas is a sovereign archipelagic state whose core territory consists of islands; it has no continental mainland core and no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                                   | yes                  |       0.99
 Jamaica               | NORTH AMERICA |  0.5267649045134026 | t              | {"reason": "Jamaica is a sovereign state whose core territory is the island of Jamaica in the Caribbean; it is not a mainland state with merely offshore islands and has no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": false, "mainland_core_territory": false}                                                     | yes                  |       0.99
 Ireland               | EUROPE        |  0.5295232013728273 | t              | {"reason": "Ireland’s sovereign core territory is on the island of Ireland; sharing that island with another state and having a land border do not disqualify it.", "confidence": 0.99, "island_country": "yes", "has_land_border": true, "archipelago_state": false, "mainland_core_territory": false}                                                                              | yes                  |       0.99
 Seychelles            | AFRICA        |  0.5305732376235346 | t              | {"reason": "Seychelles is a sovereign archipelagic state whose core territory consists entirely of islands, with no continental mainland territory or land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                            | yes                  |       0.99
 Barbados              | NORTH AMERICA |  0.5382523965644609 | t              | {"reason": "Barbados is a sovereign state whose core territory is the island of Barbados in the Caribbean; it is not a mainland state and has no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": false, "mainland_core_territory": false}                                                                                | yes                  |       0.99
 Kiribati              | OCEANIA       |  0.5414779178766208 | t              | {"reason": "Kiribati is a sovereign state composed of island groups in Oceania; its core territory is entirely on islands, making it an archipelagic island country with no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                      | yes                  |       0.99
 Mauritius             | AFRICA        |  0.5486025794741096 | t              | {"reason": "Mauritius is a sovereign island state in the Indian Ocean; its core territory consists of islands (an archipelagic state) and it has no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                              | yes                  |       0.99
 Fiji                  | OCEANIA       |  0.5491649050391756 | t              | {"reason": "Fiji is a sovereign archipelagic state in Oceania whose core territory consists of islands, with no continental mainland core territory and no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                       | yes                  |       0.99
 Guyana                | SOUTH AMERICA |  0.5523418078602229 | f              | {"reason": "Guyana’s core sovereign territory is on the South American mainland, not on one or more islands. It has land borders and is not an archipelagic state.", "confidence": 0.99, "island_country": "no", "has_land_border": true, "archipelago_state": false, "mainland_core_territory": true}                                                                               | no                   |       0.99
 Nauru                 | OCEANIA       |  0.5566559110627669 | t              | {"reason": "Nauru is a sovereign state whose entire core territory is a single island in Oceania; it has no mainland core territory and no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": false, "mainland_core_territory": false}                                                                                      | yes                  |       0.99
 Antigua and Barbuda   | NORTH AMERICA |  0.5591857321247522 | t              | {"reason": "Antigua and Barbuda is a sovereign state whose core territory consists of islands in the Caribbean, so it qualifies as an island country; it has no mainland core territory or land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                       | yes                  |       0.99
 Tonga                 | OCEANIA       |  0.5593505837846235 | t              | {"reason": "Tonga is a sovereign state in Oceania composed of many islands (an archipelago), with its core territory entirely on islands and no continental mainland core.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                     | yes                  |       0.99
 Sri Lanka             | ASIA          |  0.5598629197063197 | t              | {"reason": "Sri Lanka is a sovereign state whose core territory is the island of Sri Lanka; it is not a mainland state with offshore islands and has no land border.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": false, "mainland_core_territory": false}                                                                          | yes                  |       0.99
 Maldives              | ASIA          |  0.5669124653875222 | t              | {"reason": "Maldives is a sovereign archipelagic state composed entirely of islands, with no continental mainland core territory and no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                                          | yes                  |       0.99
 Palau                 | OCEANIA       |  0.5680937487620257 | t              | {"reason": "Palau is a sovereign state in Oceania composed of islands; its core territory is entirely insular and forms an archipelago, with no continental mainland core.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                     | yes                  |       0.99
 Vanuatu               | OCEANIA       |   0.571503021541246 | t              | {"reason": "Vanuatu is a sovereign archipelagic state in Oceania whose core territory consists entirely of islands; it has no continental mainland core and no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                   | yes                  |       0.99
 Saint Kitts and Nevis | NORTH AMERICA |     0.5734250081079 | t              | {"reason": "Saint Kitts and Nevis is a sovereign state whose core territory consists of the islands of Saint Kitts and Nevis; it is an island/archipelagic state with no continental mainland core territory and no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}              | yes                  |       0.99
 Tuvalu                | OCEANIA       |  0.5794875078571311 | t              | {"reason": "Tuvalu is a sovereign state in Oceania composed entirely of low-lying islands/atolls, so its core territory is on islands and it has no continental mainland core.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                                 | yes                  |       0.99
 Indonesia             | ASIA          |  0.5823673545137962 | t              | {"reason": "Indonesia is a sovereign archipelagic state whose core territory consists of islands; it also has land borders on shared islands, which does not disqualify it.", "confidence": 0.99, "island_country": "yes", "has_land_border": true, "archipelago_state": true, "mainland_core_territory": false}                                                                     | yes                  |       0.99
 Samoa                 | OCEANIA       |  0.5861267449326069 | t              | {"reason": "Samoa is a sovereign state in Oceania whose core territory consists of islands (mainly Upolu and Savai'i), making it an island/archipelagic state with no land borders.", "confidence": 0.99, "island_country": "yes", "has_land_border": false, "archipelago_state": true, "mainland_core_territory": false}                                                            | yes                  |       0.99
(25 rows)
Enter fullscreen mode Exit fullscreen mode

In this query I didn't use the vector distance as a probability of truth. I used it only to select a subset of candidates (ORDER BY distance LIMIT 25) for further analysis. With a vector index such as HNSW or DiskANN, candidate retrieval can be efficient even over a large dataset. Then the calls to is_true() or extract() are limited to this subset.

This provides more accurate results and more information to fine-tune the query. Still, you may wonder whether the <=> distance is sufficient, or whether a threshold defines what counts as an island. I’d be cautious about that. In this example, Iceland and Samoa are both islands, but they score differently: 0.44 vs. 0.58. So the score captures more than simply whether a place is an island. For example, I suspect countries with "Island" in their names, or similar, rank higher. "Iceland" ranks at the top, and its name differs from "Island" by just one letter. Let's run a quick test to see whether this lexical similarity may influence the ranking:

postgres=> WITH q AS (
  SELECT azure_openai.create_embeddings(
    input => 'island country',
    dimensions => 1536
  )::vector(1536) AS v
),
names(name) AS (
  VALUES
    ('Iceland'),('Ireland'),('Islandia'),('NotAnIsland'),('Finland'),('Thailand'),('Atlantide'),('Ile de France'),('Null Island')
)
SELECT
  name,
  azure_openai.create_embeddings(
    input => name,
    dimensions => 1536
  )::vector(1536) <=> q.v AS distance
FROM names, q
ORDER BY distance
;
     name      |      distance
---------------+---------------------
 Islandia      | 0.34246944579687133
 Null Island   | 0.37654763654023216
 NotAnIsland   |  0.4288926533293339
 Iceland       | 0.44426333275809626
 Ireland       |  0.5295232013728273
 Thailand      |  0.6414752852954178
 Finland       |  0.6800102097608935
 Atlantide     |  0.7162388484049518
 Ile de France |  0.7201280663813582
(9 rows)
Enter fullscreen mode Exit fullscreen mode

This is interesting: "NotAnIsland" ranks higher than Iceland, and Null Island ranks even higher, despite there being no landmass at 0°N 0°E. "Null Island" is highly similar to "island country" in the embedding model’s vector space while being factually not an island and not a country. The lexical similarity may also contribute to Iceland’s ranking.

Conclusion

Here are some practical recommendations:

  1. Treat vector distance as a retrieval tool, not as truth. In this experiment, <=> is useful for finding candidates, but it is not a factual classifier. Azure AI also provides a rank() function to rerank candidates by relevance, which is still different from determining whether a statement is true.

  2. Use explicit definitions for judgment tasks. is_true() and extract() are much more stable when the description is explicit (for example, whether archipelagos count, shared-island borders are allowed, and mainland core territory is excluded).

  3. Prefer tri-state outputs (yes / no / uncertain) over forced booleans. Forcing true/false hides ambiguity and leads to brittle false negatives and positives.

  4. Calibrate candidate thresholds using labeled data, not intuition. Build a small gold dataset, sweep distance thresholds, and report precision and recall. If you change the model, recalibrate.

  5. Separate and compare signals. Keep three columns in the evaluation: distance, is_true, and structured extract. Analyzing disagreements is often more informative than looking at aggregate accuracy.

  6. Expect model variance and prompt sensitivity. Small changes to prompts or schemas can flip edge cases. Version prompts and keep regression examples (Bahamas, Ireland, Haiti, Indonesia, UK, etc.).

  7. Do not over-interpret model-generated confidence. Here, confidence is simply a field I asked the model to generate. A value such as 0.99 is not a calibrated probability of correctness.

  8. Do not forget that LLMs are language models, not a database of facts. They bring some knowledge, but not structured enough to answer a precise question.

Use similarity search with cosine distance (<=>) to find relevant candidates, not to determine truth. On those candidates, use is_true() with a precise statement when you need a truth evaluation, and extract() with a precise definition when you need a structured answer. Similarity, relevance, and truth are different signals.

Top comments (0)