RE: [HACKERS] Really slow query on 6.4.2

You don't mention any indexes.  Make sure you have indexes in stalled in
autos.ownerid and owners.id.
-----Original Message-----From:    Postgres mailing lists [SMTP:postgres@weblynk.com]Sent:    Wednesday, March 24, 1999
12:37AMTo:    hackers@postgreSQL.orgSubject:    [HACKERS] Really slow query on 6.4.2
 
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 theresults 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
at0% idle CPU, with the backend taking
 
all thetime. The file ends up about 3MB. Both tables have between 1200 and
1600rows with about 25 and 7 columns respectively.A simpler query like:"select * from autos;" takes about a second at
about50% idle, and
 
producesa 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 wouldbe 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 |
+----------------------------------+----------------------------------+-------+