Обсуждение: Seqscan

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,

---

¡Sé un mejor fotógrafo!
Perfecciona tu técnica y encuentra las mejores fotos en:
http://telemundo.yahoo.com/promos/mejorfotografo.html

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

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

(Please don't top-post. )

Adrian Demaestri skrev:
> */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.

> It is not actualy a table, sorry, it is a quite complex view that
> involve three large tables.


If hugetable isn't a table, you chose a really bad name for it.

What you have here is a specific query performing badly, not a generic
issue with all queries containing "LIMIT X". You might of course have
found a construct which the planner has problems with - but the first
step is to let us see the result of EXPLAIN ANALYZE.

Anyway, I think you might be hitting this issue:

"Fix mis-planning of queries with small LIMIT values due to poorly
thought out "fuzzy" cost comparison"
(http://www.postgresql.org/docs/8.0/static/release-8-0-4.html)

which was fixed in 8.0.4 . You should upgrade.

Nis