First, make sure you ran vacuum analyze to update
the statistics for the table.
If a large portion of your table is going to be scanned,
Seq Scan is often faster than Index Scan due to possibly
random seeks within the heap file (the transaction commit
state isn't in the index, so there is still a read from the
heap to check if it's valid). The optimizer seems to think
333 records match num>2. Is this reasonable?
Stephan Szabo
sszabo@bigpanda.com
On Tue, 15 Aug 2000, Martin Dolog wrote:
> Hi *,
>
> I have pgsql7.0.2 on Linux2.2.16 and table with following indices:
>
> CREATE TABLE T1 (NUM INT NOT NULL, NAME VARCHAR(10) NOT NULL, POP
> VARCHAR(10) NOT NULL);
> CREATE INDEX T1_I_NUM ON T1(NUM);
> CREATE INDEX T1_I_NAME ON T1(NAME);
> CREATE INDEX T1_I_POP ON T1(POP);
>
> ... and some data, but I really don't understant how indices work, look
> at that:
>
> template1=# explain select * from t1 where num=2;
> NOTICE: QUERY PLAN:
> Index Scan using t1_i_num on t1 (cost=0.00..8.14 rows=10 width=28)
>
> what is ok, ***BUT***
>
> template1=# explain select * from t1 where num>2;
> NOTICE: QUERY PLAN:
> Seq Scan on t1 (cost=0.00..22.50 rows=333 width=28)
>
>
> WHY SEQ SCAN ?!!?
>
>
> thank you
>
> --
>
> #md
>