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