From jan.kesten@web.de Fri Mar 31 09:16:51 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5B15B11F6205 for ; Fri, 31 Mar 2006 05:16:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47945-07 for ; Fri, 31 Mar 2006 05:16:50 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id E015911F61CF for ; Fri, 31 Mar 2006 05:16:48 -0400 (AST) Received: from h6608.serverkompetenz.net (gpg-keyserver.de [81.169.179.29]) by svr4.postgresql.org (Postfix) with ESMTP id 231245AF1AC for ; Fri, 31 Mar 2006 09:16:49 +0000 (GMT) Received: from dslc-082-082-101-080.pools.arcor-ip.net ([82.82.101.80] helo=[192.168.0.60]) by h6608.serverkompetenz.net with esmtpa (Exim 4.60) (envelope-from ) id 1FPFkH-0007Eq-Tk for pgsql-performance@postgresql.org; Fri, 31 Mar 2006 11:16:43 +0200 Message-ID: <442CF375.2080903@web.de> Date: Fri, 31 Mar 2006 11:16:37 +0200 From: Jan Kesten User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: index not used again X-Enigmail-Version: 0.94.0.0 OpenPGP: id=82201FC4; url=http://gpg-keyserver.de Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigD34F435BA9B3A6C9DBE8E7AB" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200603/636 X-Sequence-Number: 18073 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 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
_idx_token on
using (token) where token=3D1; After that I run vacuum analyse to update statistics and changed my to: update
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-- From sszabo@megazone.bigpanda.com Fri Mar 31 14:44:46 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D395A11F6782 for ; Fri, 31 Mar 2006 10:44:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33195-07 for ; Fri, 31 Mar 2006 10:44:46 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id 9F16711F6773 for ; Fri, 31 Mar 2006 10:44:44 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id CA98034DEF; Fri, 31 Mar 2006 06:44:44 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id C894634DBC; Fri, 31 Mar 2006 06:44:44 -0800 (PST) Date: Fri, 31 Mar 2006 06:44:44 -0800 (PST) From: Stephan Szabo To: Jan Kesten Cc: pgsql-performance@postgresql.org Subject: Re: index not used again In-Reply-To: <442CF375.2080903@web.de> Message-ID: <20060331064252.J61282@megazone.bigpanda.com> References: <442CF375.2080903@web.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.138 required=5 tests=[AWL=0.138] X-Spam-Score: 0.138 X-Spam-Level: X-Archive-Number: 200603/637 X-Sequence-Number: 18074 On Fri, 31 Mar 2006, Jan Kesten wrote: > > 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
set token=0; > > Okay, this uses seq scan of course. For speeding things up, I created an > partial index on every table like this: > > create index
_idx_token on
using (token) where token=1; > > After that I run vacuum analyse to update statistics and changed my to: > > update
set token=0 where token=1; > > I think this should be able to use my index, and indeed on one table > this works quite fine: > > transfer=> explain analyse update ku set token=0 where token=1; > > QUERY PLAN > ------------------------------------------------------------------------ > Index Scan using ku_idx_token on ku (cost=0.00..1.01 rows=1 > width=1871) (actual time=0.169..0.169 rows=0 loops=1) > Index Cond: (token = 1) > Total runtime: 3.816 ms > (3 rows) > > But on most of the other tables a seq scan is still used: > > transfer=> explain analyse update fak6 set token=0 where token=1; > > QUERY PLAN > ------------------------------------------------------------------------ > Seq Scan on fak6 (cost=0.00..301618.71 rows=24217 width=1895) (actual > time=96987.417..127020.919 rows=24251 loops=1) > Filter: (token = 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=> set enable_seqscan to off; > SET > transfer=> explain analyse update fak6 set token=0 where token=1; > > QUERY PLAN > ------------------------------------------------------------------------ > Index Scan using fak6_idx_token on fak6 (cost=0.00..301697.93 > rows=24217 width=1895) (actual time=1271.273..1271.273 rows=0 loops=1) > Index Cond: (token = 1) > Total runtime: 1272.572 ms > (3 rows) Did you reset the table contents between these two (remember that explain analyze actually runs the query)? The second appears to be changing no rows from the output. From jan.kesten@web.de Sun Apr 2 16:20:26 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7353E11F709E for ; Sun, 2 Apr 2006 13:20:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96930-01 for ; Sun, 2 Apr 2006 13:20:24 -0300 (ADT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 762B411F6F4C for ; Sun, 2 Apr 2006 13:20:24 -0300 (ADT) Received: from h6608.serverkompetenz.net (h6608.serverkompetenz.net [81.169.179.29]) by svr4.postgresql.org (Postfix) with ESMTP id 12F5A5AF8A8 for ; Sun, 2 Apr 2006 16:20:23 +0000 (GMT) Received: from dslc-082-082-104-253.pools.arcor-ip.net ([82.82.104.253] helo=[192.168.0.60]) by h6608.serverkompetenz.net with esmtpa (Exim 4.60) (envelope-from ) id 1FQ5JL-0001HK-Ni for pgsql-performance@postgresql.org; Sun, 02 Apr 2006 18:20:20 +0200 Message-ID: <442FF9C0.2070702@web.de> Date: Sun, 02 Apr 2006 18:20:16 +0200 From: Jan Kesten User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: index not used again References: <442CF375.2080903@web.de> <20060331064252.J61282@megazone.bigpanda.com> In-Reply-To: <20060331064252.J61282@megazone.bigpanda.com> X-Enigmail-Version: 0.94.0.0 OpenPGP: id=82201FC4; url=http://gpg-keyserver.de Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig73D473C4F4EAA40FB404890A" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200604/14 X-Sequence-Number: 18098 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig73D473C4F4EAA40FB404890A Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Stephan Szabo schrieb: > Did you reset the table contents between these two (remember that > explain analyze actually runs the query)? The second appears to be > changing no rows from the output. I for myself did not, but as there are runnig automatic jobs periodically I can't tell, if one ran in the time while I was testing (but I guess not). At starting my tests all rows contained a zero for all tokens and there should be no ones at all. In my case rows with token set to one are really rare, about one of a thousand rows. I looked for fast way to find therse rows. I'll try again after a successful run - not resetting the token (not using analyse this time). Cheers, Jan --------------enig73D473C4F4EAA40FB404890A 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 iD8DBQFEL/nCvvmCkIIgH8QRAn5kAKC2UqgyBKZeH19Ot8htLrJA4kwingCeLTgR y+PvfErLkni+B6kW5TZ7PeQ= =JTgs -----END PGP SIGNATURE----- --------------enig73D473C4F4EAA40FB404890A-- From sszabo@megazone.bigpanda.com Sun Apr 2 19:46:59 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 66A4111F7DAE for ; Sun, 2 Apr 2006 16:46:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27534-03 for ; Sun, 2 Apr 2006 16:46:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id 7F7DC11F7D8A for ; Sun, 2 Apr 2006 16:46:56 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id E02AA350AD; Sun, 2 Apr 2006 12:46:55 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id DB005350A3; Sun, 2 Apr 2006 12:46:55 -0700 (PDT) Date: Sun, 2 Apr 2006 12:46:55 -0700 (PDT) From: Stephan Szabo To: Jan Kesten Cc: pgsql-performance@postgresql.org Subject: Re: index not used again In-Reply-To: <442FF9C0.2070702@web.de> Message-ID: <20060402124047.X46838@megazone.bigpanda.com> References: <442CF375.2080903@web.de> <20060331064252.J61282@megazone.bigpanda.com> <442FF9C0.2070702@web.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.137 required=5 tests=[AWL=0.137] X-Spam-Score: 0.137 X-Spam-Level: X-Archive-Number: 200604/15 X-Sequence-Number: 18099 On Sun, 2 Apr 2006, Jan Kesten wrote: > Stephan Szabo schrieb: > > > Did you reset the table contents between these two (remember that > > explain analyze actually runs the query)? The second appears to be > > changing no rows from the output. > > I for myself did not, but as there are runnig automatic jobs > periodically I can't tell, if one ran in the time while I was testing > (but I guess not). At starting my tests all rows contained a zero for > all tokens and there should be no ones at all. The reason I asked is that the explain analyze output for the first query on fak6 (using a seqscan) seemed to imply 24k rows actually matched the condition and were updated, so comparisons to the later times may be skewed.