Re: what's going on here?

On Fri, 9 Mar 2001, Tom Lane wrote:

> No.  The fact that the planner doesn't derive an estimate could be fixed
> with not too much work (but it won't happen for 7.1).  Making IS NULL an
> indexable condition would affect considerably more code, and it's not
> really what's making the difference here anyway --- although it would
> allow us to replace the seqscan on playlist with an indexscan, which'd
> be even faster.

It would be nice if postgres could index on IS NULL, or if it was
documented that indexes don't get used here. Otherwise I would have used
played == 0 to mean the same thing that I'm trying to get out of played IS
NULL..... doing that lets explain give a very accurate picture.

> No.  Your second query is using nested loop with inner indexscan joins.
> That's fine for *small* numbers of rows returned by the outer subquery,
> but it goes to hell in a handbasket for large numbers of rows.  The
> planner is doing the right thing to switch to a heavier-duty plan with
> more startup overhead ... or it would be if it had the right idea about
> how many rows are going to come out of playlist, that is.

Ahhh, so desu. Thanks for the explinations.

> The real issue here is the lack of estimation of IS NULL selectivity.
> I was aware that that was broken, but I don't recall having seen a
> real-life example before where it really mattered.  I'll try to move
> it up a notch or two on my to-do list.  Or if someone else wants to
> dive in, feel free...

Yeah, like I said above, being about to use an accurate index for
estimating the size of the inner loop makes quite a difference. But at
least in this case, I had a value I could use in place of NULL, so it's
hardly a high priority.

Thanks again.