X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C3A655405F
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 22 Apr 2005 23:08:57 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 50709-03
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat, 23 Apr 2005 02:08:47 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 185FB54053
	for <pgsql-performance@postgresql.org>;
	Fri, 22 Apr 2005 23:08:44 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3N286Zv004270;
	Fri, 22 Apr 2005 22:08:06 -0400 (EDT)
To: "Jim C. Nasby" <decibel@decibel.org>
Cc: Dave Held <dave.held@arrayservicesgrp.com>,
	pgsql-performance@postgresql.org
Subject: Re: Sort and index 
In-reply-to: <20050423015404.GV58835@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>
Comments: In-reply-to "Jim C. Nasby" <decibel@decibel.org>
	message dated "Fri, 22 Apr 2005 20:54:04 -0500"
Date: Fri, 22 Apr 2005 22:08:06 -0400
Message-ID: <4269.1114222086@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/630
X-Sequence-Number: 12027

"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.

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?

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

			regards, tom lane