Indicies work on FreeBSD, not on Linux - Mailing list pgsql-hackers

I'm sure this is something simple, but my Linux development machine
running Postgresql 7.4.7 has very different behavior than my FreeBSD
production machine running Postgresql 7.4.2. I've got the same table
definitions, but I do have different data in the databases.

On FreeBSD, indicies do what I expect they would -- everything speeds up
nice and 'explain analyze' shows me that an index scan is happening.

On my Linux machine, the CREATE INDEX statement works, and I can see
the index when I view the table information. But 'explain analyze'
indicates that a sequential scan is still occuring.

Any ideas? 

############ FREEBSD BEFORE & AFTER INDEXING ###############

freebsd=# explain analyze select * from orders where or_status = 'N';
QUERYPLAN 
 
-------------------------------------------------------------------------------------------------------------Seq Scan
onorders  (cost=0.00..2590.03 rows=47 width=759) (actual
 
time=6029.894..6090.079 rows=38 loops=1)  Filter: (or_status = 'N'::bpchar)Total runtime: 6090.476 ms
(3 rows)

freebsd=# create index or_status_idx on orders(or_status);
CREATE INDEX
freebsd=# explain analyze select * from orders where or_status = 'N';
    QUERY PLAN 
 

----------------------------------------------------------------------------------------------------------------------------Index
Scanusing or_status_idx on orders  (cost=0.00..382.26 rows=96
 
width=759) (actual time=0.098..1.648 rows=38 loops=1)  Index Cond: (or_status = 'N'::bpchar)Total runtime: 1.866 ms
(3 rows)


############ LINUX BEFORE & AFTER INDEXING ###############

linux=# explain analyze select * from orders where or_status = 'N' order
by or_number;                                                   QUERY PLAN 
-------------------------------------------------------------------------------------------------------------------Sort
(cost=3978.80..3985.74 rows=2776 width=770) (actual
 
time=1304.325..1351.477 rows=7760 loops=1)  Sort Key: or_number  ->  Seq Scan on orders  (cost=0.00..3260.35 rows=2776
width=770)
(actual time=0.192..869.504 rows=7760 loops=1)        Filter: (or_status = 'N'::bpchar)Total runtime: 1361.277 ms
(5 rows)

linux=# create index or_status_idx on orders(or_status);
CREATE INDEX
linux=# explain analyze select * from orders where or_status = 'N' order
by or_number;                                                   QUERY PLAN 
-------------------------------------------------------------------------------------------------------------------Sort
(cost=3981.40..3988.35 rows=2779 width=770) (actual
 
time=1338.112..1384.465 rows=7760 loops=1)  Sort Key: or_number  ->  Seq Scan on orders  (cost=0.00..3260.54 rows=2779
width=770)
(actual time=0.185..891.342 rows=7760 loops=1)        Filter: (or_status = 'N'::bpchar)Total runtime: 1394.538 ms
(5 rows)


-- 
Christopher Farley
www.northernbrewer.com