Hello,
Please ignore my previous post. I found the reason for the reported behavior. The 1st section of the UNION produces 37454 immediately; but one of the remaining sections of the UNION ALL is hanging. I'll have to further analyze that section alone.
Thanks.
A.A.
_____________________________________________
From: Alanoly Andrews
Sent: Wednesday, April 1, 2020 2:09 PM
To: pgsql-admin@postgresql.org
Subject: Use of "limit" to return a subset of query results
Hello,
I have a query which returns a large number of rows (presumably a total 37454 rows).
I present three cases:
- Select * from invitj_rec; -- this runs for more than 30 mns after which I terminated it.
- Select * from invitj_rec limit 37454; -- this returns in a few seconds with 37454 rows.
- Select * from invitj_rec limit 37455; -- does not return for 30 mns at least (same as case 1)
In case 2, I can use any number less than 37454 and it returns immediately with the specified number of rows. In case 3, any limit of more than 37454 hangs.
So, apparently the query produces a total of 37454 rows. But I do not know this number in advance. I need to run this query without a "limit" and produce the 37454 rows. Apparently all the rows can be produced in a few seconds (as the use of "limit" proves).
It should be noted that invitj_rec is actually a view which consists of a UNION ALL of about 8 different SQL statements on various tables in the database.
Appreciate some input on this issue.
Alanoly Andrews.