Hi,
we are using PowerBuilder for development and I experimented a little
with PostgreSQL-8.1 through ODBC and I experienced the following
on psqlodbc-8.01.0106.
We have some large views, consisting of 6+ UNION members.
Recently, I rewrote them to use UNION ALL, since the members
give distinct rows across the whole view.
The performance difference between the two is enormous, e.g. using UNION,
the rows starts flowing only after 30-32 seconds but when using UNION ALL,
it starts instantly. Doing the same in psql like below runs under 4 seconds
using UNION and under about 12 seconds using UNION ALL, and this
includes getting all the rows from the server.
time echo "select * from view_x" | psql db >result.txt
What may cause such a large difference?