From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim C. Nasby <decibel@decibel.org>
Cc: Dave Held <dave.held@arrayservicesgrp.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sort and index
Date: Fri, 22 Apr 2005 22:08:06 -0400
Message-ID: <4269.1114222086@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, decibel@decibel.org, dave.held@arrayservicesgrp.com
Subject: Re: Sort and index
In-Reply-To: <4269.1114222086@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox