From: Jim C. Nasby <decibel@decibel.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dave Held <dave.held@arrayservicesgrp.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sort and index
Date: Fri, 22 Apr 2005 22:00:02 -0500
Message-ID: <20050423030002.GY58835@decibel.org> (raw)
In-Reply-To: <4269.1114222086@sss.pgh.pa.us>
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>
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?"
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: decibel@decibel.org, tgl@sss.pgh.pa.us, dave.held@arrayservicesgrp.com
Subject: Re: Sort and index
In-Reply-To: <20050423030002.GY58835@decibel.org>
* 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