Re: stange optimizer results

On Thu, 21 Nov 2002, Stephan Szabo wrote:

> On 21 Nov 2002, Peter T. Brown wrote:
>
> > Hello--
> >
> > Attached is a file containing two SQL queries. The first take
> > prohibitively long to complete because, according to EXPLAIN, it ignore
> > two very important indexes. The second SQL query seems almost identical
> > to the first but runs very fast because, according to EXPLAIN, it does
> > uses all the indexes appropriately.
> >
> > Can someone please explain to me what the difference is here? Or if
> > there is something I can do with my indexes to make the first query run
> > like the second?
>
> It doesn't take into account that in general a=b, b=constant implies
> a=constant.
>
> Perhaps if you used explicit join syntax for visitor joining
> visitorextra it might help. Like doing:
> FROM visitor inner join visitorextra on (...)
> left outer join ...

Sent this too quickly. It probably won't make it use an index on
vistorextra, but it may lower the number of expected rows that it's going
to be left outer joining so that a nested loop and index scan makes sense.