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

SELECT max() won't use a NOT NULL index

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: SELECT max() won't use a NOT NULL index
Date: 2006-02-06 23:54:05
Message-ID: 20060206235405.GR1240@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-bugs
If you have a large table with a lot of nulls in a field then max() will
perform poorly, presumably because it will backward-scan past all the
nulls. The same is true of the old max hack (SELECT ... WHERE field IS
NOT NULL ORDER BY field DESC LIMIT 1). However, if you

CREATE INDEX indexname ON table(field) WHERE field IS NOT NULL

the hack will use that index, but max() won't.  ISTM that's a bug.

Also, isn't there some way to expedite the backwards scan in this case?
If I add a constraint such as field < 1000, it seems that the database
will go directly to wherever it needs to start the reverse scan, but it
can't do this with IS NOT NULL. Is this due to NOT NULL not being an
operator?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-bugs by date

Next:From: James William PyeDate: 2006-02-07 01:22:51
Subject: Re: BUG #2240: length() with geometric types
Previous:From: Michael MeskesDate: 2006-02-06 20:33:16
Subject: Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql

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