> > > *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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
