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

On Sun, 30 Sep 2001, Brian Hirt wrote:

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.

for example:
999,900 have the value 1
23 have the value 2
67 have the value 3
10 have the value 4

I often want to fetch the rows within that subset of 100. When i index this
column, the planner always seems to choose a table scan when i query it.
I've tried BTREE and HASH indexes and both do the same thing.

Yes, i do vacuum the table.

Does anyone know how to avoid all these table scans?

Under 7.1 and earlier, you're pretty much stuck with them unless you make
the frequent column value NULL (which has some pain and suffering involved
with queries if you aren't careful). I think 7.2 will handle this better.

"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

Hey Doug,

Hmmm... I just re-did two columns in a table from float4 from float8 'cause
the index was never used when executing a query. I'm guessing that the
values from the select were used as a float8 then? Thanks for the hint.

Later,
Rob

Good day,

I've noticed that PostgreSQL enters the ABORT STATE within a transaction
block when a simple syntax error is committed. Is there any kind of
configurable setting to adjust the sensitivity of what ERROR messages send
PostgreSQL into the ABORT STATE?

Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com