Thread: ways to force index use?

Hello,

Thanks to all the previous suggestions for my previous question.  I've
done a lot more research and playing around since then, and luckily I
think I have a better understanding of postgresql.

I still have some queries that don't use an index, and I was wondering
if there were some other tricks to try out?

My system: RH9, PG 7.3.4, IDE, 1 gig RAM, celeron 1.7
My Table Columns (all bigints): start, stop, step1, step2, step3
My Indexes: btree(start), btree(stop), btree(start, stop)
Size of table: 16212 rows
Params: shared_buffers = 128, effective_cache_size = 8192

The Query: explain analyze select * from path where start = 653873 or
start = 649967 or stop = 653873 or stop = 649967

The Result:
Seq Scan on "path"  (cost=0.00..450.22 rows=878 width=48) (actual time=0
.08..40.50 rows=1562 loops=1)
 Filter: (("start" = 653873) OR ("start" = 649967) OR (stop = 653873) OR
 (stop = 649967))
Total runtime: 42.41 msec

Does anyone have a suggestion on how to get that query to use an index?
  Is it even possible?  I did run vacuum analyze right before this test.

I'm only beginning to customize the parameters in postgresql.conf
(mainly from tips from this list).

Thanks very much!
Seth

Seth,

> The Query: explain analyze select * from path where start = 653873 or
> start = 649967 or stop = 653873 or stop = 649967

you need to cast all those numbers to BIGINT:

select * from path where start = 653873::BIGINT or
start = 649967::BIGINT or stop = 653873::BIGINT or stop = 649967::BIGINT

Then an index on start or stop should be used by the planner.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Seth Ladd <seth@picklematrix.net> writes:
> My Table Columns (all bigints): start, stop, step1, step2, step3
                    ^^^^^^^^^^^

> The Query: explain analyze select * from path where start = 653873 or
> start = 649967 or stop = 653873 or stop = 649967

> Does anyone have a suggestion on how to get that query to use an index?

Coerce the constants to bigint, for starters.  However, a query that is
selecting almost 10% of the table, as your example is, probably
*shouldn't* be using an index.

            regards, tom lane

On Monday, Oct 13, 2003, at 21:24 Pacific/Honolulu, mila wrote:

> Seth,
>
>> My system: RH9, PG 7.3.4, IDE, 1 gig RAM, celeron 1.7
> ...
>> Size of table: 16212 rows
>> Params: shared_buffers = 128, effective_cache_size = 8192
>
> Just in case,
> the "shared_buffers" value looks a bit far too small for your system.
> I think you should raise it to at least 1024, or so.
>
> Effective cache size could be (at least) doubled, too ==> this might
> help forcing the index use.

Thanks!  I'm just beginning to play with these numbers.  I'll
definitely try them out.

I can't wait to try out the script that will help set these parameters!
:)

Seth