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
