From: | Guillaume Cottenceau <gc(at)mnc(dot)ch> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL |
Date: | 2012-11-29 11:33:50 |
Message-ID: | m3mwy0myn5.fsf@mnc.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello,
I am toying around with 9.2.1, trying to measure/determine how
index-only scans can improve our performance.
A small script which is attached to this mail, shows that as long
as the table has been VACUUM FULL'd, there is a unusual high
amount of heap fetches. It is strange that the visibilitymap_test
predicate fails in these situations, is the visibility map
somehow trashed in this situation? It should not, or at least the
documentation[1] should state it (my understanding is that vacuum
full does *more* than vacuum, but nothing less) (note to usual
anti vacuum full trollers: I know you hate vacuum full).
Using pg 9.2.1 compiled from sources, almost standard
configuration except shared_buffers at 512M, effective_cache_size
at 1536M, random_page_cost at 2, and vacuum delays increased.
Please find complete logs attached, and selected logs below:
After table creation + analyze:
Index Only Scan using i on ta (cost=0.00..156991.10 rows=2018667 width=4) (actual time=0.034..336.443 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
After vacuum:
Index Only Scan using i on ta (cost=0.00..50882.62 rows=2018667 width=4) (actual time=0.014..193.120 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
After vacuum analyze:
Index Only Scan using i on ta (cost=0.00..50167.13 rows=1990353 width=4) (actual time=0.015..193.035 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
After vacuum full:
Index Only Scan using i on ta (cost=0.00..155991.44 rows=1990333 width=4) (actual time=0.042..364.412 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
^^^^^^^ uh uh, looking bad
After vacuum full analyze:
Index Only Scan using i on ta (cost=0.00..157011.85 rows=2030984 width=4) (actual time=0.025..365.657 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 2000000
After vacuum:
Index Only Scan using i on ta (cost=0.00..51192.45 rows=2031000 width=4) (actual time=0.015..192.520 rows=2000000 loops=1)
Index Cond: (ca = 1)
Heap Fetches: 0
Thanks for any comments/hints,
Ref:
[1] http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
Attachment | Content-Type | Size |
---|---|---|
vacfull.sql | application/octet-stream | 715 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavan Deolasee | 2012-11-29 11:50:01 | Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL |
Previous Message | Mark Kirkwood | 2012-11-29 04:30:52 | Re: Optimize update query |