pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Brown <time@bigpond.net.au>
To: Josh Berkus <pgsql-performance@postgresql.org>
Subject: Re: multi-column index
Date: Thu, 17 Mar 2005 10:25:43 +1000
Message-ID: <4238CE87.1040904@bigpond.net.au> (raw)
In-Reply-To: <200503161009.24713.josh@agliodbs.com>
References: <42385A1B.9090305@ibcp.fr>
	<200503161009.24713.josh@agliodbs.com>

Whoa Josh! I don't believe you're going to reduce the cost by 10 times 
through a bit of tweaking - not without lowering the sequential scan 
cost as well.

The only thing I can think of is perhaps his primary index drastically 
needs repacking. Otherwise, isn't there a real anomaly here? Halving the 
key width might account for some of it, but it's still miles out of court.

Actually, I'm surprised the planner came up with such a low cost for the 
single column index, unless ... perhaps correlation statistics aren't 
used when determining costs for multi-column indexes?

Josh Berkus wrote:

>Pretty simple, really.  Look at the cost calculations for the index scan for 
>the multi-column index.    PostgreSQL believes that:
>The cost of a seq scan is 4788.14
>The cost of an 2-column index scan is 36720.39
>The cost of a 1-column index scan is 916.24
>
>Assuming that you ran each of these queries multiple times to eliminate 
>caching as a factor, the issue is that the cost calculations are wrong.   We 
>give you a number of GUC variables to change that:
>effective_cache_size
>random_page_cost
>cpu_tuple_cost
>etc.
>
>See the RUNTIME-CONFIGURATION docs for more details.
>



view thread (10+ messages)  latest in thread

Message-ID: <4238CE87.1040904@bigpond.net.au>
Permalink:  ../4238CE87.1040904@bigpond.net.au/
Also on:    postgresql.org/message-id/4238CE87.1040904@bigpond.net.au

 · 

reply

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: time@bigpond.net.au
  Subject: Re: multi-column index
  In-Reply-To: <4238CE87.1040904@bigpond.net.au>

* 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