Thread: fate of CLUSTER command ?
I just tried CLUSTER command at fts.postgresql.org to cluster fts index and got very visual performance win. Unfortunately I had to restore permissions and recreate other indices by hand. So, I'm interested what's a future of CLUSTER command ? Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov dijo: > I just tried CLUSTER command at fts.postgresql.org to cluster > fts index and got very visual performance win. Unfortunately > I had to restore permissions and recreate other indices by hand. > So, I'm interested what's a future of CLUSTER command ? I'm working on CLUSTER. I have a problem with dependency tracking right now that I need to get fixed before the patch gets accepted, but that shouldn't take long (hopefully). The patch supposedly fixes all the concerns about CLUSTER (permissions, other indexes, inheritance). -- Alvaro Herrera (<alvherre[a]atentus.com>) Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")
On Sat, 3 Aug 2002, Alvaro Herrera wrote: > Oleg Bartunov dijo: > > > I just tried CLUSTER command at fts.postgresql.org to cluster > > fts index and got very visual performance win. Unfortunately > > I had to restore permissions and recreate other indices by hand. > > So, I'm interested what's a future of CLUSTER command ? > > I'm working on CLUSTER. I have a problem with dependency tracking right > now that I need to get fixed before the patch gets accepted, but that > shouldn't take long (hopefully). > > The patch supposedly fixes all the concerns about CLUSTER (permissions, > other indexes, inheritance). > God news. Will it go to 7.3 ? > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov dijo: > On Sat, 3 Aug 2002, Alvaro Herrera wrote: > > > Oleg Bartunov dijo: > > > > > I just tried CLUSTER command at fts.postgresql.org to cluster > > > fts index and got very visual performance win. Unfortunately > > > I had to restore permissions and recreate other indices by hand. > > > So, I'm interested what's a future of CLUSTER command ? > > > > I'm working on CLUSTER. I have a problem with dependency tracking right > > now that I need to get fixed before the patch gets accepted, but that > > shouldn't take long (hopefully). > > God news. Will it go to 7.3 ? In fact, I have just corrected the error and am submitting the patch for revision and possible inclusion. Please test it and check if it does what you need. Let me know if it doesn't, because it should. -- Alvaro Herrera (<alvherre[a]atentus.com>) "Find a bug in a program, and fix it, and the program will work today. Show the program how to find and fix a bug, and the program will work forever" (Oliver Silfridge)
Oleg Bartunov wrote: > I just tried CLUSTER command at fts.postgresql.org to cluster > fts index and got very visual performance win. Unfortunately > I had to restore permissions and recreate other indices by hand. > So, I'm interested what's a future of CLUSTER command ? Yes, I have always liked CLUSTER with full text searches because you are usually hitting multiple rows with a single equaltiy restriction, and CLUSTER puts all the hits on the same page. If you look in contrib/fulltextindex, you will see mention of CLUSTER in the README. It may make sense to add that to your documentation. Also, is there any value to contrib/fulltextindex now that we have contrib/tsearch? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Also, let me add that CLUSTER in 7.3 will be fully functional because we will no longer be changing the oid of the table during cluster. This will allow people to use CLUSTER more frequently/safely. --------------------------------------------------------------------------- Bruce Momjian wrote: > Oleg Bartunov wrote: > > I just tried CLUSTER command at fts.postgresql.org to cluster > > fts index and got very visual performance win. Unfortunately > > I had to restore permissions and recreate other indices by hand. > > So, I'm interested what's a future of CLUSTER command ? > > Yes, I have always liked CLUSTER with full text searches because you are > usually hitting multiple rows with a single equaltiy restriction, and > CLUSTER puts all the hits on the same page. > > If you look in contrib/fulltextindex, you will see mention of CLUSTER in > the README. It may make sense to add that to your documentation. > > Also, is there any value to contrib/fulltextindex now that we have > contrib/tsearch? > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Yes, I have always liked CLUSTER with full text searches because you are > usually hitting multiple rows with a single equaltiy restriction, and > CLUSTER puts all the hits on the same page. > > If you look in contrib/fulltextindex, you will see mention of CLUSTER in > the README. It may make sense to add that to your documentation. > > Also, is there any value to contrib/fulltextindex now that we have > contrib/tsearch? I haven't looked at tsearch yet, but I expect it's way better than fulltextindex. However there's more than a few of us using fulltextindex, so I think it will need to stay for some while. I'm working on a new version of it for 7.3. I can put pointers in the README about checking out tsearch... Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Also, is there any value to contrib/fulltextindex now that we have
>> contrib/tsearch?
> I haven't looked at tsearch yet, but I expect it's way better than
> fulltextindex. However there's more than a few of us using fulltextindex,
> so I think it will need to stay for some while.
Right, at least a couple releases.
> I'm working on a new version of it for 7.3.
What have you got in mind?
regards, tom lane
On Sat, 3 Aug 2002, Bruce Momjian wrote: > Oleg Bartunov wrote: > > I just tried CLUSTER command at fts.postgresql.org to cluster > > fts index and got very visual performance win. Unfortunately > > I had to restore permissions and recreate other indices by hand. > > So, I'm interested what's a future of CLUSTER command ? > > Yes, I have always liked CLUSTER with full text searches because you are > usually hitting multiple rows with a single equaltiy restriction, and > CLUSTER puts all the hits on the same page. > > If you look in contrib/fulltextindex, you will see mention of CLUSTER in > the README. It may make sense to add that to your documentation. > I have to play to get feeling. I don't understand what happens if rows will be added to clustered table. Also, what will happens if there are several other indices on the same table ? Does clustering on one index will decrease performance of queries based on another indices ? > Also, is there any value to contrib/fulltextindex now that we have > contrib/tsearch? > they 're different things. > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
On Sun, 4 Aug 2002, Christopher Kings-Lynne wrote: > > Yes, I have always liked CLUSTER with full text searches because you are > > usually hitting multiple rows with a single equaltiy restriction, and > > CLUSTER puts all the hits on the same page. > > > > If you look in contrib/fulltextindex, you will see mention of CLUSTER in > > the README. It may make sense to add that to your documentation. > > > > Also, is there any value to contrib/fulltextindex now that we have > > contrib/tsearch? > > I haven't looked at tsearch yet, but I expect it's way better than > fulltextindex. However there's more than a few of us using fulltextindex, > so I think it will need to stay for some while. I'm working on a new > version of it for 7.3. > I'm totally agre with Chris. FTI is something another thing. FTI is good for more or less static document collection - a cost of insert if high for inverted indices. We've developed tsearch keeping in mind incremental update. FTI should be faster for short queries while tsearch is better for long one. tsearch development focused also on real IR support - language support, indexing of specified classes of lexemes , etc. We laready have OpenFTS which has these features, but we want to move all functionality to tsearch. > I can put pointers in the README about checking out tsearch... > > Chris > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
> > I'm working on a new version of it for 7.3. > > What have you got in mind? Well I have patches from Florian and someone else. Some wide character stuff, non-indexable word support, full word match search support, speed and space optimisations, etc. I'm just trying to set it up in a backwards-compatible way... I want the contrib to build two separate .so files... Chris
Oleg Bartunov wrote: > On Sat, 3 Aug 2002, Bruce Momjian wrote: > > > Oleg Bartunov wrote: > > > I just tried CLUSTER command at fts.postgresql.org to cluster > > > fts index and got very visual performance win. Unfortunately > > > I had to restore permissions and recreate other indices by hand. > > > So, I'm interested what's a future of CLUSTER command ? > > > > Yes, I have always liked CLUSTER with full text searches because you are > > usually hitting multiple rows with a single equaltiy restriction, and > > CLUSTER puts all the hits on the same page. > > > > If you look in contrib/fulltextindex, you will see mention of CLUSTER in > > the README. It may make sense to add that to your documentation. > > > > I have to play to get feeling. I don't understand what happens if > rows will be added to clustered table. Also, what will happens if > there are several other indices on the same table ? Does clustering > on one index will decrease performance of queries based on another > indices ? Clustering on one index doesn't decrease the performance of the other indexes. Also, only >=7.3 will preserve all indexes during cluster. > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Clustering on one index doesn't decrease the performance of the other > indexes. Also, only >=7.3 will preserve all indexes during cluster. Sure it must? Since you are rearranging all on-disk rows to match a particular index (say user_id, username) then it will slow down other indexes (eg one just on username). Chris
Christopher Kings-Lynne wrote: > > Clustering on one index doesn't decrease the performance of the other > > indexes. Also, only >=7.3 will preserve all indexes during cluster. > > Sure it must? Since you are rearranging all on-disk rows to match a > particular index (say user_id, username) then it will slow down other > indexes (eg one just on username). It will slow down other index scans only if there was some clustering on those indexes before you ran the CLUSTER command. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Sun, Aug 04, 2002 at 11:17:03PM -0400, Bruce Momjian wrote: > Christopher Kings-Lynne wrote: > > > Clustering on one index doesn't decrease the performance of the other > > > indexes. Also, only >=7.3 will preserve all indexes during cluster. > > > > Sure it must? Since you are rearranging all on-disk rows to match a > > particular index (say user_id, username) then it will slow down other > > indexes (eg one just on username). > > It will slow down other index scans only if there was some clustering on > those indexes before you ran the CLUSTER command. Actually, it would depend on the level of correlation between the values indexed. If there's some correlation, performance using the second index could improve some - if they're anti-correlated, it will decrease. If uncorrelated, there should be no effect. Ross