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
