Re: partitioned table set and indexes

> Rick Otten <rottenwindfish(at)gmail(dot)com> hat am 11. Dezember 2015 um 23:09
> geschrieben:

>
> The query performance hit for sequence scanning isn't all that terrible,
> but I'd rather understand and get rid of the issue if I can, now, before I
> run into it again in a situation where it is crippling.

i think, you should try to understand how the planner works.

a simple example:

test=# create table foo (id serial primary key, val text);
CREATE TABLE
test=*# insert into foo (val) select repeat(md5(1::text), 5);
INSERT 0 1
test=*# analyse foo;
ANALYZE
test=*# explain analyse select val from foo where id=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1.02 rows=1 width=164) (actual time=0.006..0.007
rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 1
Planning time: 0.118 ms
Execution time: 0.021 ms
(5 rows)

As you can see a seq-scan. It's a small table, costs ..1.02.

Adding one row:

test=*# insert into foo (val) select val from foo;
INSERT 0 1
test=*# analyse foo;
ANALYZE
test=*# explain analyse select val from foo where id=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1.02 rows=1 width=164) (actual time=0.006..0.007
rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 1
Planning time: 0.118 ms
Execution time: 0.021 ms
(5 rows)

The same plan. Adding 2 rows:

test=*# insert into foo (val) select val from foo;
INSERT 0 2
test=*# analyse foo;
ANALYZE
test=*# explain analyse select val from foo where id=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1.05 rows=1 width=164) (actual time=0.220..0.277
rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 3
Planning time: 0.149 ms
Execution time: 0.453 ms
(5 rows)

The same plan. Adding more rows:

test=*# insert into foo (val) select val from foo;
INSERT 0 4
test=*# insert into foo (val) select val from foo;
INSERT 0 8
test=*# insert into foo (val) select val from foo;
INSERT 0 16
test=*# insert into foo (val) select val from foo;
INSERT 0 32
test=*# insert into foo (val) select val from foo;
INSERT 0 64
test=*# insert into foo (val) select val from foo;
INSERT 0 128
test=*# insert into foo (val) select val from foo;
INSERT 0 256
test=*# insert into foo (val) select val from foo;
INSERT 0 512
test=*# insert into foo (val) select val from foo;
INSERT 0 1024
test=*# insert into foo (val) select val from foo;
INSERT 0 2048
test=*# insert into foo (val) select val from foo;
INSERT 0 4096
test=*# analyse foo;
ANALYZE
test=*# explain analyse select val from foo where id=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using foo_pkey on foo (cost=0.28..8.30 rows=1 width=164) (actual
time=0.007..0.008 rows=1 loops=1)
Index Cond: (id = 1)
Planning time: 0.120 ms
Execution time: 0.024 ms
(4 rows)

We got a new plan! Index-Scan now. We are looking now in pg_class to see how
many rows and pages we have:

test=*# select relpages, reltuples from pg_class where relname = 'foo';
relpages | reltuples
----------+-----------
200 | 8192
(1 row)

How large ist the Index?

test=*# select relpages, reltuples from pg_class where relname = 'foo_pkey';
relpages | reltuples
----------+-----------
25 | 8192
(1 row)

So, now it's cheaper to read the index and than do an index-scan on the heap to
read one record (our where-condition is on the primary key, so only one row
expected, one page have to read with random access)

It's simple math! If you want to learn more you can find a lot about that via
google:

https://www.google.de/?gws_rd=ssl#q=explaining+explain