Thread: slow query

Hi,

Just switched an application from Oracle to PostgreSQL
recently.  I am experiencing a strange problem:
whenever I execute a relatively heavy query
in PostgreSQL, it just hangs.  To isolate the problem,
I've tried executing the query directly from psql
instead of using the application itself and
have gotten the same results.  I've even tried
vacuuming all the tables.

Anyway, viewing the process list reveals a postmaster
process that takes up 99.9% of the CPU time and just
executes forever.  Each time I execute a relatively
heavy query, a new postmaster process get stuck.  The
queries aren't really complex, i.e. a sum on a 3 table
join (about 200,000 rows  for the biggest table) [The
tables are indexed].  Like I said, Oracle takes at
most, 40 seconds to process the same query with the
same data on the same machine (dual  PIII with 1GB
RAM).  Something must be wrong...

OS:      RH 6.2
DB:      PG 7.2
APP:     CGI (using pqlib)
KERNEL:  2.4.18 (just in case it makes a diff)



  The query seemed to worked faster when one of the
index is removed. Is there a side effect when a table
has more 2 indices?


thanks,
ludwig.



__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/

Ludwig Lim <lud_nowhere_man@yahoo.com> writes:
> Just switched an application from Oracle to PostgreSQL
> recently.  I am experiencing a strange problem:
> whenever I execute a relatively heavy query
> in PostgreSQL, it just hangs.

You are going to get no useful answers since you have provided no useful
details.

Useful details would include: the exact query, the schemas of the tables
it uses, and EXPLAIN (or better, in 7.2, EXPLAIN ANALYZE) output for it.

BTW, an easy way to get the schema info is "pg_dump -s" and then trim
the script to just the relevant tables and their indexes.  This is
better than \d output since anyone who's interested in trying to
reproduce the problem can just cut-and-paste the commands into psql...

            regards, tom lane