Executing vacuuming on a postgres table

Asked Aug 01 '17 at 12:24

Active Aug 01 '17 at 12:24

Viewed 68 times

I had speed issues displaying a layer in Geoserver. The layer is retrieved through a postgres store. I have run auto-vacuum on the table (16million rows) and then things being faster. The issue is that I don't know why they are faster. Based on the outcome of the vacuum query:

INFO:  vacuuming "osm.asp_trs_roads_osm"
INFO:  "asp_trs_roads_osm": found 366524 removable, 9882216 nonremovable row versions in 425556 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 8.42s/13.92u sec elapsed 783.24 sec.
Query returned successfully with no result in 1285638 ms.

There were 0 dead rows. So if no change was applied why everything starts working fast after I execute the query?

asked Aug 01 '17 at 12:24

user1919

3,818
17
62
97

**0 dead row versions cannot be removed yet.** - they all are removable. run the command again and compare the result – Vao Tsun Aug 01 '17 at 12:26
@ Vao Tsun it returns the same thing. – user1919 Aug 01 '17 at 12:38
what command you run then?.. `vacuum (analyze verbose)`?.. – Vao Tsun Aug 01 '17 at 12:42
This one: VACUUM FULL FREEZE VERBOSE schema.table_name – user1919 Aug 01 '17 at 12:43
so the very first vacuum gace same result and dramatically speeded up queries?. – Vao Tsun Aug 01 '17 at 12:46
Yes. Does this make any sense? Shouldn't it be that in the second query the removable rows are 0? – user1919 Aug 01 '17 at 12:49
well, if no transaction need older versions, yes, I'd expect them to be removed by `vacuum full` – Vao Tsun Aug 01 '17 at 12:55
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150724/discussion-between-user1919-and-vao-tsun). – user1919 Aug 01 '17 at 12:56

Executing vacuuming on a postgres table

0 Answers0