> > > *very* slow, due to seq scan on
> > > 20 million entries, which is a test setup up to now)
> >
> > Perennial first question: did you VACUUM ANALYZE?
>
> Can there, or could there, be a notion of "rule based" optimization of
> queries in PostgreSQL? The "not using index" problem is probably the
most
> common and most misunderstood problem.
There is a (sort of) rule based behavior in PostgreSQL,
the down side of the current implementation is, that certain
other commands than ANALYZE (e.g. "create index") partly update
optimizer statistics. This is bad behavior, since then only part
of the statistics are accurate. Statistics always have to be seen
in context to other table's and other index'es statistics.
Thus, currently the rule based optimizer only works if you create
the indexes on empty tables (before loading data), which obviously
has downsides. Else you have no choice but to ANALYZE frequently.
I have tried hard to fight for this pseudo rule based behavior,
but was only partly successful in convincing core. My opinion is,
that (unless runtime statistics are kept) no other command than
ANALYZE should be allowed to touch optimizer relevant statistics
(maybe unless explicitly told to).
Andreas