X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 665C4528BB for ; Tue, 21 Jun 2005 11:42:46 -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 20273-02 for ; Tue, 21 Jun 2005 14:42:41 +0000 (GMT) Received: from sccmmhc91.asp.att.net (sccmmhc91.asp.att.net [204.127.203.211]) by svr1.postgresql.org (Postfix) with ESMTP id EBFE7528B7 for ; Tue, 21 Jun 2005 11:42:39 -0300 (ADT) Received: from juju.arbash-meinel.com ([12.214.18.81]) by sccmmhc91.asp.att.net (sccmmhc91) with ESMTP id <20050621144240m9100nhjfhe>; Tue, 21 Jun 2005 14:42:40 +0000 Received: from [192.168.1.11] (mail [192.168.1.1]) by juju.arbash-meinel.com (Postfix) with ESMTP id ABA1955FB6; Tue, 21 Jun 2005 09:42:39 -0500 (CDT) Message-ID: <42B8275C.8040300@arbash-meinel.com> Date: Tue, 21 Jun 2005 09:42:36 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Yves Vindevogel Cc: pgsql-performance@postgresql.org Subject: Re: Limit clause not using index References: In-Reply-To: X-Enigmail-Version: 0.92.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig28E9E8563DA11CE837DE20EA" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.031 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/378 X-Sequence-Number: 13015 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig28E9E8563DA11CE837DE20EA Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Yves Vindevogel wrote: > Hi, > > I have a very simple query on a big table. When I issue a "limit"=20 > and/or "offset" clause, the query is not using the index. > Can anyone explain me this ? You didn't give enough information. What does you index look like that=20 you are expecting it to use? Generally, you want to have matching columns. So you would want CREATE INDEX blah ON tblprintjobs(loginuser, desceventdate, desceventtime= ); Next, you should post EXPLAIN ANALYZE instead of regular explain, so we=20 can have an idea if the planner is actually making correct estimations. John =3D:-> > > rvponp=3D# explain select * from tblprintjobs order by loginuser,=20 > desceventdate, desceventtime offset 25 limit 25 ; > QUERY PLAN > -----------------------------------------------------------------------= ------------=20 > > Limit (cost=3D349860.62..349860.68 rows=3D25 width=3D206) > -> Sort (cost=3D349860.56..351416.15 rows=3D622236 width=3D206) > Sort Key: loginuser, desceventdate, desceventtime > -> Seq Scan on tblprintjobs (cost=3D0.00..25589.36 rows=3D622236 width=3D= 206) > (4 rows) > > rvponp=3D# explain select * from tblprintjobs order by loginuser,=20 > desceventdate, desceventtime ; > QUERY PLAN > -----------------------------------------------------------------------= ------=20 > > Sort (cost=3D349860.56..351416.15 rows=3D622236 width=3D206) > Sort Key: loginuser, desceventdate, desceventtime > -> Seq Scan on tblprintjobs (cost=3D0.00..25589.36 rows=3D622236 width=3D= 206) > (3 rows) > > Met vriendelijke groeten, > Bien =E0 vous, > Kind regards, > > *Yves Vindevogel* > *Implements* --------------enig28E9E8563DA11CE837DE20EA Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCuCdfJdeBCYSNAAMRArOGAJ47wC9pDp+ZHjj9CsP69UQVCCabHgCfVz5J Pstlzi8Xqe0keB+sTv9lAgk= =Qudp -----END PGP SIGNATURE----- --------------enig28E9E8563DA11CE837DE20EA--