On Mon, May 04, 2015 at 01:50:45PM -0400, Tom Lane wrote: > Sandro Santilli <s...@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; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers