Re: looking for a faster way to do that

Alban Hertroys wrote:

>> So you're comparing a variable field value to a variable pattern - yeah,
>> that's going to hurt. There's no way you could index exactly that.
>>
>> Perhaps there's some way you can transform the problem so that you get
>> something indexable?
>> For example, if your match patterns follow a certain pattern by themselves,
>> you could add a column with the longest match pattern that would match the
>> string. Then you could just do a query for which records have the match
>> pattern (in that new column) that you're looking for and voila!
>>
>> If something like that is possible strongly depends on what kind of match
>> patterns you're using, of course.

Hi Alban,

I already did that - the test set is just all records from the real table (about a million
entries) that match the common 'ABC' prefix

>>> An exact match "where items.code = n.wantcode" on the same data completes
>>> in 40 ms
>>>
>>
>> That's an exact string match, of course that will be fast ;)

The main difference is: the fast query looks like

explain select items.num, wantcode from items, n where code = wantcode;
Merge Join (cost=53.56..1104.02 rows=39178 width=36)
Merge Cond: (("outer".code)::text = "inner".wantcode)
-> Index Scan using itemsc on items (cost=0.00..438.75 rows=9614 width=42)
-> Sort (cost=53.56..55.60 rows=815 width=32)
Sort Key: n.wantcode
-> Seq Scan on n (cost=0.00..14.15 rows=815 width=32)

and the slow ones looks like that one:

Nested Loop (cost=14.15..176478.01 rows=39178 width=36)
Join Filter: (("outer".code)::text ~ "inner".wantcode)

So the database takes an entirely differnet approach at retrieving the entries.

Regards
Wolfgang Hamann