X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3DA49DAE64
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Oct 2005 18:22:58 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 99935-05
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Oct 2005 21:22:54 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.194])
	by svr1.postgresql.org (Postfix) with ESMTP id E0587DAF97
	for <pgsql-performance@postgresql.org>;
	Wed, 26 Oct 2005 18:22:55 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id 71so99921wra
	for <pgsql-performance@postgresql.org>;
	Wed, 26 Oct 2005 14:22:58 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=YP7QNdqlwgePnlHyAkpWHVYiJdE6uSkFqsW1bz3VctS8NK1wLq3XnH2rQW27azeimWz76NuOGH+/aT/i0Ya6lHJAai+eRyHYOYy3pEOF/y2iHuH8B86Mym57D56OAdKxtHab64MevKO60LAj7Lvg8t2gL4RV/I5v8jcvlVa4NuY=
Received: by 10.54.76.5 with SMTP id y5mr691637wra;
	Wed, 26 Oct 2005 14:22:58 -0700 (PDT)
Received: by 10.54.96.18 with HTTP; Wed, 26 Oct 2005 14:22:58 -0700 (PDT)
Message-ID: <cbd177510510261422h17f277d3w92c834164cf75a4e@mail.gmail.com>
Date: Wed, 26 Oct 2005 14:22:58 -0700
From: aurora <aurora00@gmail.com>
To: Mark Lewis <mark.lewis@mir3.com>
Subject: Re: browsing table with 2 million records
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1130360385.1156.12.camel@archimedes>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_12145_3071535.1130361778152"
References: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com>
	<1130360385.1156.12.camel@archimedes>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.758 required=5 tests=[AWL=-0.078,
	FROM_ENDS_IN_NUMS=0.516, HTML_10_20=0.295, HTML_MESSAGE=0.001,
	RCVD_BY_IP=0.024]
X-Spam-Level: 
X-Archive-Number: 200510/394
X-Sequence-Number: 15162

------=_Part_12145_3071535.1130361778152
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>> select * from table order by date limit 25 offset 0

> Do you have an index on the date column? Can you post an EXPLAIN
> ANALYZE for the slow query?

Wow! Now that I look again there are actually 2 date fields. One is indexed
and one is not. Order by was done on the column without index. Using the
indexed column turn a seq scan into index scan and the query performance is
totally fine now.

It would still be helpful if select count(*) can perform well.

Thanks!

Wy

------=_Part_12145_3071535.1130361778152
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

&gt;&gt;
&nbsp; select * from table order by date limit 25 offset 0<br>
<br>
&gt; Do you have an index on the date column? &nbsp;Can you post an EXPLAIN=
<br>
&gt; ANALYZE for the slow query?<br>
<br>
Wow! Now that I look again there are actually 2 date fields. One is
indexed and one is not. Order by was done on the column without index.
Using the indexed column turn a seq scan into index scan and the query
performance is totally fine now.<br>
<br>
It would still be helpful if select count(*) can perform well.<br>
<br>
Thanks!<br>
<br>
Wy<br>

------=_Part_12145_3071535.1130361778152--