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.
regards, tom lane
