Am 05/17/2004 10:19 PM schrieb Greg Stark:
> 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'