Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 013CCB5DBD9 for ; Tue, 4 Oct 2011 05:56:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 07259-01 for ; Tue, 4 Oct 2011 08:56:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from asmtpout030.mac.com (asmtpout030.mac.com [17.148.16.105]) by mail.postgresql.org (Postfix) with ESMTP id 8740FB5DBF4 for ; Tue, 4 Oct 2011 05:56:12 -0300 (ADT) MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-2 Received: from bifrenaria.man.poznan.pl ([150.254.149.135]) by asmtp030.mac.com (Oracle Communications Messaging Server 7u4-23.01 (7.0.4.23.0) 64bit (built Aug 10 2011)) with ESMTPSA id <0LSJ00GZBA474L20@asmtp030.mac.com> for pgsql-performance@postgresql.org; Tue, 04 Oct 2011 01:55:36 -0700 (PDT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.4.6813,1.0.211,0.0.0000 definitions=2011-10-04_04:2011-10-04, 2011-10-04, 1970-01-01 signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 ipscore=0 suspectscore=1 phishscore=0 bulkscore=0 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=6.0.2-1012030000 definitions=main-1110040024 Subject: Re: Query with order by and limit is very slow - wrong index used From: =?iso-8859-2?Q?Nowak_Micha=B3?= In-reply-to: Date: Tue, 04 Oct 2011 10:55:18 +0200 Content-transfer-encoding: quoted-printable Message-id: References: <6B8FADC8-9692-439F-8AA4-0CAA287FCEB0@me.com> <15615.1317654751@sss.pgh.pa.us> To: pgsql-performance@postgresql.org X-Mailer: Apple Mail (2.1244.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201110/32 X-Sequence-Number: 45254 Lowering random_page_cost didn't help -- I've tried values 2.0 and 1.5. Then I tried "order by id -1" hack Marcin Ma=F1k proposed... a9-dev=3D> create index foo on records(source_id, (id - 1)); CREATE INDEX a9-dev=3D> explain analyze select * from records where source_id = =3D'http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml' order by (id = -1) limit 200; QUERY PLAN = =20 = --------------------------------------------------------------------------= ----------------------------------------------------------- Limit (cost=3D0.00..379.42 rows=3D200 width=3D1124) (actual = time=3D0.137..255.283 rows=3D200 loops=3D1) -> Index Scan using foo on records (cost=3D0.00..1864617.14 = rows=3D982887 width=3D1124) (actual time=3D0.137..255.237 rows=3D200 = loops=3D1) Index Cond: ((source_id)::text =3D = 'http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml'::text) Total runtime: 255.347 ms (4 rows) Significant improvement :) As we can see, it is possible to query records fast without changing = table structure. Question is: can I do it without "hacks"?=20 Michal Nowak