Обсуждение: Slow cross-machine read on one table
Hello again!
I modified the application I mentioned in my last post, the one that is taking 20 minutes to solve a problem on our customer's system that is solved in under ten seconds on my machine. The application is written in C++. All data access is through a class named CCRecordset. We have derivatives of that class for every table in our database. CCRecordset uses ADO to communicate with the database, and the database connection is based on a DSN. The modification to the CCRecordset class logs the queries, the time the query was issued, the time the query returned, and the number of records returned.
The slow access seems to be happening with a single table. Here's the query:
select coil_id,step_number,order_number,status,status_date,cycle,weight,width,gauge,outside_diam,inside_diam,heat_number,base,charge,stack,
stack_position,alpha_code,account,archived,bed,bundle_flag,bundle_id,customer,department,disposition,entered_by,entered_date,
final_cooling_hotspot,final_disposition,final_heating_coldspot,final_heating_hotspot,finish_code,footage,grade,
heating_coldspot_time_reached,heating_hotspot_time_reached,hydrogen,location,manual,next_operation,product,priority,
promised_date,promised_week,promised_year,reanneal,received,redeox,required_cooling_hotspot,required_heating_coldspot,
required_heating_hotspot,sand_seal,schedule,roll_sequence,updated_by,updated_date
from inventory
where status = (select status from coil_status where free=1)
and archived=0
and (coil_id='320787' or coil_id='949806' or coil_id='320830' or coil_id='183015' or coil_id='320647' or coil_id='987767')
order by coil_rating desc
stack_position,alpha_code,account,archived,bed,bundle_flag,bundle_id,customer,department,disposition,entered_by,entered_date,
final_cooling_hotspot,final_disposition,final_heating_coldspot,final_heating_hotspot,finish_code,footage,grade,
heating_coldspot_time_reached,heating_hotspot_time_reached,hydrogen,location,manual,next_operation,product,priority,
promised_date,promised_week,promised_year,reanneal,received,redeox,required_cooling_hotspot,required_heating_coldspot,
required_heating_hotspot,sand_seal,schedule,roll_sequence,updated_by,updated_date
from inventory
where status = (select status from coil_status where free=1)
and archived=0
and (coil_id='320787' or coil_id='949806' or coil_id='320830' or coil_id='183015' or coil_id='320647' or coil_id='987767')
order by coil_rating desc
None of the six coils are free, so this query returns 0 records. There are indexes on the coil_id and coil_rating fields. There are just over 100,000 records in this table. The coil_rating field is null for all but about 1200 of them. This query took about 75 seconds.
I ran PGAdmin on the same machine that the application is running on, and executed the same query on the same database. It took about 30 milliseconds.
I could well believe that the cursor location is set incorrectly for the inventory table object, but I would expect that it would be set the same for this object as for all other CCRecordset objects, and data access would be just as slow for all of them. Instead, this table seems to be the only one affected. A query on another table with just under 1700 records that returns all records in the table took about one second.
Can anyone suggest why querying this one table is taking so much time?
Thanks again!
Rob
Rob Richardson wrote: > The slow access seems to be happening with a single table. Here's the > query: Can you post EXPLAIN ANALYZE output? > This query took about 75 seconds. > > I ran PGAdmin on the same machine that the application is running on, > and executed the same query on the same database. It took about 30 > milliseconds. In your application, are you using server-side prepared statements with query parameters for some of the values in that query? Try testing through PGAdmin / psql using PREPARE (with the same parameter placeholders as your app uses) then EXECUTE, instead of a straight SELECT. If you get much slower execution that way, it'll be because the planner doesn't know specific values for the parameter placeholders when planning the query, so it can't make use of knowledge gained from table statistics like "almost all values in the 'archived' column are zero'. -- Craig Ringer