From: Alanoly Andrews <alanolya@invera.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: FW: Use of "limit" to return a subset of query results
Date: Wed, 1 Apr 2020 19:03:34 +0000
Message-ID: <QB1PR01MB2562DFBA2C507F41626C2D19ABC90@QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <QB1PR01MB2562EC1B1EFE606F00F2187AABC90@QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM>
References: <QB1PR01MB2562EC1B1EFE606F00F2187AABC90@QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alanolya@invera.com
Subject: Re: FW: Use of "limit" to return a subset of query results
In-Reply-To: <QB1PR01MB2562DFBA2C507F41626C2D19ABC90@QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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