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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
