Re: 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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> 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(at)familyhealth(dot)com(dot)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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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