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 1jJirA-0006KV-85 for pgsql-admin@arkaria.postgresql.org; Wed, 01 Apr 2020 19:16:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jJir9-0003R1-4n for pgsql-admin@arkaria.postgresql.org; Wed, 01 Apr 2020 19:16:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJhni-0003cL-0i for pgsql-admin@lists.postgresql.org; Wed, 01 Apr 2020 18:09:18 +0000 Received: from mail-eopbgr670051.outbound.protection.outlook.com ([40.107.67.51] helo=CAN01-TO1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJhnd-0001fQ-Ui for pgsql-admin@postgresql.org; Wed, 01 Apr 2020 18:09:17 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=GLAcEyRf7BPQGgm7TMftun5p6eqg+VSPe9UC0QxmvUtKD6ipkRYXtVTD/fP5Npgpm2dwaPKIf73dTbaGSXM2W6OPv7Udl6+TH28My+7kH7QBzG+iFMroPVJH+VBfspdu7dXwD2J7b4+B7Q4bdt85ULnGPr7SGOHbcXwgdRXZUcZJsNhdDfevn+moYzeH4DrFGpxnic/OIstw0fjGOIuHd+JJ9Hv0E3G7/ck9TdKdoYWSWLYC3PyRyFSfjqk84JYqD4eDi6h05UgtyzReAH13bYzkXvE7TmC5ufiPaHEeZI37ma+VGibU0NVEJi5GOUn8eT64b9qOAhpOEhpJW2v+/Q== 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=RWw6MC9vvIayPLKsh5g+d+xxuGs7ly7P6cDBNTyDuVI=; b=XOqz4asurrfW+/uy9UeQQfQvQfw0A/94wBAqmn3PFgr6FTbrV+Ha/QBGDpHK+DbzvJfGdJphe6j/rvpkaDy5HMD3cv0umv9rzTeSgBummWkAB2TkAQc70aOgBSoRZQJqrHTRGiNnpVAhTf7TokLSl7TmrltrzRjt0ZDBSC6JkjvdmoiMI3p+5kTbS9GZkfi/orTyBqhehPiiDAC5vvCv9la+rwgHsxrhNHyi+H1tebPZSubsU7HJ71PrNAgztW9qbi+4dw+BkijQqJGbtZZUpd2aMTwKZqxho24xYYIk6LjTypxoeWUHmmbwMDjIchgfoKsW0/ZBvD9PZ1GE1AIrrA== 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=RWw6MC9vvIayPLKsh5g+d+xxuGs7ly7P6cDBNTyDuVI=; b=XCkm23FlIrELlzKQOioYZ+Dw+M3Tt/7JsQ0UNNDrcGgdA0vV/DYjs9r2Pz02giP52fdFXourYQ7B+Vd08NyocXYCzjegPq8V9f//hCYWne2AXB2DGWtT29cmi9vxPS5WeaVS6t8jUWC6+X2gjo3SZTdWONqv35F5D6QDldG08vQ= Received: from QB1PR01MB2562.CANPRD01.PROD.OUTLOOK.COM (52.132.88.148) by QB1PR01MB2963.CANPRD01.PROD.OUTLOOK.COM (52.132.85.209) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2856.20; Wed, 1 Apr 2020 18:09:10 +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 18:09:10 +0000 From: Alanoly Andrews To: "pgsql-admin@postgresql.org" Subject: 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/I7Yv3ZMH2Rleg0c7otOlLxA== Disposition-Notification-To: Alanoly Andrews Date: Wed, 1 Apr 2020 18:09:10 +0000 Message-ID: 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: 2cef8eef-66fa-452e-a5b8-08d7d667c6d2 x-ms-traffictypediagnostic: QB1PR01MB2963: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:9508; 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)(366004)(396003)(346002)(39850400004)(376002)(136003)(33656002)(966005)(478600001)(316002)(52536014)(6506007)(186003)(86362001)(30864003)(55016002)(9686003)(71200400001)(7696005)(66446008)(81156014)(5660300002)(76116006)(66476007)(8936002)(2906002)(64756008)(26005)(6916009)(81166006)(66556008)(8676002)(66946007);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: owZR9otJG0qxnE2xZQtrZtkfOKuj4qxFldPRaPwznbJhIXaNXeMJKB8WZ4MUnfOtvupHoiOvO9U4dKiBjwgPXAVPISm1wSqsNJcPbdVAO6qWBHky0drsXCV3HtlPLrffxOODJnyblb8m5zOzslyIn8+o1fCzlfe06c8pu6GpN+PkuDZIWep+FpPkxX7A+hsbxzW1Jf4sXgu/94S6tkmsbo1bOY46vBtteZcWLDi8c8+0dyL94dRc2RzYUNIfbqGXXgX8DHs+FD+m+PFQ2zphfnBhDFawMN4FH7G9OIwc7nsrpxXdfC4iam/5UoDjub9TrC6Q2yrz1kjWsnrTqucKLPa2mjOMdvfULdhbgfQkPFX6f/rkrhc9CIu2k+GtAGPzUst+1PbnKoxOycSKpXkIZYWnHqjprF2oaltLs180N/yERZjMmoklELhtgW/aH6DC x-ms-exchange-antispam-messagedata: oiHNdwFgsp94HDLL/BzNx3+AEqDaWAfpDUnuqGDTEM4QTZSa77pwY3XYBzC696B5DAZPCbC+RYZ5bVewPT3Tc70+z7jAxO5H+M8f+dyGYJhKWeQUSQsASzjze92VP0Y1wbfUuQDHm0UE0NZjhGpYoQ== x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_QB1PR01MB2562EC1B1EFE606F00F2187AABC90QB1PR01MB2562CANP_" MIME-Version: 1.0 X-OriginatorOrg: invera.com X-MS-Exchange-CrossTenant-Network-Message-Id: 2cef8eef-66fa-452e-a5b8-08d7d667c6d2 X-MS-Exchange-CrossTenant-originalarrivaltime: 01 Apr 2020 18:09:10.0714 (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: DmwPs9U5S1ez6RYTlfxZwmV2+sBRUI/gZGlMAlHDlsjLyfJajmb0v6YlEPMS2XbNi8dgk3Q6Dlm3GebAOZzCSA== X-MS-Exchange-Transport-CrossTenantHeadersStamped: QB1PR01MB2963 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_QB1PR01MB2562EC1B1EFE606F00F2187AABC90QB1PR01MB2562CANP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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_QB1PR01MB2562EC1B1EFE606F00F2187AABC90QB1PR01MB2562CANP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hello,
 
I have a query which returns a large number of rows (presumably a tota= l 37454 rows).
I present three cases:
&nbs= p;
  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)
 
In case 2, I can use any number less than 37454 and it returns immedia= tely with the specified number of rows. In case 3, any limit of more than 3= 7454 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).
&nbs= p;
It should be noted that invitj_rec is actually a view which consists o= f a UNION ALL of about 8 different SQL statements on various tables in the = database.
&nbs= p;
Appreciate some input on this issue.
 
Alanoly Andrews.
&nbs= p;
 --_000_QB1PR01MB2562EC1B1EFE606F00F2187AABC90QB1PR01MB2562CANP_--