>
> I don't know myself, perhaps postgres applies a simple compression to the intermediate sort files to minimize disk space usage.
>
> The fact that your query create a fully exploded result set of all columns in all of the query tables doesn't help -- it seems very unnecessary -- it only increases the size of the temp sort file.
>
> One possible performance optimization (depending on the selectivity of the prdInCatTab.PINCPrimary column) would be to add an index on this column. This will not reduce the size of the temp sort file, but could improve overall query performance.
>
>
> Sean
>

Hi Sean,
Thanks for your reply.

Yes. I know that select * doesn't help and I agree with you that the key here is the total size of the dataset to be sorted.

I created the index you 've suggested but the optimizer doesn't use it.

From these discussions (correct me if I'm wrong) I understand that there is a design issue. I can accept it and try to avoid hitting on the wall from now on by writing queries that sort small sized datasets if NATURAL PLAN is unavoidable. But I also hope that these discussions will guide Firebird developers to have a look on such issues.

Regards,
Haris