Skip site navigation (1) Skip section navigation (2)

Indicies work on FreeBSD, not on Linux

From: Christopher Farley <chris(at)northernbrewer(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Indicies work on FreeBSD, not on Linux
Date: 2005-03-05 08:18:37
Message-ID: 20050305081836.GA21744@northernbrewer.com (view raw or flat)
Thread:
Lists: 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';
                                                 QUERY PLAN 
-------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (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 Scan using 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

Responses

pgsql-hackers by date

Next:From: Pailloncy Jean-GerardDate: 2005-03-05 08:59:14
Subject: Re: bitmap AM design
Previous:From: Greg StarkDate: 2005-03-05 01:10:52
Subject: Re: 8.0.X and the ARC patent

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group