Marcus Engene <mengpg2(at)engene(dot)se> wrote:
> On 5/9/11 8:57 , Kevin Grittner wrote:
>>
>> That could be a difference is collations. What do you get from
>> the query on this page for each database?:
>>
>> http://wiki.postgresql.org/wiki/Server_Configuration
> There's indeed a different collation. Why is this affecting?
If the index isn't sorted in an order which leaves the rows you are
requesting near one another, it's not very useful for the query.
Try this query on both:
create temp table order_example (val text);
insert into order_example values ('a z'),('ab'),('123'),(' 456');
select * from order_example order by val;
> Can i force a column to be ascii?
You don't need to do that; you can specify an opclass for the index
to tell it that you don't want to order by the normal collation, but
rather in a way which will allow the index to be useful for pattern
matching:
http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html
> The (fast) test server:
> effective_cache_size | 512MB
> lc_collate | C
> lc_ctype | UTF-8
> maintenance_work_mem | 128MB
> max_connections | 100
> server_encoding | UTF8
> shared_buffers | 512MB
> temp_buffers | 8192
> TimeZone | Europe/Zurich
> wal_buffers | 1MB
> The (slow) production server:
> checkpoint_completion_target | 0.9
> checkpoint_segments | 64
> effective_cache_size | 48GB
> lc_collate | en_US.UTF-8
> lc_ctype | en_US.UTF-8
> listen_addresses | localhost,10.0.0.3,74.50.57.76
> maintenance_work_mem | 1GB
> max_connections | 600
> server_encoding | UTF8
> shared_buffers | 8GB
> temp_buffers | 32768
> TimeZone | UTC
As you've discovered, with that many differences, performance tests
on one machine may have very little to do with actual performance on
the other.
-Kevin