Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E43319FB8DD for ; Tue, 23 Oct 2007 10:06:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 73902-02 for ; Tue, 23 Oct 2007 10:06:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web50908.mail.re2.yahoo.com (web50908.mail.re2.yahoo.com [206.190.38.128]) by postgresql.org (Postfix) with SMTP id 4FBEA9FB65A for ; Tue, 23 Oct 2007 10:06:41 -0300 (ADT) Received: (qmail 88790 invoked by uid 60001); 23 Oct 2007 13:06:39 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=YXecEziS0QxdQFIFsdhdwbXoZ1QUFeYhONPkiW8CJDssiVYk9mpUaj7o+mY06zMHHminpfBDSM4utWyvdJ295Ys/EpG/nQg+rbTHIDqvSUs4Oxp9xjyzJZCNhl3cd/DpXoZbnE2YaAPyvF7UlFl7qVwEZHsngINhe3XlM1VmdCo=; X-YMail-OSG: D0AGI.0VM1mVxVYxwzmeEdNflr4qPc1quogaK11qmT49ln3gCL4lbUqBLCDA0Q4FWYyDMVbQyRNiHd01D14AzBnfmIlkRabP9Fe2l9Mrc_pH._J3K1obPEBfuVTOeg-- Received: from [200.55.12.17] by web50908.mail.re2.yahoo.com via HTTP; Tue, 23 Oct 2007 06:06:39 PDT Date: Tue, 23 Oct 2007 06:06:39 -0700 (PDT) From: Adrian Demaestri Subject: Re: Seqscan To: Jeff Davis Cc: pgsql-performance@postgresql.org In-Reply-To: <1193107511.26301.59.camel@jdavis> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-673137473-1193144799=:88585" Content-Transfer-Encoding: 8bit Message-ID: <766975.88585.qm@web50908.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.028 tagged_above=0 required=5 tests=BAYES_50=0.001, FUZZY_AMBIEN=1.026, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200710/249 X-Sequence-Number: 27271 --0-673137473-1193144799=:88585 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit It is not actualy a table, sorry, it is a quite complex view that involve three large tables. When I query the view using a where clause the answer is fast because of the use of some restrictive indexes, but when there is no where clause the "limit 1" waits until the entire table is generated and all the joins are made. I can't control the sintax of the problematic query, it is generated autamatically by another layer of our application and it's syntactically and semantically ok. Here is the view structure SELECT a.field1 FROM a LEFT JOIN b ON a.f1= b.f1 AND a.f2 = b.f2 LEFT JOIN c ON a.f3 = c.f3 Each one of the tables a, b and c has about 5 million rows. The relation between the tables using that joins conditions is at most 1 to 1 thanks! Jeff Davis escribió: On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote: > Hi, > I think planner should use other plans than seqscan to solve querys > like select * from hugetable limit 1, especially when the talbe is > very large. Is it solved in newer versions or is there some open > issues about it?. > thanks > I'm working with postgres 8.0.1, For the query in question, what would be faster than a seqscan? It doesn't read the whole table, it only reads until it satisfies the limit clause. Regards, Jeff Davis --------------------------------- ¡Sé un mejor ambientalista! Encuentra consejos para cuidar el lugar donde vivimos en: http://telemundo.yahoo.com/promos/mejorambientalista.html --0-673137473-1193144799=:88585 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit It is not actualy a table, sorry, it is a quite complex view that involve three large tables. When I query the view using a where clause the answer is fast because of the use of some restrictive indexes, but when there is no where clause the "limit 1" waits until the entire table is generated and all the joins are made. I can't control the sintax of the problematic query, it is generated autamatically by another layer of our application and it's syntactically and semantically ok.

Here is the view structure

SELECT a.field1
   FROM  a
   LEFT JOIN b ON a.f1= b.f1 AND a.f2 = b.f2
   LEFT JOIN c ON a.f3 = c.f3

Each one of the tables a, b and c has about 5 million rows.
The relation between the tables using that joins conditions is at most 1 to 1
thanks!




Jeff Davis <pgsql@j-davis.com> escribió:
On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote:
> Hi,
> I think planner should use other plans than seqscan to solve querys
> like select * from hugetable limit 1, especially when the talbe is
> very large. Is it solved in newer versions or is there some open
> issues about it?.
> thanks
> I'm working with postgres 8.0.1,

For the query in question, what would be faster than a seqscan? It
doesn't read the whole table, it only reads until it satisfies the limit
clause.

Regards,
Jeff Davis



¡Sé un mejor ambientalista!
Encuentra consejos para cuidar el lugar donde vivimos en:
http://telemundo.yahoo.com/promos/mejorambientalista.html --0-673137473-1193144799=:88585--