simple case using index on windows but not on linux - Mailing list pgsql-performance

I have a simple case, selecting on a LIKE where clause over a single
column that has an index on it.  On windows it uses the index - on
linux it does not.  I have exactly the same scema and data in each,
and I have run the necessary analyze commands on both.

Windows is running 8.1.4
Linux is running from RPM postgresql-server-8.1.4-1.FC5.1

There are 1 million rows in the table - a number I would expect to
lower the score of a sequential scan for the planner.  There is an
index on 'c_number'.

On windows I get this:

orderstest=# explain analyze select * from t_order where c_number like '0001%';
                                                           QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_order_c_number on t_order  (cost=0.00..26.53
rows=928 width=43) (actual time=0.029..2.857 rows=1000 loops=1)
   Index Cond: (((c_number)::text >= '0001'::character varying) AND
((c_number)::text < '0002'::character varying))
   Filter: ((c_number)::text ~~ '0001%'::text)
 Total runtime: 4.572 ms
(4 rows)

Great - the index is used, and the query is lightning fast.

On Linux I get this:

orderstest=# explain analyze select c_number from t_order where
c_number like '0001%';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on t_order  (cost=0.00..20835.00 rows=983 width=11) (actual
time=1.364..1195.064 rows=1000 loops=1)
   Filter: ((c_number)::text ~~ '0001%'::text)
 Total runtime: 1197.312 ms
(3 rows)

I just can't use this level of performance in my application.

On my linux box, the only way I can get it to use the index is to use
the = operator.  If I use anything else, a seq scan is used.

Disabling sequence scans in the config has no effect.  It still does
not use the index for anything other than an = comparison.

Here is a dump of the table description:

orderstest=# \d t_order;
                   Table "public.t_order"
        Column         |          Type          | Modifiers
-----------------------+------------------------+-----------
 id                    | bigint                 | not null
 c_number              | character varying(255) |
 customer_id           | bigint                 |
 origincountry_id      | bigint                 |
 destinationcountry_id | bigint                 |
Indexes:
    "t_order_pkey" PRIMARY KEY, btree (id)
    "t_order_c_number" btree (c_number)
    "zzzz_3" btree (destinationcountry_id)
    "zzzz_4" btree (origincountry_id)
    "zzzz_5" btree (customer_id)
Foreign-key constraints:
    "fk9efdd3a33dbb666c" FOREIGN KEY (destinationcountry_id)
REFERENCES go_country(id)
    "fk9efdd3a37d3dd384" FOREIGN KEY (origincountry_id) REFERENCES
go_country(id)
    "fk9efdd3a38654c9d3" FOREIGN KEY (customer_id) REFERENCES t_party(id)

That dump is exactly the same on both machines.

The only major difference between the hardware is that the windows
machine has 2gb RAM and a setting of 10000 shared memory pages,
whereas the linux machine has 756Mb RAM and a setting of 3000 shared
memory pages (max. shared memory allocation of 32Mb).  I can't see any
other differences in configuration.

Disk throughput on both is reasonable (40Mb/second buffered reads)

Can anyone explain the difference in the planner behaviour on the two
systems, using what appears to be the same version of postgres?

--
Simon Godden