Обсуждение: Indexscan is only used if we use "limit n"

Hello,
Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305.
The query only uses the index if we have a "limit n":

Without "Limit n"
explain
select esapcuit, esapcuil
from esact00 t1
order by esapcuit, esapcuil

Sort  (cost=843833.82..853396.76 rows=3825177 width=30)
  Sort Key: esapcuit, esapcuil
  ->  Seq Scan on esact00 t1  (cost=0.00..111813.77 rows=3825177 width=30)

With "Limit n"
explain
select esapcuit, esapcuil
from esact00 t1
order by esapcuit, esapcuil
limit 1

Limit  (cost=0.00..1.86 rows=1 width=30)
  ->  Index Scan using uesact002 on esact00 t1  (cost=0.00..7129736.89 rows=3825177 width=30)

Our postgresql.conf is:
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on

Thank you.
Sebastián

---

¡Sé un mejor ambientalista!
Encontrá consejos para cuidar el lugar donde vivimos..

Sebastián Baioni escribió:
> Hello,
> Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305.
> The query only uses the index if we have a "limit n":

> Without "Limit n"
> explain
> select esapcuit, esapcuil
> from esact00 t1
> order by esapcuit, esapcuil
>
> Sort  (cost=843833.82..853396.76 rows=3825177 width=30)
>   Sort Key: esapcuit, esapcuil
>   ->  Seq Scan on esact00 t1  (cost=0.00..111813.77 rows=3825177 width=30)

That's right.  What else did you expect?  It estimates it has to return
3 million rows after all -- using an indexscan would be slow.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"

which column does your indice cover?

Em Qua, 2007-08-15 às 16:36 -0300, Sebastián Baioni escreveu:
> Hello,
> Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled
> by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305.
> The query only uses the index if we have a "limit n":
>
> Without "Limit n"
> explain
> select esapcuit, esapcuil
> from esact00 t1
> order by esapcuit, esapcuil
>
> Sort  (cost=843833.82..853396.76 rows=3825177 width=30)
>   Sort Key: esapcuit, esapcuil
>   ->  Seq Scan on esact00 t1  (cost=0.00..111813.77 rows=3825177
> width=30)
>
> With "Limit n"
> explain
> select esapcuit, esapcuil
> from esact00 t1
> order by esapcuit, esapcuil
> limit 1
>
> Limit  (cost=0.00..1.86 rows=1 width=30)
>   ->  Index Scan using uesact002 on esact00 t1  (cost=0.00..7129736.89
> rows=3825177 width=30)
>
> Our postgresql.conf is:
> enable_bitmapscan = on
> enable_hashagg = on
> enable_hashjoin = on
> enable_indexscan = on
> enable_mergejoin = on
> enable_nestloop = on
> enable_seqscan = on
> enable_sort = on
> enable_tidscan = on
>
> Thank you.
> Sebastián
>
>
> Sebastián Baioni
> http://www.acomplejados.com.ar
> http://www.extremista.com.ar
> http://www.coolartists.com.ar
>
>
> ______________________________________________________________________
>
> ¡Sé un mejor ambientalista!
> Encontrá consejos para cuidar el lugar donde vivimos..

On Wed, 2007-08-15 at 16:36 -0300, Sebastián Baioni wrote:
> Hello,
> Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled
> by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305.
> The query only uses the index if we have a "limit n":
>
> Without "Limit n"
> explain
> select esapcuit, esapcuil
> from esact00 t1
> order by esapcuit, esapcuil
>
> Sort  (cost=843833.82..853396.76 rows=3825177 width=30)
>   Sort Key: esapcuit, esapcuil
>   ->  Seq Scan on esact00 t1  (cost=0.00..111813.77 rows=3825177
> width=30)
>
> With "Limit n"
> explain
> select esapcuit, esapcuil
> from esact00 t1
> order by esapcuit, esapcuil
> limit 1

This isn't really unexpected-- it's faster to do a full sequential scan
of a table than it is to do a full index traversal over the table.  And
usually it's still cheaper even after sorting the results of the full
table scan.

So as near as we can tell, PG is just doing what it's supposed to do and
picking the best plan it can.

You didn't really ask a question-- is this causing problems somehow, or
were you just confused by the behavior?

-- Mark

Sebastian,

> Whe are running PostgreSQL 8.2.0 on amd64-portbld-freebsd6.2, compiled
> by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305. The query only uses the index
> if we have a "limit n":

Um, why are you running an unpatched version of 8.2?  You should be runing
8.2.4.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco