Message-ID: <442CF375.2080903@web.de>
Date: Fri, 31 Mar 2006 11:16:37 +0200
From: Jan Kesten <jan.kesten@web.de>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: index not used again
OpenPGP: id=82201FC4;
	url=http://gpg-keyserver.de
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigD34F435BA9B3A6C9DBE8E7AB"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigD34F435BA9B3A6C9DBE8E7AB
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable


Hi folks!

I have just a issue again with unused indexes. I have a database with a
couple of tables and I have to do an sync job with them. For marking
which row has to be transfered I added a new column token (integer, I
will need some more tokens in near future) to every table.

Before determining wich rows to mark I first had a simple

update <table> set token=3D0;

Okay, this uses seq scan of course. For speeding things up, I created an
partial index on every table like this:

create index <table>_idx_token on <table> using (token) where token=3D1;

After that I run vacuum analyse to update statistics and changed my to:

update <table> set token=3D0 where token=3D1;

I think this should be able to use my index, and indeed on one table
this works quite fine:

transfer=3D> explain analyse update ku set token=3D0 where token=3D1;

QUERY PLAN
------------------------------------------------------------------------
 Index Scan using ku_idx_token on ku  (cost=3D0.00..1.01 rows=3D1
width=3D1871) (actual time=3D0.169..0.169 rows=3D0 loops=3D1)
   Index Cond: (token =3D 1)
 Total runtime: 3.816 ms
(3 rows)

But on most of the other tables a seq scan is still used:

transfer=3D> explain analyse update fak6 set token=3D0 where token=3D1;

QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on fak6  (cost=3D0.00..301618.71 rows=3D24217 width=3D1895) (ac=
tual
time=3D96987.417..127020.919 rows=3D24251 loops=3D1)
   Filter: (token =3D 1)
 Total runtime: 181828.281 ms
(3 rows)

So I tried to force using an index with setting enable_seqscan to off,
here are the results:

transfer=3D> set enable_seqscan to off;
SET
transfer=3D> explain analyse update fak6 set token=3D0 where token=3D1;

QUERY PLAN
------------------------------------------------------------------------
 Index Scan using fak6_idx_token on fak6  (cost=3D0.00..301697.93
rows=3D24217 width=3D1895) (actual time=3D1271.273..1271.273 rows=3D0 loo=
ps=3D1)
   Index Cond: (token =3D 1)
 Total runtime: 1272.572 ms
(3 rows)

transfer=3D> set enable_seqscan to on;
SET
transfer=3D> explain analyse update fak6 set token=3D0 where token=3D1;

QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on fak6  (cost=3D0.00..301618.71 rows=3D24217 width=3D1895) (ac=
tual
time=3D93903.379..93903.379 rows=3D0 loops=3D1)
   Filter: (token =3D 1)
 Total runtime: 93904.679 ms
(3 rows)

transfer=3D> set enable_seqscan to off;
SET
transfer=3D> explain analyse update fak6 set token=3D0 where token=3D1;

QUERY PLAN
------------------------------------------------------------------------
 Index Scan using fak6_idx_token on fak6  (cost=3D0.00..301697.93
rows=3D24217 width=3D1895) (actual time=3D223.721..223.721 rows=3D0 loops=
=3D1)
   Index Cond: (token =3D 1)
 Total runtime: 226.851 ms
(3 rows)

Now I'm a bit confused. The costs are nearly the same if using index or
not - but runtime is about 70 times faster? Any idea how I can fix this
issue - I thought a partial index would be the right way?

Cheers,
Jan


--------------enigD34F435BA9B3A6C9DBE8E7AB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3-cvs (MingW32)
Comment: GnuPT 2.7.6
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFELPN1vvmCkIIgH8QRAi5iAJ4iLT7eor5ontIVcISpFAqBQuXKBQCeJ5uf
uK+kCjyTvd/SzgqjKDiOUBE=
=vZt/
-----END PGP SIGNATURE-----

--------------enigD34F435BA9B3A6C9DBE8E7AB--