Re: Unexpected speed PLAIN vs. MAIN

Sandro Santilli <strk(at)keybit(dot)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.

regards, tom lane