Re: [GENERAL] Indexes on array columns
I have tried testing the perofmance on indexing array element using standard btree and it doesn't help anything. It still costing alot. create index idx_properties_address_4 on properties ((address_arr[4])) does contrib/intarray effective for text array? On 3/16/06, Oleg Bartunov oleg@sai.msu.su wrote: contrib/intarray might help you Oleg On Wed, 15 Mar 2006, [EMAIL PROTECTED] wrote: On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote: Hi, Is it possible to put an index on an array column? Apparently yes (I just did it as a test). However, consider the following from the manual. Tip: Arrays are not sets; searching for specific array elements may be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements.[1] Arrays are attractive, but it seems they most often aren't the best solution. FOr instance, I beleive I read somewhere that the index will be on the whole array, and the individual elements are not indexed, so WHERE ary = {foo,bar} might benefit from your index, but WHERE 'foo' = ANY(ary) probably wouldn't. At least that's the impression I got from reading the archives of this list. I haven't done any testing of it. -karl 1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491 Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Indexes on array columns
Hi,Is it possible to put an index on an array column?Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Indexes on array columns
On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote: Hi, Is it possible to put an index on an array column? Apparently yes (I just did it as a test). However, consider the following from the manual. Tip: Arrays are not sets; searching for specific array elements may be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements.[1] Arrays are attractive, but it seems they most often aren't the best solution. FOr instance, I beleive I read somewhere that the index will be on the whole array, and the individual elements are not indexed, so WHERE ary = {foo,bar} might benefit from your index, but WHERE 'foo' = ANY(ary) probably wouldn't. At least that's the impression I got from reading the archives of this list. I haven't done any testing of it. -karl 1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491 Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Indexes on array columns
contrib/intarray might help you Oleg On Wed, 15 Mar 2006, [EMAIL PROTECTED] wrote: On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote: Hi, Is it possible to put an index on an array column? Apparently yes (I just did it as a test). However, consider the following from the manual. Tip: Arrays are not sets; searching for specific array elements may be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements.[1] Arrays are attractive, but it seems they most often aren't the best solution. FOr instance, I beleive I read somewhere that the index will be on the whole array, and the individual elements are not indexed, so WHERE ary = {foo,bar} might benefit from your index, but WHERE 'foo' = ANY(ary) probably wouldn't. At least that's the impression I got from reading the archives of this list. I haven't done any testing of it. -karl 1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491 Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster