Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Dan Langille
On 21 Jan 2005 at 8:38, Russell Smith wrote: > On Fri, 21 Jan 2005 02:36 am, Dan Langille wrote: > > On 20 Jan 2005 at 7:26, Stephan Szabo wrote: > > [snip] > > > Honestly I expected it to be slower (which it was), but I figured > > > it's worth seeing what alternate plans it'll generate > > > (s

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Russell Smith
On Fri, 21 Jan 2005 02:36 am, Dan Langille wrote: > On 20 Jan 2005 at 7:26, Stephan Szabo wrote: [snip] > > Honestly I expected it to be slower (which it was), but I figured it's > > worth seeing what alternate plans it'll generate (specifically to see how > > it cost a nested loop on that join to

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Dan Langille
On 20 Jan 2005 at 7:26, Stephan Szabo wrote: > On Thu, 20 Jan 2005, Dan Langille wrote: > > > On 20 Jan 2005 at 6:14, Stephan Szabo wrote: > > > > > On Wed, 19 Jan 2005, Dan Langille wrote: > > > > > > > Hi folks, > > > > > > > > Running on 7.4.2, recently vacuum analysed the three tables in > >

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Stephan Szabo
On Thu, 20 Jan 2005, Dan Langille wrote: > On 20 Jan 2005 at 6:14, Stephan Szabo wrote: > > > On Wed, 19 Jan 2005, Dan Langille wrote: > > > > > Hi folks, > > > > > > Running on 7.4.2, recently vacuum analysed the three tables in > > > question. > > > > > > The query plan in question changes drama

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Dan Langille
On 20 Jan 2005 at 6:14, Stephan Szabo wrote: > On Wed, 19 Jan 2005, Dan Langille wrote: > > > Hi folks, > > > > Running on 7.4.2, recently vacuum analysed the three tables in > > question. > > > > The query plan in question changes dramatically when a WHERE clause > > changes from ports.broken to

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Stephan Szabo
On Wed, 19 Jan 2005, Dan Langille wrote: > Hi folks, > > Running on 7.4.2, recently vacuum analysed the three tables in > question. > > The query plan in question changes dramatically when a WHERE clause > changes from ports.broken to ports.deprecated. I don't see why. > Well, I do see why: a seq

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Dan Langille
On 20 Jan 2005 at 9:34, Ragnar Hafstað wrote: > On Wed, 2005-01-19 at 20:37 -0500, Dan Langille wrote: > > Hi folks, > > > > Running on 7.4.2, recently vacuum analysed the three tables in > > question. > > > > The query plan in question changes dramatically when a WHERE clause > > changes from por

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Ragnar Hafstað
On Wed, 2005-01-19 at 20:37 -0500, Dan Langille wrote: > Hi folks, > > Running on 7.4.2, recently vacuum analysed the three tables in > question. > > The query plan in question changes dramatically when a WHERE clause > changes from ports.broken to ports.deprecated. I don't see why. > Well,

Re: [PERFORM] index scan of whole table, can't see why

2005-01-20 Thread Ragnar Hafstað
On Wed, 2005-01-19 at 21:00 -0800, [EMAIL PROTECTED] wrote: > Let's see if I have been paying enough attention to the SQL gurus. > The planner is making a different estimate of how many deprecated<>'' versus > how many broken <> ''. > I would try SET STATISTICS to a larger number on the ports ta

Re: [PERFORM] index scan of whole table, can't see why

2005-01-19 Thread andrew
Let's see if I have been paying enough attention to the SQL gurus. The planner is making a different estimate of how many deprecated<>'' versus how many broken <> ''. I would try SET STATISTICS to a larger number on the ports table, and re-analyze. ---(end of broadcast)-

[PERFORM] index scan of whole table, can't see why

2005-01-19 Thread Dan Langille
Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes fro