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

Re: Constraint exclusion oddity with composite index

From: "Zeugswetter Andreas ADI SD" <ZeugswetterA(at)spardat(dot)at>
To: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>,"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-04 10:41:44
Message-ID: E1539E0ED7043848906A8FF995BDA579021B3001@m0143.s-mxs.net (view raw or flat)
Thread:
Lists: pgsql-hackers
> >> Assume the following:
> >> index on: (id, adate)
> >> constraint CHECK(adate > '01-01-2007' AND adate < '04-01-2007');
> >

Um, the subject is CE, but the question is about an index ? Those are
separate issues.
 
> >> The planner will not use the index listed above.

> > For what?
> 
> select adate from parent where adate = '01-25-2007'

A possibly cheaper plan would be a self join to produce all possible
id's and join the index for each (id, adate) pair.
Note, that you need not check visibility of the id's you produce (index
only access). 
Is that what you were expecting ? This is not implemented.

Andreas

In response to

pgsql-hackers by date

Next:From: Teodor SigaevDate: 2007-06-04 12:06:15
Subject: Re: Tsearch vs Snowball, or what's a source file?
Previous:From: Zdenek KotalaDate: 2007-06-04 10:14:27
Subject: Re: Command tags in create/drop scripts

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