I found it: disabling enable_hashagg --- On Wed, 3/2/11, Ioana Danes <ioanasoftware@xxxxxxxx> wrote: > From: Ioana Danes <ioanasoftware@xxxxxxxx> > Subject: select DISTINCT not ordering the returned rows > To: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx> > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
