Re: Another optimizer question

> > SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 =
> > Table2.Field1)
> > WHERE Table1.Field1 = 'SomeValue';
> > [ is slow, but this is fast: ]
> > SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 =
> > Table2.Field1)
> > WHERE Table1.Field1 = 'SomeValue' AND Table2.Field1 = 'SomeValue';
>
> You're correct, we do not deduce the latter from the former, and so the
> first example does not realize that it could use a constrained indexscan
> over Table2.
>
> Maybe we should try to do this, but I suspect we'd waste more cycles
> trying than we'd make back on the queries where it helps.

Hmm... As far as I can tell, it would help in any query joining several
tables on the same field in one table. I don't know how this compares to an
"average" case, but it is certainly common in the databases that I'm
working on... It would be nice if there was at least an option to enable
this sort of behaviour.

At the moment, the 6 table joins I am doing are so painfully slow, no
matter how I optimize the queries/views that I am going to forget about the
whole idea of views and set up some code that will do the separate table
subqueries manually. Even with _ALL_ fields that joins are performed on are
indexed in hash or btree, it still takes forever. Seen as I have to do this
often, unless I can find a way to speed up my queries by an order of
magnitude (or several in some cases), I don't see any other way around
this.

If I do a view that produces the data I want through joins, it takes hours,
even with all fields indexed, and after VACUUM ANALYZE. Doing SET ENABLE
SEQ_SCAN = OFF doesn't seem to make any difference. The query plan changes,
but select times are still roughly the same... Doing the separate
subqueries on each table and joining data manualy in the application code
takes literaly seconds. I am sure that cannot be right and I must be doing
something wrong, so if anyone has a good idea of how to solve this type of
problem, I'm not sure I have a lot of options left...

Regards.

Gordan