Thread: question about indexing.

question about indexing.

From

"Brian Hirt"

Date:

01 October 2001, 00:01:31

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?

Thanks,

Brian

Re: question about indexing.

From

Stephan Szabo

Date:

01 October 2001, 00:29:52

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.

Re: question about indexing.

From

"Brian Hirt"

Date:

01 October 2001, 01:00:49

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
>

Re: question about indexing.

From

Doug McNaught

Date:

01 October 2001, 01:01:36

"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

Re: question about indexing.

From

"Creager, Robert S"

Date:

01 October 2001, 17:58:36

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


> -----Original Message-----
> From: Doug McNaught [mailto:doug@wireboard.com]
> Sent: Sunday, September 30, 2001 7:36 PM
> To: Brian Hirt
> Cc: pgsql-general@postgresql.org; Brian A Hirt
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Abort State sensitivity?

From

"Command Prompt, Inc."

Date:

09 October 2001, 16:52:31

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