pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexscan is only used if we use "limit n"
5+ messages / 5 participants
[nested] [flat]
* Indexscan is only used if we use "limit n"
@ 2007-08-15 19:36 Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>
2007-08-15 20:00 ` Re: Indexscan is only used if we use "limit n" Alvaro Herrera <alvherre@commandprompt.com>
2007-08-15 20:02 ` Re: Indexscan is only used if we use "limit n" joao <joao.junior@bra03034.conab.gov.br>
2007-08-15 20:03 ` Re: Indexscan is only used if we use "limit n" Mark Lewis <mark.lewis@mir3.com>
2007-08-16 23:10 ` Re: Indexscan is only used if we use "limit n" Josh Berkus <josh@agliodbs.com>
0 siblings, 4 replies; 5+ messages in thread
From: Sebastián Baioni @ 2007-08-15 19:36 UTC (permalink / raw)
To: pgsql-performance
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..
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexscan is only used if we use "limit n"
2007-08-15 19:36 Indexscan is only used if we use "limit n" Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>
@ 2007-08-15 20:00 ` Alvaro Herrera <alvherre@commandprompt.com>
3 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2007-08-15 20:00 UTC (permalink / raw)
To: Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>; +Cc: pgsql-performance
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"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexscan is only used if we use "limit n"
2007-08-15 19:36 Indexscan is only used if we use "limit n" Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>
@ 2007-08-15 20:02 ` joao <joao.junior@bra03034.conab.gov.br>
3 siblings, 0 replies; 5+ messages in thread
From: joao @ 2007-08-15 20:02 UTC (permalink / raw)
To: sebaioni-postgresql@yahoo.com.ar; +Cc: pgsql-performance
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..
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexscan is only used if we use "limit n"
2007-08-15 19:36 Indexscan is only used if we use "limit n" Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>
@ 2007-08-15 20:03 ` Mark Lewis <mark.lewis@mir3.com>
3 siblings, 0 replies; 5+ messages in thread
From: Mark Lewis @ 2007-08-15 20:03 UTC (permalink / raw)
To: sebaioni-postgresql@yahoo.com.ar; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexscan is only used if we use "limit n"
2007-08-15 19:36 Indexscan is only used if we use "limit n" Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>
@ 2007-08-16 23:10 ` Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2007-08-16 23:10 UTC (permalink / raw)
To: pgsql-performance; sebaioni-postgresql@yahoo.com.ar
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-08-16 23:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-15 19:36 Indexscan is only used if we use "limit n" Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>
2007-08-15 20:00 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-08-15 20:02 ` joao <joao.junior@bra03034.conab.gov.br>
2007-08-15 20:03 ` Mark Lewis <mark.lewis@mir3.com>
2007-08-16 23:10 ` Josh Berkus <josh@agliodbs.com>
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