On Mon, 2006-09-11 at 13:14, Piñeiro wrote:
> Hi,
>
> a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
> 7.4.7). To migrate the database we use a dump, using pg_dump with this
> options:
> pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>
>
> We have a search, that using woody take about 1-2 minutes, but with
> sarge it is executing about 2 hours, and at least it crashes, with a
> message about a temporal file and no more disk space ( i have more than
> a GB of free disk space).
>
> The search is very long, with a lot of joins (generated by a ERP we
> manage). We think that the problem can be at the indices, but we are not
> sure. At the original woody database we create indices, but when the
> dump is being installed at sarge, it creates an implicit index, so there
> are times that there are duplicates indices. But we try to remove the
> duplicate indices and we don't resove the problem.
That query made my head hurt. However, reading as much of it as I could
make myself, it seemed to have the common problem where it has lots of
tables in the middle of the joins, i.e.
select <select list> from
table1 join table2 on (...
join table3, table4, table5
left join table 6 on (table2.xx = table6.yy)
where table3=...
So, the theoretical way to create this is to first join table1 to
table2, then table3, table4, and table5 with NO CONSTRAINT then table6,
then separate out all the rows from that huge unconstrained join with
the where clause.
I'd suggest two things.
one: Get a better ERP... :) or at least one you can inject some
intelligence into, and two: upgrade to postgresql 8.1, or even 8.2 which
will be released moderately soon, and if you won't be going into
production directly, might be ready about the time you are.