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(at)weblynk(dot)com]
Sent: Wednesday, March 24, 1999 12:37 AM
To: hackers(at)postgreSQL(dot)org
Subject: [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 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 |

+----------------------------------+----------------------------------+-----
--+