Re: Mnogosearch (Was: Re: website doc search is ... ) - Mailing list pgsql-general

From Jeff Davis
Subject Re: Mnogosearch (Was: Re: website doc search is ... )
Date
Msg-id 1073291262.32093.134.camel@jeff
Whole thread Raw
In response to Re: Mnogosearch (Was: Re: website doc search is ... )  ("Marc G. Fournier" <scrappy@postgresql.org>)
List pgsql-general
> The searches are designed so that you can do sub-section searches ... ie.
> if you only wanted to search hackers, the LIKE would be:
>
> 'http://archives.postgresql.org/pgsql-hackers/%%'
>
> while:
>
> 'http://archives.postgresql.org/%%'
>
> would give you a search of *all* the mailing lists ...
>
> In theory, you could go smaller and search on:
>
> 'http://archives.postgresql.org/pgsql-hackers/2003-11/%% for all messages
> in November of 2003 ...
>

That doesn't stop you from using an extra site attribute to speed up the
typical cases of documentation searches. You could just have an int
field or something (basically enumerating the most important sites) and
have the default set to be the result of a function. Then just make a
functional index, and maybe even cluster by that attribute.

The function I am describing would have the basic form:

function site_type(url text) returns int as '
if url like 'archives.postgresql.org%' then return 1
else if url like 'www.postgresql.org/docs/%' then return 2
...
'

That way you shouldn't have to change the code that inserts into the
table, only the code that does the search.

If the table was clustered at the time of each change to the
documentation, I couldn't imagine that the documentation searches would
even take a second.

Also, even though it's kind of a performance optimization hack, it
doesn't seem unreasonable to store the smaller document sets in a
seperate table, and then have a view of the union of the two tables.
That might help the server cache the right files for quick access.

Neither of these ideas would seem to have much impact on the flexibility
of the system you designed. Both are just some optimization things that
would give a good impression to the people doing quick documentation
searches.

Regards,
    Jeff





pgsql-general by date:

Previous
From: "Geoffrey van Dooren"
Date:
Subject: unsubscribe
Next
From: Tom Lane
Date:
Subject: Re: website doc search is extremely SLOW