Re: [HACKERS] Really slow query on 6.4.2

The vacuum analyze did it. It's fast now. Thanks a bunch.
rich.

On Wed, 24 Mar 1999, Tom Lane wrote:

> "Postgres mailing lists" <postgres(at)weblynk(dot)com> writes:
> > Anyway, I'm using 6.4.2 and execute the following query in psql, piping the
> > results to a file:
> > "select autos.*, owners.name, owners.email, owners.dphone, owners.ephone,
> > owners.zip, owners.country from autos, owners where autos.ownerid =
> > owners.id;"
> > This takes about 60 seconds at 0% idle CPU, with the backend taking all the
> > time. The file ends up about 3MB. Both tables have between 1200 and 1600
> > rows with about 25 and 7 columns respectively.
>
> Have you done a "vacuum analyze" lately? Sounds like the thing is using
> a nested loop query plan, which is appropriate for tiny tables but not
> for large ones. You could check this by seeing what EXPLAIN says.
>
> Unfortunately, if you haven't done a vacuum, the system effectively
> assumes that all your tables are tiny. I think this is a brain-dead
> default, but haven't had much luck convincing anyone else that the
> default should be changed.
>
> regards, tom lane
>