Обсуждение: mnogosearch -- pgsql seem so slow, please help me find out why

pgsql 7.1-current, mnogosearch-3.1.8, netbsd/alpha-1.5.1-current, dec
alpha 500, 1G ram, uw-scsi

i'm trying to find out why postgres is the bottle neck in my searches with
mnogosearch. i've tried both the search.c and php version of search and
they both took up to 50 seconds for a one word search. this is with the
system sitting pretty much idle.

'select * from url' from psql monitor took 59 seconds.

mnwork=# explain select * from url;
NOTICE:  QUERY PLAN:

Seq Scan on url  (cost=0.00..14473.56 rows=99256 width=144)

EXPLAIN

here is my url table:

mnwork=# \d url
                                       Table "url"
    Attribute    |          Type          |                   Modifier
-----------------+------------------------+-----------------------------------------------
 rec_id          | integer                | not null default
nextval('next_url_id'::text)
 status          | integer                | not null default 0
 url             | character varying(128) | not null
 content_type    | character varying(48)  | not null default ''
 title           | character varying(128) | not null default ''
 txt             | character varying(255) | not null default ''
 docsize         | integer                | not null default 0
 last_index_time | integer                | not null
 next_index_time | integer                | not null
 last_mod_time   | integer                |
 referrer        | integer                | not null default 0
 tag             | character varying(10)  | not null default ''
 hops            | integer                | not null default 0
 category        | character varying(10)  | not null default ''
 keywords        | character varying(255) | not null default ''
 description     | character varying(100) | not null default ''
 crc32           | integer                | not null default 0
 lang            | character varying(2)   | not null default ' '
Indices: url_crc,
         url_pkey,
         url_url


i just can't tolerate this kind of results for searching. any suggestions
to overcome this would be appreciated. tia.

"Thomas T. Thai" <tom@minnesota.com> writes:
> 'select * from url' from psql monitor took 59 seconds.

How big is the table?  Your EXPLAIN mentions 99256 rows, but I can't
tell if that stat is up-to-date or not.

A select like that is going to be pretty much all data transfer: read
the disk blocks, format the data values, send 'em to the frontend.
There's hardly anything that Postgres can do to optimize or pessimize
it.  You might shave a few milliseconds by using a binary cursor (to
avoid formatting the integer values into ASCII) but probably not a lot.

If you've done a whole lot of UPDATEs/DELETEs on the table since your
last VACUUM, then reading empty disk blocks might be costing you some
time.

            regards, tom lane

On Fri, 12 Jan 2001, Tom Lane wrote:

> "Thomas T. Thai" <tom@minnesota.com> writes:
> > 'select * from url' from psql monitor took 59 seconds.
>
> How big is the table?  Your EXPLAIN mentions 99256 rows, but I can't
> tell if that stat is up-to-date or not.

it is 99256. i don't think it's that big of a table is it? typically the
query under mnogo takes less than a second, at most a couple seconds but
not 50+ secs.

maybe Hermit has some input as he runs it for postgresql.org's search.

> A select like that is going to be pretty much all data transfer: read
> the disk blocks, format the data values, send 'em to the frontend.
> There's hardly anything that Postgres can do to optimize or pessimize
> it.  You might shave a few milliseconds by using a binary cursor (to
> avoid formatting the integer values into ASCII) but probably not a lot.
>
> If you've done a whole lot of UPDATEs/DELETEs on the table since your
> last VACUUM, then reading empty disk blocks might be costing you some
> time.

i did vacuum analyze.

"Thomas T. Thai" <tom@minnesota.com> writes:
> On Fri, 12 Jan 2001, Tom Lane wrote:
>> "Thomas T. Thai" <tom@minnesota.com> writes:
>>>> 'select * from url' from psql monitor took 59 seconds.
>>
>> How big is the table?  Your EXPLAIN mentions 99256 rows, but I can't
>> tell if that stat is up-to-date or not.

> it is 99256. i don't think it's that big of a table is it? typically the
> query under mnogo takes less than a second, at most a couple seconds but
> not 50+ secs.

Typical queries don't retrieve the whole of a large table.  SQL speed
is all about *not* doing that.  Do the math: while I don't know the
average width of your rows, it looked like 500 bytes would be in the
right ballpark.  So you're moving circa 50 megabytes of data in this
query, or a tad under a meg/second, which is not blazing but it's pretty
respectable when you consider the number of layers of software involved.
(What exactly were you doing with the data after it got to psql, BTW?
In my experience psql can chew up near as much CPU time as the backend
for this sort of bulk data display.)  To do this in under a second would
require total throughput exceeding 50 meg/second, which is probably a
good bit more than the sustained read speed your filesystem can achieve,
never mind any time for Postgres' and psql's processing.

If you want a fast search, you should be designing a WHERE clause that
exploits an index so that you *don't* read the entire darn table.

            regards, tom lane

I'm using hte mod_perl version ... with a database of some 12million words
... except with the database under load with a vacuum or some such, I
dont' find that the search is too slow ... would like to somehow get rid
of that LIKE clause, which would probably spee d thing sup some, but going
from search.c to search.mpl mae a world of difference here ...


On Fri, 12 Jan 2001, Thomas T. Thai wrote:

> On Fri, 12 Jan 2001, Tom Lane wrote:
>
> > "Thomas T. Thai" <tom@minnesota.com> writes:
> > > 'select * from url' from psql monitor took 59 seconds.
> >
> > How big is the table?  Your EXPLAIN mentions 99256 rows, but I can't
> > tell if that stat is up-to-date or not.
>
> it is 99256. i don't think it's that big of a table is it? typically the
> query under mnogo takes less than a second, at most a couple seconds but
> not 50+ secs.
>
> maybe Hermit has some input as he runs it for postgresql.org's search.
>
> > A select like that is going to be pretty much all data transfer: read
> > the disk blocks, format the data values, send 'em to the frontend.
> > There's hardly anything that Postgres can do to optimize or pessimize
> > it.  You might shave a few milliseconds by using a binary cursor (to
> > avoid formatting the integer values into ASCII) but probably not a lot.
> >
> > If you've done a whole lot of UPDATEs/DELETEs on the table since your
> > last VACUUM, then reading empty disk blocks might be costing you some
> > time.
>
> i did vacuum analyze.
>
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org