Richard Huxton wrote:
Sebastian Böck wrote:
Richard Huxton wrote:
Sebastian Böck wrote:
Richard Huxton wrote:
Can you post the output from your "explain analyse" calls too? The
statistics aren't going to be the same on different machines.
Sure, here it is.
Thanks. (PS - remember to cc the list too).
[output of EXPLAIN ANALYZE]
OK - so what you want to know is why index "test_999" is used in the
second but not the first, even though both return the same rows.
The fact is that the conditional index:
CREATE INDEX test_999 ON test (datum)
WHERE version = '999' OR approved IS NOT NULL;
AFAIK looks at the WHERE clause of your query to determine where it
can run. Don't forget that the planner needs to pick which index is
best *before* it starts fetching data.
So - in the first example there might be rows where e.g.
t.version=998 which means test_999 would be a poor choice of index.
But what if the table users contains only 1 row and the column "version"
has a value of "999"?
It still doesn't know that the only value in "version" is 999(*). Let's
say there were 2000 rows and 1900 had the value 999 - the index is still
useless because we'd have to do a sequential scan to check the remaining
200 rows.
Are there any other options to speed up this kind of query?
Well, your problem is the (version=X OR approved IS NOT NULL) clause. I
must admit I can't quite see what this is supposed to do. The "test"
table connects to the "users" table via "version" (and "datum", though
not a simple check) unless the "test" has been "approved", in which case
it applies to all users?
Can you explain what the various tables/columns are really for?
The whole thing is a multiuser facility managment application.
Every user can plan things like he wants (different versions).
All these changes apply to a common (approved) version.
Things get complicated as everybody should be able to "travel"
through the history via the "datum" field.
That's why i need this "silly OR" in my where-clause.
At the moment i get very exciting results using immutable
functions, but i have another question.
In the docs it is stated that:
IMMUTABLE indicates that the function always returns the same
result when given the same argument values;
What if i define my functions like:
CREATE OR REPLACE FUNCTION datum () RETURNS TIMESTAMP AS '
SELECT datum FROM public.benutzer;
' LANGUAGE sql IMMUTABLE;
They normally (untill now) give the correct results,
also if the values in the underlaying view changes.
Can i relay on this or is it only luck.
(*) Don't forget the statistics for column values are usually
out-of-date compared to the actual data, so you can't rely on it.
I'm aware of that.
Thanks
Sebastian
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
