Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 072AF9F9923 for ; Wed, 15 Aug 2007 16:43:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 80242-05 for ; Wed, 15 Aug 2007 16:43:24 -0300 (ADT) X-Greylist: delayed 00:06:39.986206 by SQLgrey-1.7.5 Received: from web36104.mail.mud.yahoo.com (web36104.mail.mud.yahoo.com [66.163.179.218]) by postgresql.org (Postfix) with SMTP id A75019F9760 for ; Wed, 15 Aug 2007 16:43:26 -0300 (ADT) Received: (qmail 48234 invoked by uid 60001); 15 Aug 2007 19:36:45 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.ar; h=X-YMail-OSG:Received:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=Eprl0zo5jUKw/vhjNLsNSG6VVi8HvilXrvD0djgLpMqqpSrwP2WawbnK8AV4JCjOCjYYfh4yZz8B8XmWrOL9+i9r14G2+V4EnEQU8GkYWT8rCsC1UTiWG1EKcKd7ZtcA0tup5BN8sxOMxYF6kiWJmNRO8WTx0iQb13/K8uWSvWU=; X-YMail-OSG: 3bs8Wm4VM1nxepeBzyfHXhyu3Yb7LSit4lu8C6yqiOR697xXKncx9YBwX30mwGqoXCehnR1XFIy8ApWno41PxouuVaJmv5YMqdjDY8lpdRCshZdCRItt5qzxRfZqNA-- Received: from [200.49.144.50] by web36104.mail.mud.yahoo.com via HTTP; Wed, 15 Aug 2007 16:36:45 ART Date: Wed, 15 Aug 2007 16:36:45 -0300 (ART) From: =?iso-8859-1?q?Sebasti=E1n=20Baioni?= Reply-To: sebaioni-postgresql@yahoo.com.ar Subject: Indexscan is only used if we use "limit n" To: Performance PostgreSQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1817478765-1187206605=:48008" Content-Transfer-Encoding: 8bit Message-ID: <377451.48008.qm@web36104.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200708/152 X-Sequence-Number: 26282 --0-1817478765-1187206605=:48008 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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.. --0-1817478765-1187206605=:48008 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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..
 --0-1817478765-1187206605=:48008--