Re: Unexpected speed PLAIN vs. MAIN

On Mon, May 04, 2015 at 01:50:45PM -0400, Tom Lane wrote:
> Sandro Santilli <strk@keybit.net> writes:
> > I'm comparing speed of some queries against tables having the same data
> > but different storage, and got an unexpected behavior.
> 
> > The tables have 2 integer fields and a PcPatch field 
> > ("p", custom type from pgPointCloud).
> 
> > There are no TOASTs involved (the toast table associated with the table
> > with MAIN storage is empty, the table with PLAIN storage has no toast table).
> 
> > Running a SELECT count(p) takes 6261.699 ms on the table with MAIN storage
> > and 18488.713 ms on the table with PLAIN storage.
> 
> > The number of buffer reads are about the same.
> > Why would reading presence/absence of a value be faster from MAIN than
> > from PLAIN storage ?
> 
> Hm ... MAIN allows in-line compression while PLAIN doesn't.  But for
> count(), that would only make a difference if it resulted in a smaller
> physical table size, which it evidently didn't.
> 
> My best guess is that the OS had many of the pages from rtlidar_dim_main
> sitting in OS disk cache, so that those "buffer reads" didn't all
> translate to physical I/O.  Try flushing the OS cache immediately before
> each trial to get more-reproducible results.

Bingo, it was the OS disk cache. Thanks for the tip !

That cache (Linux) acts in mysterious ways, btw.

After a new boot, with no explicit flushing, I obtained slow times in both
tables (~18 secs) with queries in this order: PLAIN,MAIN,PLAIN,MAIN.
Then 3 queries in a row against MAIN brought down its timing to 2,
but after that no number of consecutive queries against PLAIN could
do that. It took a disk flush (echo 3  >  /proc/sys/vm/drop_caches;
sync was not enough) to get the 18 seconds back on reading MAIN and
allowing me to force caching PLAIN via consecutive calls...

I'll play a bit with pgfincore to learn more.
(http://git.postgresql.org/gitweb/?p=pgfincore.git;a=summary)

--strk;