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=1width=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=1194948loops=1) Output: id, source, pa Buffers: shared hit=32 read=1187659Total runtime: 6261.699
ms
=# explain (analyze, verbose, buffers) select count(pa) from rtlidar_dim_plain;Aggregate (cost=1202627.85..1202627.86
rows=1width=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.974rows=1194948 loops=1) Output: id, source, pa Buffers: shared hit=37
read=1187654Totalruntime: 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;