Hi,
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?
Regards
Wolfgang