Skip site navigation (1) Skip section navigation (2)

Re: Poor index choice -- multiple indexes of the same columns

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor index choice -- multiple indexes of the same columns
Date: 2005-06-27 22:37:41
Message-ID: 200506271537.41797.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Karl,

> Seems to me that when there's a constant value in the query
> and an = comparision it will always be faster to use the (b-tree)
> index that's ordered first by the constant value, as then all further
> blocks are guarenteed to have a higher relevant information
> density.  At least when compared with another index that has the
> same columns in it.

That really depends on the stats.   Such a choice would *not* be 
appropriate if the < comparison was expected to return 1- rows while the = 
condition applied to 15% of the table.

What is your STATISTICS_TARGET for the relevant columns set to?   When's 
the last time you ran analyze?  If this is all updated, you want to post 
the pg_stats rows for the relevant columns?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-performance by date

Next:From: Jacques CaronDate: 2005-06-27 22:40:53
Subject: Re: Forcing use of a particular index
Previous:From: Vivek KheraDate: 2005-06-27 21:11:47
Subject: Re: perl garbage collector

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group