Am 05/17/2004 10:19 PM schrieb Greg Stark:
Richard Huxton <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] (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
[EMAIL PROTECTED] http://r.zorbla.de/
-------------
veQDuj'oH DujlIj'e'
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
