Обсуждение: Index problem

Hi!

I am using PostgreSQL 7.1.2, and have problems making a query use an
index and not perform tablescans.

The table has the following definition:

        Attribute        |           Type           |
Modifier
------------------------+--------------------------+-------------------------------------------
  game_index_oid         | integer                  | not null
  gamegroup_oid          | integer                  |
  user_oid               | integer                  |
  marketplace_oid        | integer                  |
  number_of_participants | integer                  |
  total_value            | double precision         |
  avg_value              | double precision         |
  index_value            | double precision         |
  created_date           | timestamp with time zone | not null default
"timestamp"('now'::text)
  index_type             | character(1)             |
  market_index_value     | double precision         |

I have generated an index on the index_type field with the following
statement:

create index gi_index_type on game_indices(index_type);

As far as I can understand, the following statement;
select * from game_indices where index_type='G';

should use this index. But when I try explain, I get the following result:

 >explain select * from game_indices where index_type = 'G';
 >NOTICE:  QUERY PLAN:
 >
 >Seq Scan on game_indices  (cost=0.00..8454.04 rows=11080 width=72)
 >
 >EXPLAIN

However, if I try the same select but with a differend index_type value
I get:

 >explain select * from game_indices where index_type = 'M';
 >NOTICE:  QUERY PLAN:
 >
 >Index Scan using gi_index_type on game_indices  (cost=0.00..116.67
 >rows=33 width=72)
 >
 >EXPLAIN

So. When the constraint is for index_type='G', a seq scan is used, and
for other values of index_type the index is used. The table has 361000
entries, with the following index_type values:
count  | index_type
--------+------------
   11080 | G
     328 | M
  349958 |


Any ideas would be gratefully appreciated.

Regards
Rolf Woll
Anakon

Rolf Woll <rolf@anakon.no> writes:
> So. When the constraint is for index_type='G', a seq scan is used, and
> for other values of index_type the index is used. The table has 361000
> entries, with the following index_type values:
> count  | index_type
> --------+------------
>    11080 | G
>      328 | M
>   349958 |

The system thinks a seqscan is cheaper.  It might well be right; to
retrieve 3% of the tuples will probably mean hitting every page of the
table anyway, if the 'G' values are randomly scattered.  Have you tried
doing actual timings both ways?  (You can force use of the indexscan
with "set enable_seqscan to false".)

See also recent thread "again on index usage" in pgsql-hackers.
There's been some talk of reducing the default value of
random_page_cost, which would have the effect of making the planner
more willing to choose indexscans.

            regards, tom lane