Re: Yet again on indices...

On Wed, 27 Feb 2002, Jean-Paul ARGUDO wrote:

> EXPLAIN SELECT t12_bskid, t12_pnb, t12_lne, t12_tck
> FROM T12_20011231
> WHERE t12_bskid >= 1
> ORDER BY t12_bskid, t12_pnb, t12_tck, t12_lne;
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=3006.13..3006.13 rows=25693 width=46)
> -> Seq Scan on t12_20011231 (cost=0.00..1124.20 rows=25693 width=46)
>
> => not good, table t12_20011231 as 26K tuples :-(

>
> => create index t12_idx_bskid_20011231 on t12_20011231 (t12_bskid);
>
> Sort (cost=3006.13..3006.13 rows=25693 width=46)
> -> Seq Scan on t12_20011231 (cost=0.00..1124.20 rows=25693 width=46)
>
> => probably statistic refresh to be done:
> $ /usr/local/pgsql/bin/vacuumdb --analyze dbks
>
> Sort (cost=3006.13..3006.13 rows=25693 width=46)
> -> Seq Scan on t12_20011231 (cost=0.00..1124.20 rows=25693 width=46)
>
>
> => Uh? Seq scan cheaper than index???
>
> => let's disable seqscan to read cost of index:
> postgresql.conf : enable_seqscan = false
>
> Sort (cost=3126.79..3126.79 rows=25693 width=46)
> -> Index Scan using t12_idx_bskid_20011231 on t12_20011231
> (cost=0.00..1244.86 rows=25693 width=46)
>
> => Uh? seq scan'cost is lower than index scan?? => mailto hackers
>
> ----
>

> What's your opinion?

Well you didn't send the schema, or explain analyze results to show
which is actually faster, but...

Sequence scan *can be* faster than index scan when a large portion of the
table is going to be read. If the data is randomly distributed,
eventually you end up reading most/all of the table blocks anyway to get
the validity information for the rows and you're doing it in random order,
plus you're reading parts of the index as well. How many rows are in
the table, and how many match t12_bskid >=1?