Обсуждение: 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 | +----------------------------------+----------------------------------+----- --+
"Postgres mailing lists" <postgres@weblynk.com> writes:
> 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.
Have you done a "vacuum analyze" lately? Sounds like the thing is using
a nested loop query plan, which is appropriate for tiny tables but not
for large ones. You could check this by seeing what EXPLAIN says.
Unfortunately, if you haven't done a vacuum, the system effectively
assumes that all your tables are tiny. I think this is a brain-dead
default, but haven't had much luck convincing anyone else that the
default should be changed.
regards, tom lane
The vacuum analyze did it. It's fast now. Thanks a bunch. rich. On Wed, 24 Mar 1999, Tom Lane wrote: > "Postgres mailing lists" <postgres@weblynk.com> writes: > > 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. > > Have you done a "vacuum analyze" lately? Sounds like the thing is using > a nested loop query plan, which is appropriate for tiny tables but not > for large ones. You could check this by seeing what EXPLAIN says. > > Unfortunately, if you haven't done a vacuum, the system effectively > assumes that all your tables are tiny. I think this is a brain-dead > default, but haven't had much luck convincing anyone else that the > default should be changed. > > regards, tom lane >