Re: [HACKERS] Really slow query on 6.4.2

"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