pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Slow update of indexed column with many nulls
2+ messages / 2 participants
[nested] [flat]

* Slow update of indexed column with many nulls
@ 2003-03-23 17:38  bsamwel@xs4all.nl
  0 siblings, 1 reply; 2+ messages in thread

From: bsamwel@xs4all.nl @ 2003-03-23 17:38 UTC (permalink / raw)
  To: pgsql-performance

Hi everybody,

I'm having a performance problem, PostgreSQL (7.3.2) is skipping some
optimisation options that it shouldn't IMO. It can be fully reproduced as
follows:

create table foo(
bar char(100),
baz integer
);

Now create a file with 1.2 million empty lines and do a \copy foo (bar)
from 'thatfile'. This should fill the table with 1.2 million rows. Now do:

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:

bsamwel=> explain update foo set baz=null where baz is not null;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..34470.09 rows=1286146 width=110)
   Filter: (baz IS NOT NULL)
(2 rows)


So, it thinks it must do a sequential scan on foo, even though it should
know by now that foo.baz is really mostly null. Even if I disable
sequential scan it still chooses this option! Why doesn't it use the
index? It doesn't use the index either when I try to select all rows that
are not null.

Just for completeness' sake I'll give you the explain analyze:

bsamwel=> explain analyze update foo set baz=null where baz is not null;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..34470.09 rows=1286146 width=110) (actual
time=19678.82..19678.84 rows=1 loops=1)
   Filter: (baz IS NOT NULL)
 Total runtime: 19750.21 msec
(3 rows)

Do you guys have any idea?

Regards,
Bart




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Slow update of indexed column with many nulls
@ 2003-03-23 21:55  Josh Berkus <josh@agliodbs.com>
  parent: bsamwel@xs4all.nl
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2003-03-23 21:55 UTC (permalink / raw)
  To: bsamwel@xs4all.nl; 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-03-23 21:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-23 17:38 Slow update of indexed column with many nulls bsamwel@xs4all.nl
2003-03-23 21:55 ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox