Re: looking for a faster way to do that

hamann.w <mailto:hamann(dot)w(at)t-online(dot)de> 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 item
> select code .... where code = 'ABC3563X'
> I get fast result. I also get fast result when doing a prefix match
> select code .... where code ~ '^ABC3563'
>
> If a am retrieving many items by joining with another table
> select code ..... where code = wantcode
> this is still fast.
> 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 a
> shortened table of 10000 ones) took about 200 seconds to complete
>
> What other queries could I use to get the requested selection?

Is the index used for "where code ~ '^ABC3563'"?

If not, then the result is fast only because the table is scanned only once,
and it's just the factor of 3000 that's killing you.

The second query (where code ~ wantcode) can never use an index because
the pattern "wantcode" is unknown at query planning time.

Yours,
Laurenz Albe