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

Re: Slow update of indexed column with many nulls

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: bsamwel(at)xs4all(dot)nl, pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow update of indexed column with many nulls
Date: 2003-03-23 21:55:02
Message-ID: 200303231355.02826.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Bart,

> insert into foo (baz) values (28);
> create index foo_idx on foo(baz);
> vacuum full analyze foo;
>
> Now, we would expect that PostgreSQL is fully aware that there are not
> many rows in foo that have "baz is not null". However:

This is a known issue discussed several times on this list.   Try re-creating 
your index as:

create index foo_idx on foo(baz) where foo is not null;

See the list archives for the reasons why.  This may improve in future 
releases of PostgreSQL.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


In response to

pgsql-performance by date

Next:From: Oleg LebedevDate: 2003-03-24 17:48:52
Subject: Slow query
Previous:From: Stephan SzaboDate: 2003-03-23 19:30:04
Subject: Re: Adding a foreign key constraint is extremely slow

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