You have a tremendous amount of LEFT OUTER JOINS in there, and its hard to tell but it seems like you're doing lots of "with_polymorphic" queries as well as "lazy='joined'" styles of relationships, making for a very cumbersome query. That it returns the first result quickly (that's your 47 msec) but takes a lot longer to actually return rows (thats your .15 sec) suggests it returns a lot of rows. I'd turn off all the "with_polymorphic" stuff, as well as not using any "lazy='joined'" for collections, "lazy='subquery'" is a better choice for collections but even then, usually better as a query time option rather than a fixed mapping configuration.
On Jun 26, 2011, at 10:25 AM, nospam wrote: > > I'm seeing a simple get taking a considerable amount of time. > > a = session.query(Annotation).get(annotation.id) > > This line can take anywhere between 0.15 secs to all the way up to 1.5 > secs ... The query sqlalchemy produces is below. If I execute the > query in pgadmin, it consistently runs in 47 msecs. > > Any ideas? > > It seems to run longer if I issue the 'get' right after an update and > another select query. If I wait a little bit (5 secs), then it seems > to run closer to the 0.15 secs. > > SELECT (SELECT users.username > FROM users > WHERE annotations.user_id = users.id) AS created_by, annotations.id AS > annotations_id, annotations.version AS annotations_version, > annotations.message AS annotations_message, annotations.image_filename > AS annotations_image_filename, annotations.user_id AS > annotations_user_id, annotations.camera_id AS annotations_camera_id, > annotations.game_id AS annotations_game_id, annotations.item_id AS > annotations_item_id, annotations.creation_datetime AS > annotations_creation_datetime, annotations.modified_datetime AS > annotations_modified_datetime, annotations.camera_datetime AS > annotations_persistent, anon_1.items_id AS anon_1_items_id, > anon_1.items_type AS anon_1_items_type, anon_1.items_user_id AS > anon_1_items_user_id, anon_1.items_game_id AS anon_1_items_game_id, > anon_1.people_item_id AS anon_1_people_item_id, > anon_1.vehicles_item_id AS anon_1_vehicles_item_id, > anon_1.articles_item_id AS anon_1_articles_item_id, > anon_1.relationships_item_id AS anon_1_relationships_item_id, > anon_1.relationships_lower_item_id AS > anon_1_relationships_lower_item_id, > anon_1.relationships_higher_item_id AS > anon_1_relationships_higher_item_id, anon_2.attributes_id AS > anon_2_attributes_id, anon_2.attributes_name AS > anon_2_attributes_name, anon_2.attributes_type AS > anon_2_attributes_type, anon_2.attributes_annotation_id AS > anon_2_attributes_annotation_id, anon_2.attributes_suspicion_level AS > anon_2_attributes_suspicion_level, > anon_2.vehicle_attributes_attributes_id AS > anon_2_vehicle_attributes_attributes_id, > anon_2.vehicle_attributes_vehicle_make AS > anon_2_vehicle_attributes_vehicle_model, > anon_2.relationship_attributes_attributes_id AS > anon_2_relationship_attributes_attributes_id, > anon_2.relationship_attributes_action AS > anon_2_relationship_attributes_action, > anon_2.police_report_attributes_attributes_id AS > anon_2_police_report_attributes_attributes_id, > anon_2.police_report_attributes_first_name AS > anon_2_article_attributes_attributes_id, > anon_2.article_attributes_stationary AS > anon_2.person_attributes_attributes_id AS > anon_2_person_attributes_attributes_id, > anon_2.person_attributes_eye_color AS > anon_2_person_attributes_eye_color, > anon_2.person_attributes_hair_color AS > anon_2_person_attributes_hair_color, anon_2.person_attributes_gender > AS anon_2_person_attributes_gender, anon_2.person_attributes_height AS > anon_2_person_attributes_height, anon_2.person_attributes_age AS > anon_2_person_attributes_age, anon_2.person_attributes_build AS > anon_2_person_attributes_build, anon_2.person_attributes_luggage AS > anon_2_person_attributes_luggage, anon_2.person_attributes_mobility AS > anon_2_person_attributes_mobility, > anon_2.flight_checkin_attributes_attributes_id AS > anon_2_flight_checkin_attributes_attributes_id, > anon_2.flight_checkin_attributes_airline AS > anon_2_flight_checkin_attributes_start_datetime_of_flight, > anon_2.flight_checkin_attributes_end_datetime_of_flight AS > anon_2_flight_checkin_attributes_end_datetime_of_flight, > anon_2.flight_checkin_attributes_first_name AS > anon_2_flight_checkin_attributes_first_name, > anon_2.flight_checkin_attributes_last_name AS > anon_2_lpr_attributes_attributes_id, > anon_2.lpr_attributes_start_datetime AS > anon_2_lpr_attributes_start_datetime, > anon_2.lpr_attributes_end_datetime AS > anon_2_lpr_attributes_end_datetime, > anon_2.lpr_attributes_license_plate AS > anon_2_lpr_attributes_license_plate, anon_2.lpr_attributes_action AS > anon_2_lpr_attributes_action, anon_2.lpr_attributes_station AS > anon_2_lpr_attributes_station, > anon_2.gate_boarding_attributes_attributes_id AS > anon_2_gate_boarding_attributes_attributes_id, > anon_2.gate_boarding_attributes_airline AS > anon_2_rmv_attributes_attributes_id, anon_2.rmv_attributes_first_name > AS anon_2_rmv_attributes_first_name, anon_2.rmv_attributes_last_name > AS anon_2_rmv_attributes_last_name, anon_2.rmv_attributes_address AS > anon_2_rmv_attributes_address, anon_2.rmv_attributes_min_height_feet > AS anon_2.rmv_attributes_min_age AS anon_2_rmv_attributes_min_age, > anon_2.rmv_attributes_max_age AS anon_2_rmv_attributes_max_age, > anon_2.rmv_attributes_eye_color AS anon_2_rmv_attributes_eye_color, > anon_2.rmv_attributes_gender AS anon_2_rmv_attributes_gender, > anon_2.rmv_attributes_license_plate AS > anon_2_rmv_attributes_license_plate, > anon_2.rmv_attributes_vehicle_color AS > anon_2_rmv_attributes_vehicle_color, > anon_2.rmv_attributes_vehicle_model AS > anon_2_rmv_attributes_vehicle_model, > anon_2.security_checkpoint_attributes_attributes_id AS > anon_2_security_checkpoint_attributes_attributes_id, > anon_2.security_checkpoint_attributes_end_datetime AS > anon_2_security_checkpoint_attributes_end_datetime, > anon_2.security_checkpoint_attributes_id_type AS > anon_2_security_checkpoint_attributes_id_type > FROM annotations LEFT OUTER JOIN (SELECT items.id AS items_id, > items.type AS items_type, items.user_id AS items_user_id, > items.game_id AS items_game_id, people.item_id AS people_item_id, > vehicles.item_id AS vehicles_item_id, articles.item_id AS > articles_item_id, relationships.item_id AS relationships_item_id, > relationships.lower_item_id AS relationships_lower_item_id, > relationships.higher_item_id AS relationships_higher_item_id > FROM items LEFT OUTER JOIN people ON items.id = people.item_id LEFT > OUTER JOIN vehicles ON items.id = vehicles.item_id LEFT OUTER JOIN > articles ON items.id = articles.item_id LEFT OUTER JOIN relationships > ON items.id = relationships.item_id) AS anon_1 ON anon_1.items_id = > annotations.item_id LEFT OUTER JOIN (SELECT attributes.id AS > attributes_id, attributes.name AS attributes_name, attributes.type AS > attributes_type, attributes.annotation_id AS attributes_annotation_id, > attributes.suspicion_level AS attributes_suspicion_level, > vehicle_attributes.attributes_id AS vehicle_attributes_attributes_id, > vehicle_attributes.vehicle_make AS vehicle_attributes_vehicle_make, > vehicle_attributes.vehicle_type AS vehicle_attributes_vehicle_type, > vehicle_attributes.vehicle_model AS vehicle_attributes_vehicle_model, > relationship_attributes.attributes_id AS > relationship_attributes_attributes_id, relationship_attributes.action > AS relationship_attributes_action, > police_report_attributes.attributes_id AS > police_report_attributes_attributes_id, > police_report_attributes.first_name AS > police_report_attributes_first_name, > police_report_attributes.last_name AS > police_report_attributes_last_name, > police_report_attributes.license_plate AS > police_report_attributes_license_plate, > police_report_attributes.location AS > police_report_attributes_location, police_report_attributes.summary AS > police_report_attributes_summary, > police_report_attributes.start_datetime AS > police_report_attributes_start_datetime, > police_report_attributes.end_datetime AS > police_report_attributes_end_datetime, > article_attributes.attributes_id AS article_attributes_attributes_id, > article_attributes.stationary AS article_attributes_stationary, > article_attributes.article_type AS article_attributes_article_type, > article_attributes.article_size AS article_attributes_article_size, > article_attributes.article_color AS article_attributes_article_color, > article_attributes.other AS article_attributes_other, > person_attributes.attributes_id AS person_attributes_attributes_id, > person_attributes.eye_color AS person_attributes_eye_color, > person_attributes.luggage AS person_attributes_luggage, > person_attributes.mobility AS person_attributes_mobility, > flight_checkin_attributes.attributes_id AS > flight_checkin_attributes_attributes_id, > flight_checkin_attributes.airline AS > flight_checkin_attributes_airline, > flight_checkin_attributes.start_datetime_of_checkin AS > flight_checkin_attributes.gate_number AS > flight_checkin_attributes_gate_number, > flight_checkin_attributes.destination AS > flight_checkin_attributes_destination, lpr_attributes.attributes_id AS > lpr_attributes_attributes_id, lpr_attributes.start_datetime AS > lpr_attributes_start_datetime, lpr_attributes.end_datetime AS > lpr_attributes_end_datetime, lpr_attributes.license_plate AS > lpr_attributes_license_plate, lpr_attributes.action AS > lpr_attributes_action, lpr_attributes.station AS > lpr_attributes_station, gate_boarding_attributes.attributes_id AS > gate_boarding_attributes_attributes_id, > gate_boarding_attributes.airline AS gate_boarding_attributes_airline, > gate_boarding_attributes.gate AS gate_boarding_attributes_gate, > gate_boarding_attributes.flight_number AS > gate_boarding_attributes_flight_number, > gate_boarding_attributes.first_name AS > gate_boarding_attributes_first_name, > gate_boarding_attributes.last_name AS > gate_boarding_attributes_last_name, > gate_boarding_attributes.start_datetime AS > gate_boarding_attributes_start_datetime, > gate_boarding_attributes.end_datetime AS > gate_boarding_attributes_end_datetime, rmv_attributes.attributes_id AS > rmv_attributes_attributes_id, rmv_attributes.first_name AS > rmv_attributes_first_name, rmv_attributes.last_name AS > rmv_attributes_last_name, rmv_attributes.address AS > rmv_attributes_address, rmv_attributes.min_height_feet AS > rmv_attributes_min_height_feet, rmv_attributes.min_height_inches AS > rmv_attributes_min_height_inches, rmv_attributes.max_height_feet AS > rmv_attributes_max_height_feet, rmv_attributes.max_height_inches AS > rmv_attributes_max_height_inches, rmv_attributes.min_weight AS > rmv_attributes_min_weight, rmv_attributes.max_weight AS > rmv_attributes_max_weight, rmv_attributes.min_age AS > rmv_attributes_min_age, rmv_attributes.max_age AS > rmv_attributes_max_age, rmv_attributes.eye_color AS > rmv_attributes_eye_color, rmv_attributes.gender AS > rmv_attributes_gender, rmv_attributes.license_plate AS > rmv_attributes_license_plate, rmv_attributes.vehicle_color AS > rmv_attributes_vehicle_color, rmv_attributes.vehicle_make AS > rmv_attributes_vehicle_make, rmv_attributes.vehicle_min_year AS > rmv_attributes_vehicle_min_year, rmv_attributes.vehicle_max_year AS > rmv_attributes_vehicle_max_year, rmv_attributes.vehicle_model AS > rmv_attributes_vehicle_model, > security_checkpoint_attributes.attributes_id AS > security_checkpoint_attributes_attributes_id, > security_checkpoint_attributes.checkpoint AS > security_checkpoint_attributes_id_type > FROM attributes LEFT OUTER JOIN vehicle_attributes ON attributes.id = > vehicle_attributes.attributes_id LEFT OUTER JOIN > relationship_attributes ON attributes.id = > relationship_attributes.attributes_id LEFT OUTER JOIN > police_report_attributes ON attributes.id = > police_report_attributes.attributes_id LEFT OUTER JOIN > article_attributes ON attributes.id = article_attributes.attributes_id > LEFT OUTER JOIN person_attributes ON attributes.id = > person_attributes.attributes_id LEFT OUTER JOIN > flight_checkin_attributes ON attributes.id = > flight_checkin_attributes.attributes_id LEFT OUTER JOIN lpr_attributes > ON attributes.id = lpr_attributes.attributes_id LEFT OUTER JOIN > gate_boarding_attributes ON attributes.id = > gate_boarding_attributes.attributes_id LEFT OUTER JOIN rmv_attributes > ON attributes.id = rmv_attributes.attributes_id LEFT OUTER JOIN > security_checkpoint_attributes ON attributes.id = > security_checkpoint_attributes.attributes_id) AS anon_2 ON > annotations.id = anon_2.attributes_annotation_id > WHERE annotations.id = '3c9d55a8-3eef-4ca7-a8bc-a7aae98a5b53' > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
