Stephen Toney <toney-HRyyShxxSFfDS4vTE2/[EMAIL PROTECTED]>
wrote:
select count(*) from keyword a, keyword b where a.key=b.key and
a.value='music' and b.value='history';
0|0|TABLE keyword AS a WITH INDEX value
1|1|TABLE keyword AS b WITH INDEX value
4,318 records have value='music' and 27,058 have value='history'.
Try running ANALYZE statement. The optimizer might be able to choose
better plan after that.
If this doesn't help, try this query:
select count(*) from keyword a, keyword b where a.key=b.key and
a.value='music' and b.value||''='history';
Using an expression in place of b.value prevents the optimizer from
using an index on it, at which point it hopefully would use one on
b.key. This would result in O(M log N) performance, where M=4318 (the
number of records with value='music') and N is the total number of
records. The query plan used now results in O(M*M') where M=4318 and
M'=27058 - a much worse complexity.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
