Re: [GENERAL] looking for a faster way to do that

At 18:18 26/09/2011, you wrote: Eduardo Morras wrote: >> >> At 08:04 25/09/2011, haman...@t-online.de wrote: >> >> > >> select * from items where regexp_matches(items.code,'(ABC) (DE1) >> > >> (any_substring)')<>{}; >> > >> >> > >> >Hi Eduardo, >> > >> >it is clear that scanning the table once

Re: [GENERAL] looking for a faster way to do that

Alban Hertroys wrote: >> > Hi, >> >=20 >> > the strings are not really known before. >> > Let me explain the scenario; there is one table about products, and = >> code is the >> > manufacturer's (or resellers') product id. >> > So, if ABC were a maker of laptops, ABC123 and ABC456 might be two

Re: [GENERAL] looking for a faster way to do that

Eduardo Morras wrote: >> >> At 08:04 25/09/2011, haman...@t-online.de wrote: >> >> > >> select * from items where regexp_matches(items.code,'(ABC) (DE1) >> > >> (any_substring)')<>{}; >> > >> >> > >> >Hi Eduardo, >> > >> >it is clear that scanning the table once with a list of matches will >>

Re: [GENERAL] looking for a faster way to do that

You forgot to include the list ;) On 26 Sep 2011, at 6:06, haman...@t-online.de wrote: > Alban Hertroys wrote: >>> >>> To me it sounds a little bit like you're comparing every item in a = >>> warehouse to a set of descriptions to see what type of item it is, which = >>> is something you would be

Re: [GENERAL] looking for a faster way to do that

At 08:04 25/09/2011, haman...@t-online.de wrote: >> select * from items where regexp_matches(items.code,'(ABC) (DE1) >> (any_substring)')<>{}; >> Hi Eduardo, it is clear that scanning the table once with a list of matches will outperform rescanning the table for every string wanted. Now, my p

Re: [GENERAL] looking for a faster way to do that

On 25 Sep 2011, at 8:04, haman...@t-online.de wrote: > Hi Eduardo, > > it is clear that scanning the table once with a list of matches will > outperform > rescanning the table for every string wanted. Now, my problem is that the > patterns are > dynamic as well. So if I could translate a table w

Re: [GENERAL] looking for a faster way to do that

Eduardo Morras wrote: >> > >> >Hi, >> > >> >if I understand this right, it does not mean "check if the string >> >appears at position 0" >> >which could translate into an index query, but rather "check if the >> >string appears anywhere >> >and then check if that is position 0", so the entire ta

Re: [GENERAL] looking for a faster way to do that

At 14:12 23/09/2011, haman...@t-online.de wrote: Eduardo Morras wrote: >> You can try these, i doubt they will use any index but its a >> different approach: >> >> select * from items where length(items.code)<>length(rtrim(items.code,'ABC')); >> >> select * from items where strpos(items.code,'

Re: [GENERAL] looking for a faster way to do that

explain analyze select num, n.wantcode from items, n where items.code ~ n.wantcode; Nested Loop (cost=20.00..216502.14 rows=48070 width=36) (actual time=148.479..336280.488 rows=2871 loops=1) Join Filter: (("outer".code)::text ~ "inner".wantcode) -> Seq Scan on items (cost=0.00..167.14 row

Re: [GENERAL] looking for a faster way to do that

>> > >> > 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 >> >> I think you misunderstood what I wrote. Notice the difference between >> "which strings match the pattern" and "which

Re: [GENERAL] looking for a faster way to do that

On 23 September 2011 14:29, wrote: > > 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 > >>

Re: [GENERAL] 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 matc

Re: [GENERAL] looking for a faster way to do that

Eduardo Morras wrote: >> You can try these, i doubt they will use any index but its a >> different approach: >> >> select * from items where >> length(items.code)<>length(rtrim(items.code,'ABC')); >> >> select * from items where strpos(items.code,'ABC')=0 or >> strpos(items.code,'any_substrin

Re: [GENERAL] looking for a faster way to do that

On 23 September 2011 09:45, wrote: > Alban Hertroys wrote: > > >> What is the output of explain? > >> > >> You say 'the other table', so presumably we're dealing with a foreign > key > >> here. Is there an index on that column? > > Albe Laurenz wrote: > > >> Is the index used for "where code ~ '

Re: [GENERAL] looking for a faster way to do that

At 09:45 23/09/2011, haman...@t-online.de wrote: A single anchored query select * from items where code ~ '^ABC'; does indeed use the index to retrieve data. So I wonder whether there might be a different approach to this problem rather than pattern matching. I recall I had a similar problem

Re: [GENERAL] looking for a faster way to do that

Alban Hertroys wrote: >> What is the output of explain? >> >> You say 'the other table', so presumably we're dealing with a foreign key >> here. Is there an index on that column? Albe Laurenz wrote: >> Is the index used for "where code ~ '^ABC3563'"? >> >> If not, then the result is fast only

Re: [GENERAL] looking for a faster way to do that

On 21 September 2011 17:59, wrote: > If I try to get many items on a prefix match > select code where code ~ wantcode > things go very slow. Explain shows a nested loop, so seemingly the table is > rescanned > for every wanted item in the other table. A test run (3000 wanted codes > against

Re: [GENERAL] looking for a faster way to do that

hamann.w wrote:Gesendet: Mi 2011-09-21 17:59 > I have one large table (about a million entries) with an indexed column > containing codes > like ABC3561A, ABC3563X, 72-451-823 etc. (lots of order numbers from different > manufacturers) > > When I ask for a specific i