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