From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Sandro Santilli <strk(at)keybit(dot)net> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Unexpected speed PLAIN vs. MAIN |
Date: | 2015-05-04 17:50:45 |
Message-ID: | 13555.1430761845@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2015-05-04 18:04:05 | Re: BUG in XLogRecordAssemble |
Previous Message | Fabrízio de Royes Mello | 2015-05-04 17:33:16 | Re: psql :: support for \ev viewname and \sv viewname |