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 table,

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, I do

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 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 sequential

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 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 dramatically when a WHERE

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 question. The

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 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 (specifically

[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

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