Unexpected speed PLAIN vs. MAIN

From: Sandro Santilli <strk(at)keybit(dot)net>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Unexpected speed PLAIN vs. MAIN
Date: 2015-05-04 17:11:35
Message-ID: 20150504171135.GA26279@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 ?

The explain output:

=# explain (analyze, verbose, buffers) select count(pa) from rtlidar_dim_main;
Aggregate (cost=1202627.85..1202627.86 rows=1 width=32) (actual time=6261.644..6261.644 rows=1 loops=1)
Output: count(pa)
Buffers: shared hit=32 read=1187659
-> Seq Scan on public.rtlidar_dim_main (cost=0.00..1199640.48 rows=1194948 width=32) (actual time=0.060..6105.566 rows=1194948 loops=1)
Output: id, source, pa
Buffers: shared hit=32 read=1187659
Total runtime: 6261.699 ms

=# explain (analyze, verbose, buffers) select count(pa) from rtlidar_dim_plain;
Aggregate (cost=1202627.85..1202627.86 rows=1 width=32) (actual time=18473.973..18473.973 rows=1 loops=1)
Output: count(pa)
Buffers: shared hit=37 read=1187654
-> Seq Scan on public.rtlidar_dim_plain (cost=0.00..1199640.48 rows=1194948 width=32) (actual time=0.058..18247.974 rows=1194948 loops=1)
Output: id, source, pa
Buffers: shared hit=37 read=1187654
Total runtime: 18474.028 ms

The relation sizes:

=# select pg_total_relation_size('rtlidar_dim_plain');
9756426240

=# select pg_total_relation_size('rtlidar_dim_main');
9756434432

--strk;

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2015-05-04 17:16:58 Re: BUG in XLogRecordAssemble
Previous Message Zhang Zq 2015-05-04 16:04:09 BUG in XLogRecordAssemble