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
