Re: Long Query

David Wheeler <David(at)Wheeler(dot)net> writes:

> 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.

Make sure PG is logging to a file (rather than /dev/null), crank up
the log level, and make sure the queries that PG gets are what the app
is supposed to send.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.