From: Dag Lem <dag@nimrod.no>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: daitch_mokotoff module
Organization: Nimrod
References: <20221223132559.mauqerlf75d7jnuq@alvherre.pgsql>
	<ygev8m1visd.fsf@sid.nimrod.no>
Date: Sat, 24 Dec 2022 08:13:07 +0100
In-Reply-To: <ygev8m1visd.fsf@sid.nimrod.no> (Dag Lem's message of "Fri, 23
	Dec 2022 23:48:02 +0100")
Message-ID: <yger0wpuvek.fsf@sid.nimrod.no>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/yger0wpuvek.fsf%40sid.nimrod.no>
Precedence: bulk

Dag Lem <dag@nimrod.no> writes:

> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>
>> On 2022-Dec-23, Alvaro Herrera wrote:
>>
>
> [...]
>
>> I tried downloading a list of surnames from here
>> https://www.bibliotecadenombres.com/apellidos/apellidos-espanoles/
>> pasted that in a text file and \copy'ed it into a table.  Then I ran
>> this query
>>
>> select string_agg(a, ' ' order by a), daitch_mokotoff(a), count(*)
>> from apellidos
>> group by daitch_mokotoff(a)
>> order by count(*) desc;
>>
>> so I have a first entry like this
>>
>> string_agg =E2=94=82 Balasco Balles Belasco Belles Blas Blasco Fallas Fe=
liz
>> Palos Pelaez Plaza Valles Vallez Velasco Velez Veliz Veloz Villas
>> daitch_mokotoff =E2=94=82 784000
>> count           =E2=94=82 18
>>
>> but then I have a bunch of other entries with the same code 784000 as
>> alternative codes,
>>
>> string_agg      =E2=94=82 Velazco
>> daitch_mokotoff =E2=94=82 784500 784000
>> count           =E2=94=82 1
>>
>> string_agg      =E2=94=82 Palacio
>> daitch_mokotoff =E2=94=82 785000 784000
>> count           =E2=94=82 1
>>
>> I suppose I need to group these together somehow, and it would make more
>> sense to do that if the values were arrays.
>>
>>
>> If I scroll a bit further down and choose, say, 794000 (a relatively
>> popular one), then I have this
>>
>> string_agg =E2=94=82 Barraza Barrios Barros Bras Ferraz Frias Frisco Par=
ras
>> Peraza Peres Perez Porras Varas Veras
>> daitch_mokotoff =E2=94=82 794000
>> count           =E2=94=82 14
>>
>> and looking for that code in the result I also get these three
>>
>> string_agg      =E2=94=82 Barca Barco Parco
>> daitch_mokotoff =E2=94=82 795000 794000
>> count           =E2=94=82 3
>>
>> string_agg      =E2=94=82 Borja
>> daitch_mokotoff =E2=94=82 790000 794000
>> count           =E2=94=82 1
>>
>> string_agg      =E2=94=82 Borjas
>> daitch_mokotoff =E2=94=82 794000 794400
>> count           =E2=94=82 1
>>
>> and then I see that I should also search for possible matches in codes
>> 795000, 790000 and 794400, so that gives me
>>
>> string_agg =E2=94=82 Baria Baro Barrio Barro Berra Borra Feria Para Parra
>> Perea Vera
>> daitch_mokotoff =E2=94=82 790000
>> count           =E2=94=82 11
>>
>> string_agg      =E2=94=82 Barriga Borge Borrego Burgo Fraga
>> daitch_mokotoff =E2=94=82 795000
>> count           =E2=94=82 5
>>
>> string_agg      =E2=94=82 Borjas
>> daitch_mokotoff =E2=94=82 794000 794400
>> count           =E2=94=82 1
>>
>> which look closely related (compare "Veras" in the first to "Vera" in
>> the later set.  If you ignore that pseudo-match, you're likely to miss
>> possible family relationships.)
>>
>>
>> I suppose if I were a genealogy researcher, I would be helped by having
>> each of these codes behave as a separate unit, rather than me having to
>> split the string into the several possible contained values.
>
> It seems to me like you're trying to use soundex coding for something it
> was never designed for.
>
> As stated in my previous mail, soundex algorithms are designed to index
> names on some representation of sound, so that alike sounding names with
> different spellings will match, and as shown in the documentation
> example, that is exactly what the implementation facilitates.
>
> Daitch-Mokotoff Soundex indexes alternative sounds for the same name,
> however if I understand correctly, you want to index names by single
> sounds, linking all alike sounding names to the same soundex code. I
> fail to see how that is useful - if you want to find matches for a name,
> you simply match against all indexed names. If you only consider one
> sound, you won't find all names that match.
>
> In any case, as explained in the documentation, the implementation is
> intended to be a companion to Full Text Search, thus text is the natural
> representation for the soundex codes.
>
> BTW Vera 790000 does not match Veras 794000, because they don't sound
> the same (up to the maximum soundex code length).
>

I've been sleeping on this, and perhaps the normal use case can just as
well (or better) be covered by the "@>" array operator? I originally
implemented similar functionality using another soundex algorithm more
than a decade ago, and either arrays couldn't be GIN indexed back then,
or I simply missed it. I'll have to get back to this - now it's
Christmas!

Merry Christmas!

Best regards,

Dag Lem