simple case using index on windows but not on linux

From: "simon godden" <sgodden(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: simple case using index on windows but not on linux
Date: 2006-10-04 07:48:03
Message-ID: 168519350610040048m7c4031e3t1a24b3fd4d9265a3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Luc Delgado 2006-10-04 08:03:00 Unsubscribe
Previous Message Adnan DURSUN 2006-10-04 02:24:19 Re: PostgreSQL Caching