seandknutson@gmail.com writes:
> It seems that ordering is lost when doing a "select distinct" from a
> materialized view.
SELECT DISTINCT doesn't promise to preserve order in any context,
matview or otherwise. If you want a particular output ordering
you need to say ORDER BY explicitly in the query. Otherwise the
planner is free to do the DISTINCT via hashing, as it evidently
did here. (Actually, it's free to do it by hashing anyhow; but
with ORDER BY it'd then have to re-sort.)
> 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. Certainly, if you haven't created an index on the
matview, the planner will assume that it must either sort-and-unique
or hash in order to do the DISTINCT correctly ... and unless the table
is too large for the hashtable to fit in memory, it's likely to think
the hash approach is preferable.
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?)
regards, tom lane
