Re: question about indexing.

That's the answer!  Thanks Doug.

basement=# explain select * from game_developer where approved = 2;
Seq Scan on game_developer  (cost=0.00..1920.17 rows=48 width=46)

basement=# explain select * from game_developer where approved = int2(2);
Index Scan using game_developer_approved on game_developer
(cost=0.00..80.87 rows=48 width=46)

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: <pgsql-general@postgresql.org>; "Brian A Hirt" <bhirt@berkhirt.com>
Sent: Sunday, September 30, 2001 7:36 PM
Subject: Re: [GENERAL] question about indexing.


> "Brian Hirt" <bhirt@mobygames.com> writes:
>
> > I have a table with about 1 million rows in it.  One of the columns in
this
> > table is some sort of status (it's an int2).  Out of the million rows,
only
> > about 100 of the rows have a status that is not like the rest.
>
> > Yes, i do vacuum the table.
> >
> > Does anyone know how to avoid all these table scans?
>
> Cast the value you're testing against in the query to int2 and you may
> see an improvement.  The planner isn't currently smart enough to
> realize it can use the index when the test value in the query is an
> int4.
>
> -Doug
> --
> In a world of steel-eyed death, and men who are fighting to be warm,
> Come in, she said, I'll give you shelter from the storm.    -Dylan
>