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

Re: Constraint exclusion oddity with composite index

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Constraint exclusion oddity with composite index
Date: 2007-06-01 21:55:22
Message-ID: 466095CA.9040205@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> Tom Lane wrote:
>>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>>>> Assume the following:
>>>> index on: (id, adate)
>>>> constraint CHECK(adate > '01-01-2007' AND adate < '04-01-2007');
>>>> The planner will not use the index listed above.
>>> For what?
> 
>> select adate from parent where adate = '01-25-2007'
> 
> That's unsurprising.  Searching with only a lower-order index column
> value seldom wins, 'cause you've got to scan the entire index.  The
> constraint is irrelevant to this.

I guess where I got confused is:

http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html

And explicitly:

A multicolumn B-tree index can be used with query conditions that 
involve any subset of the index's columns, but the index is most 
efficient when there are constraints on the leading (leftmost) columns.

Sincerely,

Joshua D. Drake


> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 


-- 

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


In response to

Responses

pgsql-hackers by date

Next:From: George PavlovDate: 2007-06-01 21:55:41
Subject: Re: query log corrupted-looking entries
Previous:From: Magnus HaganderDate: 2007-06-01 21:44:34
Subject: Re: syslogger line-end processing infelicity

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