Re: stange optimizer results

trouble is that this SQL is being automatically created by my
object-relational mapping software and I can't easily customize it. Is
there any other way I can force Postgres to do the most efficient thing?



On Thu, 2002-11-21 at 10:35, Stephan Szabo wrote:
>
> 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.
>
>
--

Peter T. Brown
Director Of Technology
Memetic Systems, Inc.
"Translating Customer Data Into Marketing Action."
206.335.2927
http://www.memeticsystems.com/