Re: looking for a faster way to do that - Mailing list pgsql-general

From hamann.w@t-online.de
Subject Re: looking for a faster way to do that
Date
Msg-id 4E7C7B9F.mailH6L11UJN5@amadeus3.local
Whole thread Raw
In response to looking for a faster way to do that  (hamann.w@t-online.de)
Responses Re: looking for a faster way to do that
List pgsql-general
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


pgsql-general by date:

Previous
From: hamann.w@t-online.de
Date:
Subject: Re: looking for a faster way to do that
Next
From: Venkat Balaji
Date:
Subject: : Checksum ERROR when restoring Online Backup