I found it: disabling enable_hashagg
--- On Wed, 3/2/11, Ioana Danes <ioanasoftware@yahoo.ca> wrote:
> From: Ioana Danes <ioanasoftware@yahoo.ca>
> Subject: [GENERAL] select DISTINCT not ordering the returned rows
> To: "PostgreSQL General" <pgsql-general@postgresql.org>
> Received: Wednesday, March 2, 2011, 3:35 PM
> Hi Everyone,
>
> I would like to ask for your help finding a temporary
> solution for my problem.
> I upgraded postgres from 8.3 to 9.0.3 and I have an issue
> with the order of the returned rows.
>
> The following script is a simplification of my real case:
>
> create table tmp_1 (field1 integer, field2 integer);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 3);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 4);
> insert into tmp_1 values (1, 1029);
> insert into tmp_1 values (1, 1101);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 3);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 4);
> insert into tmp_1 values (13, 1029);
> insert into tmp_1 values (13, 1101);
> analyze tmp_1;
> SELECT distinct field2 FROM tmp_1 WHERE field1 = 13;
>
> The result in postgres 8.3 is as follows:
> 3
> 4
> 1029
> 1101
> And it stays the same no matter what the physical order of
> the records is in the table. I can do random update and I
> get the same results. It looks like the result is ordered by
> the distinct fields...
>
> The result in postgres 9.0 is as follows:
> 3
> 4
> 1101
> 1029
> not ordered by the distinct fields nor physical order...
>
> I am wondering if there is a temporary solution (updates,
> indexes, ...) to order the result by field1 without changing
> the statement...
>
> Thank you in advance,
> Ioana Danes
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
