Confusing performance of specific query

From: Adam Endicott <leftwing17(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Confusing performance of specific query
Date: 2007-08-09 17:08:02
Message-ID: 1186679282.329920.170270@e9g2000prf.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm having an issue with a specific query, and I don't really know
where to start figuring out what's going on. I'm pretty new to
PostgreSQL in specific, and I'm not much of a database/SQL guru in
general. I've got one query that is consistently taking 10X longer to
run on a production machine than on my desktop. I haven't noticed
other queries suffering similar performance issues on the production
machine. Here's the query in question:

----------
SELECT DISTINCT
"movies_movie"."id","movies_movie"."title","movies_movie"."cinemasource_id","movies_movie"."mpaa_rating_id","movies_movie"."advisory","movies_movie"."teaser","movies_movie"."review_id","movies_movie"."runtime","movies_movie"."studio_url","movies_movie"."distributor_id","movies_movie"."synopsis","movies_movie"."stars","movies_movie"."main_image_id","movies_movie"."trailer","movies_movie"."editors_pick"
FROM "movies_movie" INNER JOIN "movies_moviescreening" AS
"movies_movie__moviescreening" ON "movies_movie"."id" =
"movies_movie__moviescreening"."movie_id" WHERE
("movies_movie__moviescreening"."id" IS NOT NULL) ORDER BY
"movies_movie"."title" ASC;
----------

I'm using a web frame work with an ORM (Django), so that's where this
query originates from - so it might not be the best way to do what I'm
trying to accomplish, but right now I'm more interested in the
performance difference between the two machines than I am in changing
this query to something better.

When I run EXPLAIN ANALYZE on this query, it takes something like
1200ms on my desktop (Dual 2GHz G5 Mac - 1.5 GB RAM for reference) and
about 14000ms on the production server (quad processor, 8 GB RAM,
running Ubuntu). There are about 500 rows in the movies_movie table,
and about 16k rows in the movies_moviescreening table. The data is the
same on both machines. My desktop is running PostgreSQL 8.2.3, and the
production server is running 8.1.9.

Since I don't know much about how to diagnose this, I don't know what
other information to give, so let me know if I've left out something
crucial.

Any help would be greatly appreciated.

Thanks,
Adam

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-08-09 17:20:09 Re: failed to unlink, Permission denied
Previous Message Steve Crawford 2007-08-09 17:06:19 Re: Allowing LAN connections