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
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 |