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(at)wireboard(dot)com>
To: "Brian Hirt" <bhirt(at)mobygames(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>; "Brian A Hirt" <bhirt(at)berkhirt(dot)com>
Sent: Sunday, September 30, 2001 7:36 PM
Subject: Re: [GENERAL] question about indexing.

> "Brian Hirt" <bhirt(at)mobygames(dot)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
>