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 |
+----------------------------------+----------------------------------+-------+