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