"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
<> psql(at)elbrief(dot)de> wrote:
>
>> insert into bla ( a , b )
>> select a , a
>> from generate_series( 1 , 1000000 ) as a ( a ) ;
>
>> explain analyze select * from bla
>> where b > 990000 order by a limit 10 ;
>> [uses index on b and has a long run time]
>
> The problem is that PostgreSQL doesn't have any sense of the
> correlation between columns a and b (i.e., they are always equal)
A belated thought on this -- you went right from your load to
running queries without leaving any time for autovacuum to kick in
and generate statistics. I recommend that somewhere after you
insert the data and before you run your selects, you run:
VACUUM ANALYZE bla;
This will get you to something which more closely resembles a
"steady state" environment.
-Kevin