Really slow query on 6.4.2

From: "Postgres mailing lists" <postgres(at)weblynk(dot)com>
To: <hackers(at)postgreSQL(dot)org>
Subject: Really slow query on 6.4.2
Date: 1999-03-24 07:37:27
Message-ID: 001201be75c9$2c1a9b10$64ba01cc@earthlink.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 1999-03-24 12:21:13 Re: [HACKERS] "CANNOT EXTEND" -
Previous Message Bruce Momjian 1999-03-24 06:11:56 Re: [HACKERS] aggregation memory leak and fix