Re: Postgresq 8,1 hangs when running function

On 8/21/06 8:27 AM, "ben sewell" <mosherben(at)gmail(dot)com> wrote:

> Hi Sean,
> I've just tried doing an explain analyze on a select query without any joins
> and CPU Usage is going between 50% and 59%. The wierd thing is that there
> select statement is only taking data from 4 tables so i would have imagined
> it would have been ok. Never the less, it's still hanging even on doing
> explain analyze.

Ben,

When you do a select on four tables without any joins, postgres is going to
do a FULL CROSS JOIN on ALL the tables! If you use 4 tables with 100 rows
each, there will be 100^4 rows (that is 10^8 rows!)!!! That is what I think
is happening with the SQL from your function. Doing this will take a huge
amount of memory and computer resources.

If your SQL from your function has the same behavior when you execute it, it
is likely one of two problems:

1) You are doing an unconstrained CROSS JOIN and getting a large result set

Or

2) You do not have some critical indices on the tables.

It sounds like you might need to go back to basics and build your queries
carefully, placing indices as you go along and doing explain analyze as you
add tables to the results. When you add a new table to the query and the
time increases drastically, look for indices that you can add and
triple-check that you constrained the join in the way you think you should.

Finally, if you have someone nearby that is experienced with SQL databases
and knows your database, it might be worthwhile to try to get him/her to sit
down with you for an hour or so to help out with your rather extensive SQL
statements.

Sean