Not sure if I should post this here, but it seemed kinda appropriate.
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.
A simpler query like:
"select * from autos;" takes about a second at about 50% idle, and produces
a similiar amount of data in a 3MB file.
Any hints on speeding this up?
OS: Redhat Linux 5.1, Dual-PPro 266.

The table definitions are below if anyone is interested:
(Also, the cdate default value doesn't get set properly to the current date.
Any hints on that would
be appreciated as well.)
Thanks,
Rich.


Table    = owners
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| id                               | float8                           |
8 |
| name                             | varchar()                        |
0 |
| email                            | varchar()                        |
0 |
| dphone                           | varchar()                        |
0 |
| ephone                           | varchar()                        |
0 |
| zip                              | varchar()                        |
0 |
| country                          | varchar()                        |
0 |
| password                         | varchar()                        |
0 |
| isdealer                         | bool                             |
1 |
| cdate                            | date default datetime 'now'      |
4 |
+----------------------------------+----------------------------------+-----
--+

Table    = autos
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| id                               | float8                           |
8 |
| ownerid                          | float8                           |
8 |
| city                             | varchar()                        |
0 |
| region                           | varchar()                        |
0 |
| year                             | varchar()                        |
0 |
| mileage                          | int8                             |
8 |
| make                             | varchar()                        |
0 |
| model                            | varchar()                        |
0 |
| price                            | money                            |
4 |
| bo                               | bool                             |
1 |
| ecolor                           | varchar()                        |
0 |
| icolor                           | varchar()                        |
0 |
| condition                        | varchar()                        |
0 |
| trans                            | varchar()                        |
0 |
| drivetrain                       | varchar()                        |
0 |
| cylinders                        | varchar()                        |
0 |
| power_steering                   | varchar()                        |
0 |
| power_windows                    | varchar()                        |
0 |
| power_locks                      | varchar()                        |
0 |
| pwr_driver_seat                  | varchar()                        |
0 |
| abs                              | varchar()                        |
0 |
| driver_air_bag                   | varchar()                        |
0 |
| dual_air_bag                     | varchar()                        |
0 |
| leather                          | varchar()                        |
0 |
| air                              | varchar()                        |
0 |
| radio                            | varchar()                        |
0 |
| cassette                         | varchar()                        |
0 |
| cd                               | varchar()                        |
0 |
| extra_cab                        | varchar()                        |
0 |
| tow_pkg                          | varchar()                        |
0 |
| sun_roof                         | varchar()                        |
0 |
| roof_rack                        | varchar()                        |
0 |
| description                      | varchar()                        |
0 |
| cdate                            | date default datetime 'now'      |
4 |
+----------------------------------+----------------------------------+-----
--+






Tue, 23 Mar 1999 23:37:27 -0800
"Postgres mailing lists" <postgres@weblynk.com>
Really slow query on 6.4.2
"Postgres mailing lists" <postgres@weblynk.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

Wed, 24 Mar 1999 10:09:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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@weblynk.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
> 

Wed, 24 Mar 1999 11:55:21 -0800 (PST)
RHS Linux User <postgres@weblynk.com>
Re: [HACKERS] Really slow query on 6.4.2 