Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 8AC431336971 for ; Thu, 16 Dec 2010 15:41:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 28937-04 for ; Thu, 16 Dec 2010 19:40:58 +0000 (UTC) X-Greylist: delayed 00:07:15.120336 by SQLgrey-1.7.6 Received: from mail-gw0-f49.google.com (mail-gw0-f49.google.com [74.125.83.49]) by mail.postgresql.org (Postfix) with ESMTP id 8D5C5133655C for ; Thu, 16 Dec 2010 15:40:57 -0400 (AST) Received: by gwj20 with SMTP id 20so2359764gwj.22 for ; Thu, 16 Dec 2010 11:40:57 -0800 (PST) Received: by 10.147.125.7 with SMTP id c7mr257552yan.30.1292528022166; Thu, 16 Dec 2010 11:33:42 -0800 (PST) Received: from ANIBALVAIO ([190.128.222.90]) by mx.google.com with ESMTPS id g78sm185996yhd.22.2010.12.16.11.33.39 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 16 Dec 2010 11:33:41 -0800 (PST) From: "Anibal David Acosta" To: "'Michael Holt'" , References: <001f01cb9d56$13d69790$3b83c6b0$@devshock.com> In-Reply-To: Subject: Re: using limit Date: Thu, 16 Dec 2010 16:33:42 -0300 Message-ID: <002d01cb9d58$27fdabd0$77f90370$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01CB9D3F.02B38110" X-Mailer: Microsoft Outlook 14.0 Content-Language: es-py Thread-Index: AQLyZTQQhLHmIwgi3D6D5gDDSF90zgFAmnnxkUvferA= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201012/112 X-Sequence-Number: 36341 This is a multipart message in MIME format. ------=_NextPart_000_002E_01CB9D3F.02B38110 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Is a table with 10 million of rows with a primary key but conditions used in select are not part of PK. My table is like this: Column 1 - PK Column 2 - Indexed Column 3 - Indexed Column 2 and Column 3 are Indexed in the same index but they are not mark as unique or PK The Query is like this: Select column1 from myTable where Column2 between X and Y I am expecting just one record De: Michael Holt [mailto:michael@aers.ca] Enviado el: jueves, 16 de diciembre de 2010 04:23 p.m. Para: Anibal David Acosta; pgsql-admin@postgresql.org Asunto: RE: [ADMIN] using limit This really depends on the type of query you're talking about. If there's only one row in the table you're querying then no, I don't think it'll change anything. If you're querying a single row using a primary key it shouldn't change anything. If you're doing an aggregate query, say a sum of a bunch of rows, it also won't improve performance. If you're doing a query on a table with multiple rows and not filtering by a primary key or other unique index then yes, it will improve the query. From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Anibal David Acosta Sent: Thursday, December 16, 2010 11:19 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] using limit Hi I really like to do efficient SQL queries so, my question is if I am expecting no more than one row from a select, using the LIMIT 1 could improve the performance? If I use my logic, the LIMIT 1 instruction tell to postgres that stop searching when found 1 record, but maybe it is unnecessary Thanks Anibal ------=_NextPart_000_002E_01CB9D3F.02B38110 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Is a table with 10 million of rows = with a primary key but conditions used in select are not part of = PK.

My table is = like this:

Column 1 – PK

Column 2 = – Indexed

Column 3 – = Indexed

Column 2 = and Column 3 are Indexed in the same index but they are not mark as = unique or PK

The Query = is like this:

Select column1 from myTable where = Column2 between X and Y

I am = expecting just one record

De: Michael = Holt [mailto:michael@aers.ca]
Enviado el: jueves, 16 de = diciembre de 2010 04:23 p.m.
Para: Anibal David Acosta; = pgsql-admin@postgresql.org
Asunto: RE: [ADMIN] using = limit

This really depends on the type of = query you’re talking about. If there’s only one row in the = table you’re querying then no, I don’t think it’ll = change anything. If you’re querying a single row using a primary = key it shouldn’t change anything. If you’re doing an = aggregate query, say a sum of a bunch of rows, it also won’t = improve performance.

If you’re doing a query on a = table with multiple rows and not filtering by a primary key or other = unique index then yes, it will improve the query.

From:= pgsql-admin-owner@postgr= esql.org [mailto:pgsql-admin-owner@postgr= esql.org] On Behalf Of Anibal David Acosta
Sent: = Thursday, December 16, 2010 11:19 AM
To: pgsql-admin@postgresql.org=
Subject: [ADMIN] using limit

Hi

I really like to do efficient SQL = queries so, my question is if I am expecting no more than one row from a = select, using the LIMIT 1 could improve the = performance?

If I use my logic, the LIMIT 1 instruction tell to postgres = that stop searching when found 1 record, but maybe it is = unnecessary

Thanks

Anibal

------=_NextPart_000_002E_01CB9D3F.02B38110--