On Sat, May 18, 2013 at 2:52 AM, haris_p_sw <haris_p_sw@...> wrote:

>
> >
> >
> > There are blobs in any of the tables ? Since you are using select *, any
> > blobs will be returned. IIRC there is a memory consumition above the
> > expected when blobs are used in sorting (group by, distinct, etc.) I
> > can't recall the exact details and in wich FB version are affected.
> > Perhaps some one could put more information about it. Something
> > regarding the memory be freed just when the transaction is commited or
> > something like that.
>

Unless things have changed, blobs in a dataset are returned as eight byte
blob ids, then fetched on reference, so the contents of blobs should not be
in the
sort intermediates files.

> >
> > But, could you try to remove * and put the just the necessary fields ?
> >
> > If not, could you put the table strucure of the involved tables ?
> > Perhaps you have large varchar fields that leads to a huge memory usage.
> >
> > see you !
> >
>
> Yes, there are blobs and quite large varchar fields(160). I have already
> tested that if I select only some columns then the query execution time is
> dramatically reduced. My opinion is that it doesn't matter which columns
> you select, what matters is total size of the returned dataset. Actually,
> that's why my solution with the subquery works fast. Because it does the
> sorting on a dataset with very few char and integer columns. Maybe the
> optimizer could follow a similar plan.

Ah, OK. PostgreSQL has a phenomenal optimizer - the result of many
graduate theses. I wouldn't be terribly surprised if it could
figure out that it should execute part of the query, sort that result, then
use it as the inner loop of a set of nested loop joins. At least
that's one possibility.

Good luck,

Ann

>
>

[Non-text portions of this message have been removed]