How to generate row numbers for millions of rows returned from a view?

Question

I have requirement of selecting data from view with definition of union all of multiple select statements (each table having 500K records) but want to generate row numbers for each of the records.

View definition:

Create or replace view ABC as
Select * from table_1
union all
Select * from table_2
union all
Select * from table_3
union all
Select * from table_4
union all
Select * from table_5
union all
Select * from table_6
union all
Select * from table_7
union all
.
.
.
.
Select * from table_n

Once view is created I need to extract data but generate record number of reach record returned:

Select *,
   row_number() over() as record_id
from ABC

I tried to follow approach but I am not able to get desired result, Select * on view keeps on running.

Is there better approach to implement above scenario?

Answer 1

If you actually need deterministic, gapless numbers for all rows for every next call, and rows for all underlying tables can change at any time, it's going to be painfully expensive.

But that's not what you show. For starters, row_number() over() without ORDER BY produces arbitrary numbers. Since the advent of "parallel append" in query plans with Postgres 15, the sequence of rows returned from your query can actually change for no apparent reason. See:

• Are results from UNION ALL clauses always appended in order?

And that's not what you need, most likely. A completely volatile, arbitrary row number for millions of rows has barely any utility. Define your objective precisely, and there may be a (massively) faster / better route.

Maybe incorrect assumptions about the nature of a VIEW (basically just a stored SELECT query) vs. a MATERIALIZED VIEW (actually storing data)?