pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Use of "limit" to return a subset of query results 2+ messages / 1 participants [nested] [flat]
* Use of "limit" to return a subset of query results @ 2020-04-01 18:09 Alanoly Andrews <alanolya@invera.com> 0 siblings, 1 reply; 2+ messages in thread From: Alanoly Andrews @ 2020-04-01 18:09 UTC (permalink / raw) To: pgsql-admin Hello, I have a query which returns a large number of rows (presumably a total 37454 rows). I present three cases: 1. Select * from invitj_rec; -- this runs for more than 30 mns after which I terminated it. 2. Select * from invitj_rec limit 37454; -- this returns in a few seconds with 37454 rows. 3. 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
* FW: Use of "limit" to return a subset of query results @ 2020-04-01 19:03 Alanoly Andrews <alanolya@invera.com> parent: Alanoly Andrews <alanolya@invera.com> 0 siblings, 0 replies; 2+ messages in thread From: Alanoly Andrews @ 2020-04-01 19:03 UTC (permalink / raw) To: pgsql-admin 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: 1. Select * from invitj_rec; -- this runs for more than 30 mns after which I terminated it. 2. Select * from invitj_rec limit 37454; -- this returns in a few seconds with 37454 rows. 3. 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-04-01 19:03 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-04-01 18:09 Use of "limit" to return a subset of query results Alanoly Andrews <alanolya@invera.com> 2020-04-01 19:03 ` Alanoly Andrews <alanolya@invera.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox