Richard Huxton <dev@archonet.com> writes:
I'm guessing an index isn't being used because your LANG is something-UTF-8 and that got picked up when you recreated your database. Try recreating the database with the C locale and see if that does it.
I never had LANG to be something-UTF-8 on the machine with the database. The databases were created using de_DE@euro (because this was the system default), no foreign characters are used anywhere.
Uhm, under no locale would an index be used for "ts LIKE '2003-04-%'" unless you have an functional index on ts::text, which would be kind of weird.
You might want to rewrite this as
ts BETWEEN '2003-04-01' AND '2003-04-01'+'1 month'
I already did something like that (as I described before). At least according to EXPLAIN both ways do the same thing.
or something like that. At least this way a straight normal index on ts would be usable work.
This was the hint which helped me out to direct my view. I found out that the indexes used were not sufficient. It seems that the queries developed over time but the index situation has never changed. In fact, the index on ts, which was available on other similar tables, was missing on the biggest and most used table :(
After creating some more indexes, everything works fine now. Same speed with Linux 2.4 and 2.6.
So, the performance "issue" only applies only to sequential scans. I just don't get why sequential scans in postgres 7.4.2 on Linux 2.6 are up to 10 times slower than sequential scans on Linux 2.4 (using the "LIKE" construct, still 2 times slower with "BETWEEN").
-dirk
--
D i r k F "o r s t e r l i n g
r@zorbla.de http://r.zorbla.de/
-------------
veQDuj'oH DujlIj'e'---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
