Friday, February 20, 2015

Counting rows again - inheritance strangeness solved

Taking a second look at the execution plans, I've noticed that the scan on slow read twice the number of pages from disk than the one on fast:

Buffers: shared read=442478 vs. Buffers: shared read=221239

Since I loaded all rows into slow first and then moved 50% of them into fast, this makes sense, I guess.
If I understand it correctly, those pages in slow are now empty, but PostgreSQL keeps them for future use.

So I tried a VACUUM FULL on slow and ran my queries again. That changed the plans:

Buffers: shared read=221239 vs. Buffers: shared read=221239

And execution times are now about equal. The observed effect had nothing to do with table inheritance at all.

So VACUUM FULL can help, when moving big chunks of data between tables with otherwise low write activity.

1 comment:

  1. Hi, small note, PG uses mvcc, long story short it means that each time you change a row, update or delete, it marks the current row with the last transaction that can access it and creates a new row.
    so, if you move 50% of your rows you significantly increased your table size.
    (note that the data is needed until all old transactions finish so they will have snapshot consistency)

    Vacuuming removes rows which are not needed - so you are back to the original sizes.

    - Jony