Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 777E164FF67 for ; Sun, 23 Nov 2008 17:41:18 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18992-02 for ; Sun, 23 Nov 2008 17:41:15 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 90C2464FEBD for ; Sun, 23 Nov 2008 17:41:15 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id mANLfCUw067119 for ; Sun, 23 Nov 2008 17:41:12 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id mANLcpBj064605 for pgsql-performance@postgresql.org; Sun, 23 Nov 2008 17:38:51 -0400 (AST) (envelope-from news) From: "Andrus" X-Newsgroups: pgsql.performance Subject: limit clause produces wrong query plan Date: Sun, 23 Nov 2008 22:52:54 +0200 Organization: Hub.Org Networking Services Lines: 40 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="windows-1257"; reply-type=original Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6001.18000 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 200811/220 X-Sequence-Number: 31951 Adding limit clause causes very slow query: explain analyze select * from firma2.dok where doktyyp='J' order by dokumnr limit 100 "Limit (cost=0.00..4371.71 rows=100 width=1107) (actual time=33189.971..33189.971 rows=0 loops=1)" " -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..278740.01 rows=6376 width=1107) (actual time=33189.959..33189.959 rows=0 loops=1)" " Filter: (doktyyp = 'J'::bpchar)" "Total runtime: 33190.103 ms" Without limit is is fast: explain analyze select * from firma2.dok where doktyyp='J' order by dokumnr "Sort (cost=7061.80..7077.74 rows=6376 width=1107) (actual time=0.119..0.119 rows=0 loops=1)" " Sort Key: dokumnr" " -> Index Scan using dok_doktyyp on dok (cost=0.00..3118.46 rows=6376 width=1107) (actual time=0.101..0.101 rows=0 loops=1)" " Index Cond: (doktyyp = 'J'::bpchar)" "Total runtime: 0.245 ms" How to fix this without dropping dok_doktyyp index so that limit can safely used for paged data access ? indexes: dok_doktyyp: dok(doktyyp) dok_dokumnr_idx: dok(dokumnr) types: dokumnr int primary key doktyyp char(1) Andrus. Using 8.1.4