Marcus Engene <mengpg2@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
