DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=mwCZN3q4w6q8qtJr9b15JUfjJhgYFY1mTbqkre3F7p7anx1G6zUPdgiZnyW5+zaqle
	o3/RZubq6QfBx7TVo/dRRqCmwVLaA73JH1nCQKYsCjPQm4MdtY0TQJBrT2A3DSVklRUc
	NN7YQXgPJz2HIIsQILeh90lMUjHOOH4wS/w+g=
Message-ID: <162867790809020046ne597803qa7f597a4fbd54d45@mail.gmail.com>
Date: Tue, 2 Sep 2008 09:46:58 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Guillaume Cottenceau" <gc@mnc.ch>
Subject: Re: limit clause breaks query planner?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <87iqtfouu6.fsf@mnc.ch>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1683189597512580954@unknownmsgid>
	<162867790809010552o624a347aif0eb5fc71e7bbad@mail.gmail.com>
	<2934211014530751484@unknownmsgid>
	<162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com>
	<87iqtfouu6.fsf@mnc.ch>

2008/9/2 Guillaume Cottenceau <gc@mnc.ch>:
> "Pavel Stehule" <pavel.stehule 'at' gmail.com> writes:
>
>> Hello
>>
>> 2008/9/1 David West <david.west@cusppoint.com>:
>>> 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?

yes, true, my mistake

Pavel

>
> 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
>