Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0AB6164FD3F for ; Tue, 2 Sep 2008 01:29:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 90563-08 for ; Tue, 2 Sep 2008 01:29:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.trinitydigital.com.au (mail.trinitydigital.com.au [122.102.101.9]) by postgresql.org (Postfix) with ESMTP id 1A4F464FCED for ; Tue, 2 Sep 2008 01:29:19 -0300 (ADT) Received: from localhost (unknown [122.102.101.11]) by mail.trinitydigital.com.au (Postfix) with ESMTP id 16DFD2BD29; Tue, 2 Sep 2008 13:50:19 +1000 (EST) Received: from mail.trinitydigital.com.au ([122.102.101.9]) by localhost (spam-filter.trinitydigital.com.au [122.102.101.11]) (amavisd-new, port 10024) with LMTP id 24575-01-77; Tue, 2 Sep 2008 13:51:47 +1000 (EST) Received: from [172.17.73.18] (ppp59-167-92-19.lns2.mel6.internode.on.net [59.167.92.19]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.trinitydigital.com.au (Postfix) with ESMTP id A03EE2B8B4; Tue, 2 Sep 2008 13:50:11 +1000 (EST) Message-ID: <48BCB90D.8090606@pws.com.au> Date: Tue, 02 Sep 2008 13:54:53 +1000 From: Russell Smith User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.16) Gecko/20080724 Thunderbird/2.0.0.16 Mnenhy/0.7.5.0 MIME-Version: 1.0 To: Pavel Stehule CC: David West , pgsql-performance@postgresql.org Subject: Re: limit clause breaks query planner? References: <1683189597512580954@unknownmsgid> <162867790809010552o624a347aif0eb5fc71e7bbad@mail.gmail.com> <2934211014530751484@unknownmsgid> <162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com> In-Reply-To: <162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Trinity Digital Spam Filter X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/21 X-Sequence-Number: 31334 Pavel Stehule wrote: > Hello > > 2008/9/1 David West : > >> Thanks for your suggestion but the result is the same. >> >> Here is the explain analyse output from different queries. >> Select * from my_table where A is null and B = '21' limit 15 >> >> "Limit (cost=0.00..3.68 rows=15 width=128) (actual time=85837.043..85896.140 rows=15 loops=1)" >> " -> Seq Scan on my_table this_ (cost=0.00..258789.88 rows=1055580 width=128) (actual time=85837.038..85896.091 rows=15 loops=1)" >> " Filter: ((A IS NULL) AND ((B)::text = '21'::text))" >> "Total runtime: 85896.214 ms" >> >> [snip] Further to Pavel's comments; (actual time=85837.038..85896.091 rows=15 loops=1) That's 85 seconds on a sequence scan to return the first tuple. The table is not bloated by any chance is it? Regards Russell