Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 573D464FCDF for ; Tue, 2 Sep 2008 04:44:44 -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 68659-06 for ; Tue, 2 Sep 2008 04:44:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205]) by postgresql.org (Postfix) with ESMTP id 783F864FCDA for ; Tue, 2 Sep 2008 04:44:38 -0300 (ADT) Received: from localhost (mail2.mnc.ch [127.0.0.1]) by mail.mnc.ch (Postfix) with ESMTP id 62CC523086; Tue, 2 Sep 2008 07:44:34 +0000 (GMT) X-Virus-Scanned: amavisd-new at mnc.ch Received: from mail.mnc.ch ([127.0.0.1]) by localhost (mail2.mnc.ch [127.0.0.1]) (amavisd-new, port 10025) with ESMTP id 6uym7OhQrHcb; Tue, 2 Sep 2008 07:44:34 +0000 (GMT) Received: from meuh.localdomain (62-2-77-204.static.cablecom.ch [62.2.77.204]) by mail.mnc.ch (Postfix) with ESMTP id 089FC23085; Tue, 2 Sep 2008 07:44:34 +0000 (GMT) Received: by meuh.localdomain (Postfix, from userid 1000) id EC1DF183626; Tue, 2 Sep 2008 09:44:33 +0200 (CEST) To: "Pavel Stehule" Cc: pgsql-performance@postgresql.org Subject: Re: limit clause breaks query planner? References: <1683189597512580954@unknownmsgid> <162867790809010552o624a347aif0eb5fc71e7bbad@mail.gmail.com> <2934211014530751484@unknownmsgid> <162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com> From: Guillaume Cottenceau X-Hashcash: 1:20:080902:pavel.stehule@gmail.com::MqeZ5zxVE49gelkN:000000000000000000000000000000000000000L5O X-Hashcash: 1:20:080902:pgsql-performance@postgresql.org::th4owkHp4FecmcHg:0000000000000000000000000000001R7 Date: Tue, 02 Sep 2008 09:44:33 +0200 In-Reply-To: <162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com> (Pavel Stehule's message of "Mon\, 1 Sep 2008 21\:17\:17 +0200") Message-ID: <87iqtfouu6.fsf@mnc.ch> User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii 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/25 X-Sequence-Number: 31338 "Pavel Stehule" writes: > 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" >> > > I see it - problem is in statistics - system expect 1055580, but there > is only 15 values. Aren't you rather seeing the effect of the limit clause? gc=# create table foo ( bar int ); CREATE TABLE gc=# insert into foo ( select generate_series(0, 10000000) / 1000000 ); INSERT 0 10000001 gc=# analyze foo; ANALYZE gc=# explain analyze select * from foo where bar = 8 limit 15; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..2.30 rows=15 width=4) (actual time=2379.878..2379.921 rows=15 loops=1) -> Seq Scan on foo (cost=0.00..164217.00 rows=1070009 width=4) (actual time=2379.873..2379.888 rows=15 loops=1) Filter: (bar = 8) Total runtime: 2379.974 ms (on 8.3.1) -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36