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?
(*) 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.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
