Re: mnogosearch -- pgsql seem so slow, please help me find out why

"Thomas T. Thai" <tom(at)minnesota(dot)com> writes:
> On Fri, 12 Jan 2001, Tom Lane wrote:
>> "Thomas T. Thai" <tom(at)minnesota(dot)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