Date: Fri, 22 Apr 2005 22:00:02 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dave Held <dave.held@arrayservicesgrp.com>,
	pgsql-performance@postgresql.org
Subject: Re: Sort and index
Message-ID: <20050423030002.GY58835@decibel.org>
References: <49E94D0CFCD4DB43AFBA928DDD20C8F9026184B6@asg002.asg.local>
	<20050420004234.GX58835@decibel.org>
	<19631.1113966086@sss.pgh.pa.us>
	<20050420034041.GC58835@decibel.org>
	<20050423015404.GV58835@decibel.org>
	<4269.1114222086@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4269.1114222086@sss.pgh.pa.us>
User-Agent: Mutt/1.5.8i

On Fri, Apr 22, 2005 at 10:08:06PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel@decibel.org> 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 than a sequential scan and a
> > sort.
> 
> ... for one test case, on one platform, with a pretty strong bias to the
> fully-cached state since you ran the test multiple times consecutively.

The table is 6.5G and the box only has 4G, so I suspect it's not cached.

> Past experience has generally been that an explicit sort is quicker,
> so you'll have to pardon me for suspecting that this case may be
> atypical.  Is the table nearly in order by pkey, by any chance?

It might be, but there's no way I can check with a multi-key index,
right?

I'll re-run the tests with a single column index on a column with a
correlation of 16%

> > In any case, it's clear that the planner is making the wrong choice
> > here. BTW, changing random_page_cost to 3 or 4 doesn't change the plan.
> 
> Feel free to propose better cost equations.

Where would I look in code to see what's used now?
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"