Hey All,
I'm developing a large, PosgreSQL-based content management system. It runs
via Apache/mod_perl. I have a weird issue that has come up with a few
queries: they take forever! They're nothing complex - simpl queries that
join two tables and look up data via a primary key ID. When I paste one
of these queries into psql, they return their results immediately. When
they're executed via the application, in mod_perl, however, they can take
up to 20 seconds! During that time, one postmaster shoots to the top of
the list in the top utility, sucking up 90%+ of the CPU time.
Can anyone suggest how I might figure out why the query takes so long in
one context but not another? Since it's the postmaster process that sucks
up all the cycles, I'm assuming that Perl/DBI is just waiting on
PostgreSQL. Any pointers to help with monitoring what Postgres is doing
during all that time would be helpful.
Thanks!
David
PS: The PostgreSQL server is a pretty default installation, with no
performance tuning. Here's the EXPLAIN output of one of the queries:
Merge Join (cost=0.00..82.29 rows=1 width=240)
-> Index Scan using pk_story__id on story s (cost=0.00..8.14 rows=10 width=168)
-> Index Scan using fdx_story__story_instance on story_instance i (cost=0.00..59.00 rows=1000 width=72)
EXPLAIN
--
David Wheeler AIM: dwTheory
David@Wheeler.net ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory@jabber.org
