Index / Performance issues - Mailing list pgsql-performance

Here's another example, just trying to pick out a single random entry out of a 170,000. 
First, the simple approach (status quo):

# explain analyze select * from my_events WHERE my_e_id = 10800000;                                                                    
Seq Scan on my_events  (cost=0.00..68132.85 rows=1 width=771) (actual time=15916.75..16337.31 rows=1 loops=1)
Total runtime: 16337.42 msec

Pretty darned slow.. (16 secs in fact, ouch).  So now lets try our idea with limiting the query by order it in reverse order, and limiting to 1 result (even though the limit is unnecessary, but performance is identical without it)

# explain analyze select * from my_events WHERE my_e_id = 10800000 ORDER BY my_e_id DESC LIMIT 1;
Limit  (cost=68132.86..68132.86 rows=1 width=771) (actual time=16442.42..16442.43 rows=1 loops=1)
  ->  Sort  (cost=68132.86..68132.86 rows=1 width=771) (actual time=16442.42..16442.42 rows=1 loops=1)
        ->  Seq Scan on my_events  (cost=0.00..68132.85 rows=1 width=771) (actual time=16009.50..16441.91 rows=1 loops=1)
Total runtime: 16442.70 msec

Well, that's not any better... over a few runs, sometimes this was even slower that the status quo.  Well, at this point there was only one thing left to try... put in a <= in place of =, and see if it made a difference.

# explain analyze select * from my_events WHERE my_e_id <= 10800000 ORDER BY my_e_id DESC LIMIT 1;
Limit  (cost=0.00..5.52 rows=1 width=771) (actual time=474.40..474.42 rows=1 loops=1)
  ->  Index Scan Backward using my_events_pkey on my_events  (cost=0.00..257365.51 rows=46663 width=771) (actual time=474.39..474.41 rows=2 loo
ps=1)
Total runtime: 474.55 msec

Oddly enough, it did... note the "Index Scan Backward"... finally!  So for whatever reason, the DB decides not to use an index scan unless there's a greater or less than comparison operator in conjunction with an ORDER BY/LIMIT.  Now it takes half a second, instead of 16.

# explain analyze select * from my_events WHERE my_e_id >= 10800000 ORDER BY my_e_id LIMIT 1;
Limit  (cost=0.00..2.03 rows=1 width=771) (actual time=1379.74..1379.76 rows=1 loops=1)
  ->  Index Scan using my_events_pkey on my_events  (cost=0.00..257365.51 rows=126765 width=771) (actual time=1379.73..1379.75 rows=2 loops=1)
Total runtime: 1380.10 msec

Just for fun, run it in regular order (front to back, versus back to front, looking for >=).  Sure enough, still far better than the scan... 1.4 seconds vs 16.  So even the worst case index scan is still far better than the default approach.  Note that I tried using "set enable_seqscan=off", and it STILL insisted on scanning the table, but even slower this time.

Am I missing something really obvious?  Is there a proven way to consistantly encourage it to use indexes for these sorts of (rather obvious) queries?

Several runs of the above resulted in some variations in run time, but the corresponding orders of difference performance stayed pretty consistant.  I'm just confused as to why I have to go through such convoluted methods to force it to use the index when its obviously a FAR more efficient route to go regardless of which order it scans it in (forwards or backwards).  Any thoughts are appreciated.  Thanks!

  Lucas.