Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJiu8-0006cx-FN for pgsql-admin@arkaria.postgresql.org; Wed, 01 Apr 2020 19:20:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jJiu7-0001KK-Dr for pgsql-admin@arkaria.postgresql.org; Wed, 01 Apr 2020 19:19:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJieK-0004KU-EA for pgsql-admin@lists.postgresql.org; Wed, 01 Apr 2020 19:03:40 +0000 Received: from mail-eopbgr660082.outbound.protection.outlook.com ([40.107.66.82] helo=CAN01-QB1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJieH-0007hA-Cj for pgsql-admin@postgresql.org; Wed, 01 Apr 2020 19:03:39 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=KcmyFwQjrukbA1JKOlkfKWG1u72sM3rEuKuK7Zt4E/4tZY8gCRRjlTxn6d82x6zThb3loNdVWqHdOCCnBW7IFytgop1/W6oHDjUCrqiYdh5RP8zSu8HgfXEYoK8u449Eb5IDeasKIYPLOzOrJtxOAYgThQ1FyF0uYdr9ERmTPCTM1CsBPnWt9OEdMsERKG8yEiPR20Sm6tidcRnC4mJ2TNEfEmvD5zGCI5WQcqYJtQgrbtc6J5+7tGybtIF1aC46XdW2BB1Fx3p9KpbgeDPpk0I4uhXb5Og8i5hLVSFuaPu3tQ6rI9YRp03odSHO0VCKk8KanCZkegSlNJq+1kAukA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=TMZgAl27XgTKRBCyEh7KxUeRTYoPIOV6AF9RgwlAwRA=; b=Le24PI+V6p6Y1Uf+FdQzmbZnToIt69d2TUG0yCkEa1/Ff1GV+8xQ5wBRrfTz/WAbNu2uLcxJNmRhx2h93qntged2r9rS6IBGfFGg/8GezmztyVwMYxMWW89AdQb5/O3IG/FI2/J/9B/PtbDD4R0BPW8PHPCb19Od6mnYoFtt6ubilNjWfu3mOeRNrxWg9hQBbhvloOyXkA6fW3lIKkFt0x02aOYpF8zoW1WHr7olZmbMH8mkZzb6rHfWIY75eX7Q6DLBVeR7bcB6nGT3NLRJgWWwkyOPJ/9NnVATjkqQ0xSlXvrChmXrjRUFn2SmRHHtpe3IAjFIcD7ZcvhibJzD1A== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=invera.com; dmarc=pass action=none header.from=invera.com; dkim=pass header.d=invera.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=invera365.onmicrosoft.com; s=selector2-invera365-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=TMZgAl27XgTKRBCyEh7KxUeRTYoPIOV6AF9RgwlAwRA=; b=6Yi8zh4uP1jFTsOtDLLqxkApE0eEzusINx+7Zg899DtJnM2apF1FnUCSA8nCzm2p4mb/j2oX6RNp4NVmKa5j0vVcHlASKDtXy0B/kr4GmaKXus18tsQJc+BO81JULV4dnOEG6AT+czP3Sp9Jg1X6AT7UrOy3LswNRf11BnjuxKY= Received: from QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM (52.132.88.148) by QB1PR01MB2467.CANPRD01.PROD.OUTLOOK.COM (52.132.88.156) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2878.15; Wed, 1 Apr 2020 19:03:34 +0000 Received: from QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM ([fe80::155f:185e:b716:df24]) by QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM ([fe80::155f:185e:b716:df24%7]) with mapi id 15.20.2856.019; Wed, 1 Apr 2020 19:03:34 +0000 From: Alanoly Andrews To: "pgsql-admin@postgresql.org" Subject: FW: Use of "limit" to return a subset of query results Thread-Topic: Use of "limit" to return a subset of query results Thread-Index: AdYIT0/I7Yv3ZMH2Rleg0c7otOlLxAACJxFA Disposition-Notification-To: Alanoly Andrews Date: Wed, 1 Apr 2020 19:03:34 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=alanolya@invera.com; x-originating-ip: [158.106.121.68] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: adcb9ee5-416e-4e42-0b22-08d7d66f60c3 x-ms-traffictypediagnostic: QB1PR01MB2467: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:10000; x-forefront-prvs: 03607C04F0 x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFTY:;SFS:(10009020)(376002)(136003)(39850400004)(396003)(346002)(366004)(81166006)(8936002)(966005)(316002)(52536014)(26005)(2906002)(7696005)(30864003)(6916009)(33656002)(6506007)(76116006)(66946007)(66446008)(64756008)(66556008)(66476007)(186003)(81156014)(8676002)(86362001)(55016002)(5660300002)(2940100002)(9686003)(71200400001)(478600001);DIR:OUT;SFP:1101; received-spf: None (protection.outlook.com: invera.com does not designate permitted sender hosts) x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: 2jxgKfO8gPL5Br9wr6I5KUsA9mJeAn6jabgcgm1hZCBUlLgYGcD7MHFSOHhxrEyd/m7QTvyP4rFDW9GFVzWPtZbCwXsRLMili1H3r8WbY9Td9bLQseLqlw/F0IzSxWBROyGDD5CvUBBABXkaTiVKgvCtxWt83gVbt7FL5gf6yw4ZjgIdEKiKbGw8S+k+Wh+PzppT21EcvZLf0OYaFdJw/0BClvrgyD19lLCNLTNpD9E0J2NrwYG4nECzztJqYvoPK0qSSa184KotM+0dHvGC4W2GEY7E/9g9P2TBnWpqdryz0yvd5gTIlALW+X3QROr33u6UV52pSoH98MuiIJxewjG+skmSqZj7+23+f0xunsn/V77wUeiGbZ2i+2f4CCiXDIGzxOZqzsobeQDCVtwy1F+4bcAPETFa0yjOJNzcsgaCV/TWs959uJgSfQuX5qMK x-ms-exchange-antispam-messagedata: GOTTG8FmAKabtSCo5wkqKBLnqg1ON8tfu0GBFDPSBmt3+9SI1aJmVaDGYxsMc0k1FS8uXZu6ZqLBUZT+iR+DaZErzp0zjthaS/iAPSz2U8/prgzYuDpTrKpgf1/0PPILd9+gBX3iwJrAo7cs79Gppw== x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_QB1PR01MB2562DFBA2C507F41626C2D19ABC90QB1PR01MB2562CANP_" MIME-Version: 1.0 X-OriginatorOrg: invera.com X-MS-Exchange-CrossTenant-Network-Message-Id: adcb9ee5-416e-4e42-0b22-08d7d66f60c3 X-MS-Exchange-CrossTenant-originalarrivaltime: 01 Apr 2020 19:03:34.8913 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 63c603f5-415e-4e47-92ce-37fe9a1a475d X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: rCEiJrSwvj2nkPrKU+zBj/VJ2+YjFAv8XJktmQcMosuF0sCju6TwzYPUTDWkGqGwl2KpTQ/zuThUWhfai+Mulw== X-MS-Exchange-Transport-CrossTenantHeadersStamped: QB1PR01MB2467 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_QB1PR01MB2562DFBA2C507F41626C2D19ABC90QB1PR01MB2562CANP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, Please ignore my previous post. I found the reason for the reported behavio= r. 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 analyz= e 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 374= 54 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 sec= onds 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 prod= uce the 37454 rows. Apparently all the rows can be produced in a few second= s (as the use of "limit" proves). It should be noted that invitj_rec is actually a view which consists of a U= NION ALL of about 8 different SQL statements on various tables in the datab= ase. Appreciate some input on this issue. Alanoly Andrews. --_000_QB1PR01MB2562DFBA2C507F41626C2D19ABC90QB1PR01MB2562CANP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hello,
 
Please ignore my previous post. I found the re= ason 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 sec= tion 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 result= s
 
 
Hello,<= /div>
 <= /div>
I have a query which = returns a large number of rows (presumably a total 37454 rows).
I present three cases= :
 <= /div>
  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 in= vitj_rec limit 37455; -- does not return for 30 mns at least (same as case = 1)
    4.
 
In case 2, I can use = any number less than 37454 and it returns immediately with the specified nu= mber of rows. In case 3, any limit of more than 37454 hangs.<= /div>
So, apparently the qu= ery produces a total of 37454 rows. But I do not know this number in advanc= e. I need to run this query without a "limit" and produce the 374= 54 rows. Apparently all the rows can be produced in a few seconds (as the use of "limit" proves).
 <= /div>
It should be noted th= at invitj_rec is actually a view which consists of a UNION ALL of about 8 d= ifferent SQL statements on various tables in the database.
 <= /div>
Appreciate some input= on this issue.
 <= /div>
Alanoly Andrews.
 
--_000_QB1PR01MB2562DFBA2C507F41626C2D19ABC90QB1PR01MB2562CANP_--