Hm, for something I'm trying at the moment on 9.4, I created two identical tables where the second one inherits all from the first:
CREATE UNLOGGED TABLE everything.slow
(
id integer NOT NULL,
value real
)
WITH (
OIDS=FALSE
)
CREATE UNLOGGED TABLE everything.fast
(
)
INHERITS (everything.slow)
WITH (
OIDS=FALSE
)
No indexes. Default tablespace.
If I then load 50 million records into each of those tables and query them individually using the ONLY restrictor, a count(*) on the parent table (slow) is slower than on the descendant (fast):
select count(*) from only everything.slow;
"Aggregate (cost=1067783.10..1067783.11 rows=1 width=0) (actual time=4973.812..4973.813 rows=1 loops=1)"
" Output: count(*)"
" Buffers: shared read=442478"
" -> Seq Scan on everything.slow (cost=0.00..942722.08 rows=50024408 width=0) (actual time=1012.708..3416.349 rows=50000000 loops=1)"
" Output: id, value"
" Buffers: shared read=442478"
"Planning time: 0.118 ms"
"Execution time: 4973.901 ms"
select count(*) from only everything.fast;
"Aggregate (cost=846239.00..846239.01 rows=1 width=0) (actual time=3988.235..3988.235 rows=1 loops=1)"
" Output: count(*)"
" Buffers: shared read=221239"
" -> Seq Scan on everything.fast (cost=0.00..721239.00 rows=50000000 width=0) (actual time=0.101..2403.813 rows=50000000 loops=1)"
" Output: id, value"
" Buffers: shared read=221239"
"Planning time: 0.086 ms"
"Execution time: 3988.302 ms"
This works with other aggregates like avg() too.
I had expected some overhead when querying without ONLY on slow, because of the traversal of the inheritance hierarchy, but not when I restrict the query to a specific table with ONLY...
Can someone explain this? UPDATE: Maybe I found it myself. See next post for my explanation.
No comments:
Post a Comment