best practise/pattern for large OR / LIKE searches

From: Ries van Twisk <pg(at)rvt(dot)dds(dot)nl>
To: "pgsql-general(at)postgresql(dot)org mailing list" <pgsql-general(at)postgresql(dot)org>
Subject: best practise/pattern for large OR / LIKE searches
Date: 2009-08-26 04:18:55
Message-ID: AD0AF454-6481-441B-A5EE-A5B5E1EE2BFE@rvt.dds.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey All,

I am wondering if there is a common pattern for these sort of queries :

SELECT * FROM tbl WHERE datanumber LIKE '%12345%' OR LIKE '%54321%' OR
LIKE '%8766%' OR LIKE '%009%', ..

The number of OR/LIKES are in the order of 50-100 items...
the table tbl is a couple of million rows.

The datanumber is a string that are maximum 10 characters long, no
spaces and can contain numbers and letters.

Apart from creating a couple of index table to make the LIKE left
anchored something like this :

tbl <----> tbl_4letters
tbl <----> tbl_5letters
tbl <----> tbl_3letters

or creating a functional index 'of some sort' are there any other
brilliant ideas out there to solve such a problem (GIN/GIS???) ?

Searches are currently taking to long and we would like to optimize
them, but before we dive into our own solution we
where wondering if there already common solutions for this...

Kind Regards,
Ries van Twisk

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2009-08-26 06:51:21 Re: best practise/pattern for large OR / LIKE searches
Previous Message Mark Kirkwood 2009-08-26 02:46:58 Re: [Skytools-users] WAL Shipping + checkpoint