Re: BUG #14136: select distinct from a materialized view does not preserve result order

On Fri, May 13, 2016 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> seandknutson@gmail.com writes:

>> and a materialized view defined as
>> create materialized view view_test as (select * from test order by name);
>
> AFAIK, a matview is just a table, and even if you define it like that
> there's no guarantee that the rows in the matview will be physically
> ordered by name.

> This seems like a documentation shortcoming, though, as the documentation
> doesn't really say that a matview won't preserve row order (or at least
> I see nothing about it in the CREATE MATERIALIZED VIEW ref page --- maybe
> it's mentioned somewhere else?)

I agree that it should be documented.  I'll do something about that.

Note that even if the table is initially created in order, there is
no guarantee that REFRESH CONCURRENTLY will leave it in order.  (In
that regard it's more-or-less like CLUSTER.)  And even if you query
it while it is in order, there is no guarantee that this is the
order in which the rows would be returned, as there could be a
synchronous scan or an index-only scan on an index in some other
order.

>> If I have a non-materialized view with the same definition,
>> the query returns
>>
>>  name
>> -------
>>  adam
>>  jim
>>  sean
>>  steve
>>
>> as expected.

It is bad technique to assume an order from a query that is not
explicitly specified in that query, regardless of whether the data
is being drawn from a table, view, or materialized view.  A
DISTINCT or GROUP BY clause should not be assumed to order the
output -- it might sometimes do so, if that is the fastest way to
group things; but there is no requirement that it do so.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company