Really slow query on 6.4.2 - Mailing list pgsql-hackers

From Postgres mailing lists
Subject Really slow query on 6.4.2
Date
Msg-id 001201be75c9$2c1a9b10$64ba01cc@earthlink.net
Whole thread Raw
Responses Re: [HACKERS] Really slow query on 6.4.2
List 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 |
+----------------------------------+----------------------------------+-----
--+







pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] aggregation memory leak and fix
Next
From: Tatsuo Ishii
Date:
Subject: Re: [HACKERS] "CANNOT EXTEND" -