Skip site navigation (1) Skip section navigation (2)

Re: limit clause breaks query planner?

From: Guillaume Cottenceau <gc(at)mnc(dot)ch>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: limit clause breaks query planner?
Date: 2008-09-02 07:44:33
Message-ID: 87iqtfouu6.fsf@mnc.ch (view raw or flat)
Thread:
Lists: pgsql-performance
"Pavel Stehule" <pavel.stehule 'at' gmail.com> writes:

> Hello
>
> 2008/9/1 David West <david(dot)west(at)cusppoint(dot)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?

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

In response to

Responses

pgsql-performance by date

Next:From: Pavel StehuleDate: 2008-09-02 07:46:58
Subject: Re: limit clause breaks query planner?
Previous:From: Thomas FinneidDate: 2008-09-02 06:39:24
Subject: Re: slow update of index during insert/copy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group