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