> Second, CLUSTER takes forever because it is moving all over the disk
> retrieving each row in order.
>
> So, to your solution. CLUSTER is too slow. The disk is going crazy
> moving single rows into the temp table. I recommend doing a COPY of
> artist_fti to a flat file, doing a Unix 'sort' on the flat file, then
> re-loading the data into the artist_fti, and then putting the index on
> the table and vacuum.
>
> I have done this, and now all searches are instantaneous THE FIRST TIME
> and every time.
>
> With this change, I am anxious to hear how fast you can now do your
> multi-word searches. Daily changes will not really impact performance
> because they are a small part of the total search, but this process of
> COPY/sort/reload/reindex will need to be done on a regular basis to
> maintain performance.
One more piece of good news. The reason CLUSTER was so slow is because
you loaded massive unordered amounts of data into the system. Once you
do the COPY out/reload, subsequent clusters will run very quickly,
because 99% of the data is already ordered. Only the new/changed data
is unordered, so you should be able to rapidly run CLUSTER from then on
to keep good performance.
I think a user module allowing this word fragment searching will be a
big hit with users.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)