Re: why sequential scan

On Thu, Aug 16, 2001 at 08:10:41PM -0400, newsreader@mediaone.net wrote:
> Ok I set enable_hashjoin and enable_mergejoin to off
> and performance is much much worse: just over 1 second
> job becomes a minute job
>
> Perhaps I should re-check if the database
> gets bigger.
>
> Thanks a lot
>
> On Thu, Aug 16, 2001 at 12:45:28PM -0400, Tom Lane wrote:
> > newsreader@mediaone.net writes:
> > > I would then iterate over each id I get and
> > > look up in item like this
> >
> > > q=> select * from item where item =? order by finish
> >
> > That's a nestloop join with inner indexscan.  The planner did consider
> > that, and rejected it as slower than the hashjoin it chose.  Now,
> > whether its cost model is accurate for your situation is hard to tell;
> > but personally I'd bet that it's right.  1500 index probes probably
> > are slower than a sequential scan over 5000 items.
> >
> > You could probably force the planner to choose that plan by setting
> > enable_hashjoin and enable_mergejoin to OFF.  It'd be interesting to
> > see the EXPLAIN result in that situation, as well as actual timings
> > of the query both ways.
> >
> >             regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html