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


Mon, 13 Oct 2003 13:48:15 -1000
Seth Ladd <seth@picklematrix.net>
ways to force index use?
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


Mon, 13 Oct 2003 16:55:25 -0700
Josh Berkus <josh@agliodbs.com>
Re: ways to force index use?
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

Mon, 13 Oct 2003 20:01:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ways to force index use? 
Tom Lane wrote:
> 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.

I think that for 10% is still better and Index scan.
There is around a way to calculate this elbow ?



Regards
Gaetano Mendola


Tue, 14 Oct 2003 02:20:40 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: ways to force index use?