Обсуждение: Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se lect)
Re: Selectivity of "=" (Re: [HACKERS] Index not used on simple se lect)
От
Zeugswetter Andreas IZ5
Дата:
> Yes, I think we index nulls. What are partial indexes? > A create index statement that accepts a where condition. All rows that satisfy the where condition are indexed, others not. This needs intelligence in the optimizer. This was in postgresql code some time ago, but was removed for some reason I don't remember. Example: create index ax0 on a (id) where id is not null; Andreas
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at> writes: >> Yes, I think we index nulls. What are partial indexes? >> > A create index statement that accepts a where condition. All > rows that satisfy the where condition are indexed, others not. > This needs intelligence in the optimizer. > This was in postgresql code some time ago, but was removed > for some reason I don't remember. It was? There's still a ton of code in the optimizer to support it (a big chunk of indxqual.c is for testing index predicates). regards, tom lane
The code for partial indices is still intact in RTREES, and there is some information about them in one of the Stonebraker papers. If anyone is intersted I will dig up my file and look for an exact reference. Bernie
> > This was in postgresql code some time ago, but was removed > > for some reason I don't remember. > It was? There's still a ton of code in the optimizer to support it > (a big chunk of indxqual.c is for testing index predicates). There was talk of removing it, but it seemed to be a Bad Idea to do so. The discussion even provoked a negative response from the Gods themselves (in the voice of Paul Aoki) and led to the short description of them in the docs. They have definitely been neglected, but are a Good Thing and should be rehabbed... - Thomas -- Thomas Lockhart lockhart@alumni.caltech.edu South Pasadena, California