Re: [PERFORM] Sort and index

2005-05-14 Thread Jim C. Nasby
On Thu, May 12, 2005 at 08:54:48PM +0200, Manfred Koizar wrote: > On Wed, 11 May 2005 16:15:16 -0500, "Jim C. Nasby" <[EMAIL PROTECTED]> > wrote: > >> This is divided by the number of index columns, so the index correlation > >> is estimated to be 0.219. > > > >That seems like a pretty bad assumpti

Re: [PERFORM] Sort and index

2005-05-12 Thread Manfred Koizar
On Wed, 11 May 2005 16:15:16 -0500, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: >> This is divided by the number of index columns, so the index correlation >> is estimated to be 0.219. > >That seems like a pretty bad assumption to make. Any assumption we make without looking at entire index tuples h

Re: [PERFORM] Sort and index

2005-05-11 Thread Jim C. Nasby
First, I've got some updated numbers up at http://stats.distributed.net/~decibel/ timing2.log shows that the planner actually under-estimates an index scan by several orders of magnitude. Granted, random_page_cost is set to an unrealistic 1.1 (otherwise I can't force the index scan), but that alon

Re: [PERFORM] Sort and index

2005-05-11 Thread Manfred Koizar
On Sun, 24 Apr 2005 17:01:46 -0500, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: >> >> Feel free to propose better cost equations. I did. More than once. >estimated index scan cost for (project_id, id, date) is >0.00..100117429.34 while the estimate for work_units is >0.00..103168408.62; almost no

Re: [PERFORM] Sort and index

2005-04-24 Thread Jim C. Nasby
On Sat, Apr 23, 2005 at 01:00:40AM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > >> Feel free to propose better cost equations. > > > Where would I look in code to see what's used now? > > All the gold is hidden in src/backend/optimizer/path/costsize.c. > >

Re: [PERFORM] Sort and index

2005-04-22 Thread Tom Lane
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: >> Feel free to propose better cost equations. > Where would I look in code to see what's used now? All the gold is hidden in src/backend/optimizer/path/costsize.c. regards, tom lane ---(end of broadcast)

Re: [PERFORM] Sort and index

2005-04-22 Thread Jim C. Nasby
On Fri, Apr 22, 2005 at 10:08:06PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > I've run some performance tests. The actual test case is at > > http://stats.distributed.net/~decibel/timing.sql, and the results are at > > http://stats.distributed.net/~decibel/timing.log. I

Re: [PERFORM] Sort and index

2005-04-22 Thread Tom Lane
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > I've run some performance tests. The actual test case is at > http://stats.distributed.net/~decibel/timing.sql, and the results are at > http://stats.distributed.net/~decibel/timing.log. In a nutshell, doing > an index scan appears to be about 2x faster

Re: [PERFORM] Sort and index

2005-04-22 Thread Jim C. Nasby
I've run some performance tests. The actual test case is at http://stats.distributed.net/~decibel/timing.sql, and the results are at http://stats.distributed.net/~decibel/timing.log. In a nutshell, doing an index scan appears to be about 2x faster than a sequential scan and a sort. Something else

Re: [PERFORM] Sort and index

2005-04-20 Thread Andrei Gaspar
Michael Fuhr wrote: On Mon, Apr 18, 2005 at 10:44:43AM -0500, Dave Held wrote: I thought that an index can be used for sorting. I'm a little confused about the following result: create index OperationsName on Operations(cOperationName); explain SELECT * FROM Operations ORDER BY cOperationName;

Re: [PERFORM] Sort and index

2005-04-19 Thread Jim C. Nasby
On Tue, Apr 19, 2005 at 11:01:26PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Actually, the planner (at least in 7.4) isn't smart enough to consider > > if the sort would fit in memory or not. > > Really? Have you read cost_sort()? > > It's certainly possible that th

Re: [PERFORM] Sort and index

2005-04-19 Thread Tom Lane
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Actually, the planner (at least in 7.4) isn't smart enough to consider > if the sort would fit in memory or not. Really? Have you read cost_sort()? It's certainly possible that the calculation is all wet, but to claim that the issue is not considered

Re: [PERFORM] Sort and index

2005-04-19 Thread Jim C. Nasby
On Mon, Apr 18, 2005 at 10:44:43AM -0500, Dave Held wrote: > Since you are fetching the entire table, you are touching all the rows. > If the query were to fetch the rows in index order, it would be seeking > all over the table's tracks. By fetching in sequence order, it has a > much better chance

Re: [PERFORM] Sort and index

2005-04-18 Thread Michael Fuhr
On Mon, Apr 18, 2005 at 10:44:43AM -0500, Dave Held wrote: > > > > I thought that an index can be used for sorting. > > I'm a little confused about the following result: > > > > create index OperationsName on Operations(cOperationName); > > explain SELECT * FROM Operations ORDER BY cOperationName

Re: [PERFORM] Sort and index

2005-04-18 Thread Andrei Gaspar
Thanks for the quick response Andrei Dave Held wrote: -Original Message- From: Andrei Gaspar [mailto:[EMAIL PROTECTED] Sent: Monday, April 18, 2005 10:36 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Sort and index I thought that an index can be used for sorting. I'm a l

Re: [PERFORM] Sort and index

2005-04-18 Thread Dave Held
> -Original Message- > From: Andrei Gaspar [mailto:[EMAIL PROTECTED] > Sent: Monday, April 18, 2005 10:36 AM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Sort and index > > I thought that an index can be used for sorting. > I'm a little confus

[PERFORM] Sort and index

2005-04-18 Thread Andrei Gaspar
Hi, I thought that an index can be used for sorting. I'm a little confused about the following result: create index OperationsName on Operations(cOperationName); explain SELECT * FROM Operations ORDER BY cOperationName; QUERY PLAN ---