Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A45D21336B42 for ; Thu, 16 Dec 2010 15:46:41 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 85252-09 for ; Thu, 16 Dec 2010 19:46:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from na3sys009aog111.obsmtp.com (na3sys009aog111.obsmtp.com [74.125.149.205]) by mail.postgresql.org (Postfix) with SMTP id 658A5133655C for ; Thu, 16 Dec 2010 15:46:32 -0400 (AST) Received: from source ([209.85.210.179]) by na3sys009aob111.postini.com ([74.125.148.12]) with SMTP ID DSNKTQpskpv2pKh5no0Uf1MrK9u5zh+DLadc@postini.com; Thu, 16 Dec 2010 11:46:33 PST Received: by mail-iy0-f179.google.com with SMTP id 19so1771822iye.10 for ; Thu, 16 Dec 2010 11:46:26 -0800 (PST) Received: by 10.231.35.201 with SMTP id q9mr6632881ibd.185.1292528786133; Thu, 16 Dec 2010 11:46:26 -0800 (PST) From: Michael Holt References: <001f01cb9d56$13d69790$3b83c6b0$@devshock.com> <002d01cb9d58$27fdabd0$77f90370$@devshock.com> In-Reply-To: <002d01cb9d58$27fdabd0$77f90370$@devshock.com> MIME-Version: 1.0 X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AQLyZTQQhLHmIwgi3D6D5gDDSF90zgFAmnnxkUvferCAAAUMUA== Date: Thu, 16 Dec 2010 11:46:25 -0800 Message-ID: <4fb832d4d430b6edb5965e662c75334a@mail.gmail.com> Subject: Re: using limit To: Anibal David Acosta , pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=0022152d610928d2be04978c4fd9 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.199 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3 X-Spam-Level: X-Archive-Number: 201012/113 X-Sequence-Number: 36342 --0022152d610928d2be04978c4fd9 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Yes, a limit should speed up your query from the sounds of things. *From:* Anibal David Acosta [mailto:aa@devshock.com] *Sent:* Thursday, December 16, 2010 11:34 AM *To:* 'Michael Holt'; pgsql-admin@postgresql.org *Subject:* RE: [ADMIN] using limit Is a table with 10 million of rows with a primary key but conditions used i= n select are not part of PK. My table is like this: Column 1 =96 PK Column 2 =96 Indexed Column 3 =96 Indexed Column 2 and Column 3 are Indexed in the same index but they are not mark a= s 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=92re talking about. If there= =92s only one row in the table you=92re querying then no, I don=92t think it=92l= l change anything. If you=92re querying a single row using a primary key it shouldn=92t change anything. If you=92re doing an aggregate query, say a su= m of a bunch of rows, it also won=92t improve performance. If you=92re doing a query on a table with multiple rows and not filtering b= y 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 --0022152d610928d2be04978c4fd9 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable

Yes, a limit should speed up your query from the sounds of things.<= /p>

=A0

From: Anibal David Acosta [mail= to:aa@devshock.com]
Sent: Thursday, December 16, 2010 11:34 AM
To: 'Michae= l Holt'; pgsql-admin@post= gresql.org
Subject: RE: [ADMIN] using limit

=A0

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

=A0

My table is like this:=

Column 1 = =96 PK

Colum= n 2 =96 Indexed

Column 3 =96 Indexed

=A0

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

=A0

The Query is like this:=

Select column1 fro= m myTable where Column2 between X and Y

=A0

I am expecting just one record=

=A0<= /p>

=A0

=A0

De: Michael Hol= t [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<= /p>

=A0

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

=A0

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

=A0

From: pgsql-admin-owner@postgre= sql.org [mailto:pgs= ql-admin-owner@postgresql.org] On Behalf Of Anibal David Acosta<= br> Sent: Thursday, December 16, 2010 11:19 AM
To: pgsql-admin@postgresql.org
Sub= ject: [ADMIN] using limit

=A0

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=A0 the LIMIT 1 could improve the performance?=

=A0

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

=A0

=A0

Thanks

=A0

Anibal

--0022152d610928d2be04978c4fd9--