[PERFORM] 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 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: [PERFORM] Indexscan is only used if we use limit n
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Indexscan is only used if we use limit n
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.. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Indexscan is only used if we use limit n
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
