Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Sat, 2006-02-11 at 16:36 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I believe this is safe. I won't insult your intelligence by pointing out how I know that you didn't even test the patch against hash or gist. I don't recall either way, though from what you say it seems I did not test those cases. Thanks for catching my error. The major problem with the patch is that it's incapable of producing correct tuple-count stats for partial indexes, which is really not acceptable from a planning standpoint. What I'm currently fooling with is skipping the bulkdelete scan only if the index isn't partial... Thanks for spotting this case. I strive to learn. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Skipping VACUUM of indexes when no work required
Patch applied. Thanks.
---
Simon Riggs wrote:
On Wed, 2005-12-07 at 17:40 +, Simon Riggs wrote:
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
We discussed an optimization of VACUUM here
http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php
that would allow VACUUM to complete faster by avoiding scanning the
indexes when no rows were removed from the heap by the VACUUM.
Unfortunately I can't read that message right now because archives
isn't responding, but this seems like a pretty bad idea to me.
You still have to do the vacuum cleanup pass (at least in the btree
case, and the only reason gist doesn't need it is it's not yet up
to speed) so there's no real savings.
There are real savings; this is not a theoretical patch.
One pass of an index is faster than two, always.
Test results on a 1.2GB table, 10^6 rows and 3 indexes:
w/o optimization 87s
with optimization 56s
Timings taken with primed cache, to allow reasonable comparison without
confusing the issue with hint bit updates etc.
Performance gain is dependant upon:
1. size of index
2. logical/physical ordering of index pages
These tests performed immediately after load, which is best case, but
also the main case for which I seek to optimize.
postgres=# select pg_relation_size('vactest');
pg_relation_size
--
1204707328
vacuum verbose vactest;
psql:vacnout1.sql:3: INFO: vacuuming public.vactest
psql:vacnout1.sql:3: INFO: index vactest_idx1 now contains 1000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.25s/0.03u sec elapsed 5.81 sec.
psql:vacnout1.sql:3: INFO: index vactest_idx2 now contains 1000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.26s/0.04u sec elapsed 5.78 sec.
psql:vacnout1.sql:3: INFO: index vactest_idx3 now contains 1000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.23s/0.05u sec elapsed 5.69 sec.
psql:vacnout1.sql:3: INFO: vactest: found 0 removable, 1000
nonremovable row versions in 147059 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2 unused item pointers.
0 pages are entirely empty.
CPU 2.59s/0.58u sec elapsed 56.02 sec.
psql:vacnout1.sql:3: INFO: vacuuming pg_toast.pg_toast_16415
psql:vacnout1.sql:3: INFO: index pg_toast_16415_index now contains 0
row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:vacnout1.sql:3: INFO: pg_toast_16415: found 0 removable, 0
nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
update vactest set col1a =1 where col1a = 1 and col1b = 1 and col1c = 1;
UPDATE 1
vacuum verbose vactest;
psql:vacnout1.sql:6: INFO: vacuuming public.vactest
psql:vacnout1.sql:6: INFO: index vactest_idx1 now contains 1000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.94s/0.45u sec elapsed 15.29 sec.
psql:vacnout1.sql:6: INFO: index vactest_idx2 now contains 1000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.03s/0.40u sec elapsed 16.80 sec.
psql:vacnout1.sql:6: INFO: index vactest_idx3 now contains 1000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.94s/0.49u sec elapsed 15.84 sec.
psql:vacnout1.sql:6: INFO: vactest: removed 1 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.02 sec.
psql:vacnout1.sql:6: INFO: vactest: found 1 removable, 1000
nonremovable row versions in 147059 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 1 unused item pointers.
0 pages are entirely empty.
CPU 4.99s/1.85u sec elapsed 87.20 sec.
psql:vacnout1.sql:6: INFO: vacuuming pg_toast.pg_toast_16415
psql:vacnout1.sql:6: INFO: index pg_toast_16415_index now contains 0
row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:vacnout1.sql:6: INFO: pg_toast_16415: found 0 removable, 0
nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Other details available.
Best Regards, Simon Riggs
---(end of
Re: [PATCHES] Skipping VACUUM of indexes when no work required
Bruce Momjian pgman@candle.pha.pa.us writes: Patch applied. Thanks. Please revert. You cannot skip scanning indexes simply because there was no heap activity. btree for instance does post-cleanup on the next vacuum. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Skipping VACUUM of indexes when no work required
Simon Riggs [EMAIL PROTECTED] writes: We discussed an optimization of VACUUM here http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php that would allow VACUUM to complete faster by avoiding scanning the indexes when no rows were removed from the heap by the VACUUM. After looking at this, I think it is salvageable, but the patch as written complicates the vacuum-to-index-AM API more than necessary; there's no reason why the AM has to expose the fact that it skipped doing anything. I'll clean it up and reapply. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Sat, 2006-02-11 at 12:04 -0500, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Patch applied. Thanks. Please revert. You cannot skip scanning indexes simply because there was no heap activity. btree for instance does post-cleanup on the next vacuum. The patch skips only the first scan, not the second phase which does the post-cleanup. We discussed this before and I listened... I believe this is safe. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Skipping VACUUM of indexes when no work required
Simon Riggs [EMAIL PROTECTED] writes: I believe this is safe. I won't insult your intelligence by pointing out how I know that you didn't even test the patch against hash or gist. The major problem with the patch is that it's incapable of producing correct tuple-count stats for partial indexes, which is really not acceptable from a planning standpoint. What I'm currently fooling with is skipping the bulkdelete scan only if the index isn't partial... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Skipping VACUUM of indexes when no work required
Here's the patch as-applied. Note one major difference from your original: the logic about whether an indexscan can be skipped is now entirely local to the index AMs, rather than allowing VACUUM to make assumptions that may not be warranted for particular AMs. For the same reason, the AM is still responsible for providing the tuple count statistic. regards, tom lane binGCkhzbMrzu.bin Description: bulkdelete.patch.gz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Skipping VACUUM of indexes when no work required
Simon Riggs [EMAIL PROTECTED] writes: We discussed an optimization of VACUUM here http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php that would allow VACUUM to complete faster by avoiding scanning the indexes when no rows were removed from the heap by the VACUUM. Unfortunately I can't read that message right now because archives isn't responding, but this seems like a pretty bad idea to me. You still have to do the vacuum cleanup pass (at least in the btree case, and the only reason gist doesn't need it is it's not yet up to speed) so there's no real savings. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We discussed an optimization of VACUUM here http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php that would allow VACUUM to complete faster by avoiding scanning the indexes when no rows were removed from the heap by the VACUUM. resolved. Unfortunately I can't read that message right now because archives isn't responding, but this seems like a pretty bad idea to me. You still have to do the vacuum cleanup pass (at least in the btree case, and the only reason gist doesn't need it is it's not yet up to speed) so there's no real savings. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl, ODBCng - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We discussed an optimization of VACUUM here http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php that would allow VACUUM to complete faster by avoiding scanning the indexes when no rows were removed from the heap by the VACUUM. Unfortunately I can't read that message right now because archives isn't responding, but this seems like a pretty bad idea to me. You still have to do the vacuum cleanup pass (at least in the btree case, and the only reason gist doesn't need it is it's not yet up to speed) so there's no real savings. There are real savings; this is not a theoretical patch. One pass of an index is faster than two, always. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Wed, 2005-12-07 at 17:40 +, Simon Riggs wrote:
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
We discussed an optimization of VACUUM here
http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php
that would allow VACUUM to complete faster by avoiding scanning the
indexes when no rows were removed from the heap by the VACUUM.
Unfortunately I can't read that message right now because archives
isn't responding, but this seems like a pretty bad idea to me.
You still have to do the vacuum cleanup pass (at least in the btree
case, and the only reason gist doesn't need it is it's not yet up
to speed) so there's no real savings.
There are real savings; this is not a theoretical patch.
One pass of an index is faster than two, always.
Test results on a 1.2GB table, 10^6 rows and 3 indexes:
w/o optimization87s
with optimization 56s
Timings taken with primed cache, to allow reasonable comparison without
confusing the issue with hint bit updates etc.
Performance gain is dependant upon:
1. size of index
2. logical/physical ordering of index pages
These tests performed immediately after load, which is best case, but
also the main case for which I seek to optimize.
postgres=# select pg_relation_size('vactest');
pg_relation_size
--
1204707328
vacuum verbose vactest;
psql:vacnout1.sql:3: INFO: vacuuming public.vactest
psql:vacnout1.sql:3: INFO: index vactest_idx1 now contains 1000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.25s/0.03u sec elapsed 5.81 sec.
psql:vacnout1.sql:3: INFO: index vactest_idx2 now contains 1000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.26s/0.04u sec elapsed 5.78 sec.
psql:vacnout1.sql:3: INFO: index vactest_idx3 now contains 1000
row versions in 21899 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.23s/0.05u sec elapsed 5.69 sec.
psql:vacnout1.sql:3: INFO: vactest: found 0 removable, 1000
nonremovable row versions in 147059 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 2 unused item pointers.
0 pages are entirely empty.
CPU 2.59s/0.58u sec elapsed 56.02 sec.
psql:vacnout1.sql:3: INFO: vacuuming pg_toast.pg_toast_16415
psql:vacnout1.sql:3: INFO: index pg_toast_16415_index now contains 0
row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:vacnout1.sql:3: INFO: pg_toast_16415: found 0 removable, 0
nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
update vactest set col1a =1 where col1a = 1 and col1b = 1 and col1c = 1;
UPDATE 1
vacuum verbose vactest;
psql:vacnout1.sql:6: INFO: vacuuming public.vactest
psql:vacnout1.sql:6: INFO: index vactest_idx1 now contains 1000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.94s/0.45u sec elapsed 15.29 sec.
psql:vacnout1.sql:6: INFO: index vactest_idx2 now contains 1000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.03s/0.40u sec elapsed 16.80 sec.
psql:vacnout1.sql:6: INFO: index vactest_idx3 now contains 1000
row versions in 21899 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.94s/0.49u sec elapsed 15.84 sec.
psql:vacnout1.sql:6: INFO: vactest: removed 1 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.02 sec.
psql:vacnout1.sql:6: INFO: vactest: found 1 removable, 1000
nonremovable row versions in 147059 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 1 unused item pointers.
0 pages are entirely empty.
CPU 4.99s/1.85u sec elapsed 87.20 sec.
psql:vacnout1.sql:6: INFO: vacuuming pg_toast.pg_toast_16415
psql:vacnout1.sql:6: INFO: index pg_toast_16415_index now contains 0
row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:vacnout1.sql:6: INFO: pg_toast_16415: found 0 removable, 0
nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Other details available.
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
