From: Adrian Demaestri <ademaestri@yahoo.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Seqscan
Date: Tue, 23 Oct 2007 06:06:39 -0700 (PDT)
Message-ID: <766975.88585.qm@web50908.mail.re2.yahoo.com> (raw)
In-Reply-To: <1193107511.26301.59.camel@jdavis>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ademaestri@yahoo.com, pgsql@j-davis.com
Subject: Re: Seqscan
In-Reply-To: <766975.88585.qm@web50908.mail.re2.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox