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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
