Postgres Pro
Downloads
Home   >   mailing lists

Thread: Indexscan is only used if we use "limit n"

Indexscan is only used if we use "limit n"

From
Sebastián Baioni
Date:
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


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

Re: Indexscan is only used if we use "limit n"

From
Alvaro Herrera
Date:
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"

Re: Indexscan is only used if we use "limit n"

From
joao
Date:
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..

Re: Indexscan is only used if we use "limit n"

From
Mark Lewis
Date:
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

Re: Indexscan is only used if we use "limit n"

From
Josh Berkus
Date:
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