From: | Ben <bench(at)silentmedia(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: what's going on here? |
Date: | 2001-03-10 02:12:06 |
Message-ID: | Pine.LNX.4.10.10103091802440.28803-100000@gilgamesh.eos.SilentMedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Wrubleski | 2001-03-10 02:18:00 | Determine Time in other Time Zone |
Previous Message | Lee Harr | 2001-03-10 02:05:36 | Re: inheritance and primary/foreign keys |